使用SQL自连接计算分组不同日期记录的计数差值问题

现有一张包含L_DATE、GRP、Counts字段的表，需按GRP分组计算以下数据：

• 最新日期（22.01.2023）的Current Count
• 前一日（21.01.2023）的Last Count
• 前两日（20.01.2023）的Last2Last Count
• Current Count与Last Count的差值Diff1
• Current Count与Last2Last Count的差值Diff2
  无对应日期数据时填0

原表数据 #

期望输出 #

尝试的SQL代码（问题：缺少分组A的结果） #

select distinct 
T1.GRP,
T1.Counts as "Current Count",
ifnull(T2.Counts,0) as "Last Count",
T1.Counts - T2.Counts as "Diff1",
ifnull(T3.Counts,0) as "Last2Last Count",
T1.Counts - T3.Counts as "Diff2"
from tbl T1
left  join tbl T2  on (T2.L_DATE = '21.01.2023' and T2.GRP = T1.GRP)
left  join tbl T3  on (T3.L_DATE = '20.01.2023' and T3.GRP = T1.GRP)
where T1.L_DATE = ('22.01.2023') 

问题分析与解决 #

你的SQL通过WHERE T1.L_DATE = '22.01.2023'只筛选出有最新日期记录的分组，而分组A没有该日期的数据，因此被直接排除。要包含所有分组，需先提取所有唯一的GRP，再分别关联各日期的数据。

解决方案SQL

SELECT
    grps.GRP,
    IFNULL(current_counts.Counts, 0) AS "Current Count",
    IFNULL(last_counts.Counts, 0) AS "Last Count",
    IFNULL(current_counts.Counts, 0) - IFNULL(last_counts.Counts, 0) AS "Diff1",
    IFNULL(last2last_counts.Counts, 0) AS "Last2Last Count",
    IFNULL(current_counts.Counts, 0) - IFNULL(last2last_counts.Counts, 0) AS "Diff2"
FROM (
    -- 提取所有唯一分组
    SELECT DISTINCT GRP FROM tbl
) grps
LEFT JOIN tbl current_counts 
    ON grps.GRP = current_counts.GRP AND current_counts.L_DATE = '22.01.2023'
LEFT JOIN tbl last_counts 
    ON grps.GRP = last_counts.GRP AND last_counts.L_DATE = '21.01.2023'
LEFT JOIN tbl last2last_counts 
    ON grps.GRP = last2last_counts.GRP AND last2last_counts.L_DATE = '20.01.2023'
ORDER BY grps.GRP;

代码说明

1. 子查询grps先获取表中所有唯一的GRP，确保所有分组都被纳入结果；
2. 通过三次左连接分别关联三个日期的数据，用IFNULL将无数据的情况填充为0；
3. 计算差值时，对两个参与计算的值都做IFNULL处理，避免出现NULL结果；
4. 最后按GRP排序，与期望输出格式一致。

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