使用Pivot SQL按客户分档统计PAL与HUB表的日期间差数量

需求说明 #

• 现有两张数据表：PAL（存储PAL日期相关数据）、HUB（存储HUB日期相关数据）
• 两表公共字段：Consignment_Number（托运单号）、eventdatetime（事件时间），可通过托运单号关联
• 输出要求：6列统计表格，首列为分组后的客户（Client），剩余5列分别统计两表事件时间差为1、2、3、4、5天的记录条数

实现逻辑 #

1. 两表通过Consignment_Number做关联
2. 计算两表eventdatetime的日期差值，过滤出差值在1-5天范围内的有效记录
3. 按客户字段分组，分别统计各差值档位的记录数

参考SQL代码（以MySQL语法为例） #

SELECT
  p.Client AS '客户',
  SUM(CASE WHEN DATEDIFF(h.eventdatetime, p.eventdatetime) = 1 THEN 1 ELSE 0 END) AS '1day',
  SUM(CASE WHEN DATEDIFF(h.eventdatetime, p.eventdatetime) = 2 THEN 1 ELSE 0 END) AS '2days',
  SUM(CASE WHEN DATEDIFF(h.eventdatetime, p.eventdatetime) = 3 THEN 1 ELSE 0 END) AS '3days',
  SUM(CASE WHEN DATEDIFF(h.eventdatetime, p.eventdatetime) = 4 THEN 1 ELSE 0 END) AS '4days',
  SUM(CASE WHEN DATEDIFF(h.eventdatetime, p.eventdatetime) = 5 THEN 1 ELSE 0 END) AS '5days'
FROM PAL p
INNER JOIN HUB h 
  ON p.Consignment_Number = h.Consignment_Number
WHERE DATEDIFF(h.eventdatetime, p.eventdatetime) BETWEEN 1 AND 5
GROUP BY p.Client
ORDER BY p.Client;

补充说明：如果实际业务中HUB的事件时间早于PAL的事件时间，可将DATEDIFF的两个参数调换位置避免出现负数差值。如果使用其他数据库，只需替换对应日期差计算函数即可：PostgreSQL用(h.eventdatetime::date - p.eventdatetime::date)，SQL Server用DATEDIFF(day, p.eventdatetime, h.eventdatetime)。

内容的提问来源于stack exchange，提问作者sidekick