技术需求：计算特定ID的两日接收日期计数差并新增列展示

需求说明 #

需新增一列以展示与前一日的计数差异，前一日数据按接收日期（Date）划分。

现有数据表 #

表1（前一日数据） #

Date       Id
---------------
8/18/2024   2
8/19/2024   3
8/20/2024   4
8/21/2024   5
8/22/2024   6

总计数：20

表2（当日数据） #

Date       Id
--------------
8/18/2024   2
8/19/2024   3
8/20/2024   4
8/21/2024   5
8/22/2024   6
8/23/2024   2

总计数：22

计算逻辑示例 #

Date      Id        Date        Id   diff
------------------------------------------
8/18/2024   2       8/18/2024   2       0
8/19/2024   3       8/19/2024   3       0
8/20/2024   4       8/20/2024   4       0
8/21/2024   5       8/21/2024   5       0
8/22/2024   6       8/22/2024   6       0
8/23/2024   2                           2

期望输出格式 #

Date      Id    Diff
----------------------
8/18/2024   2   0
8/19/2024   3   0
8/20/2024   4   0
8/21/2024   5   0
8/22/2024   6   0
8/23/2024   2   2

实现方案（SQL） #

固定计数差异实现 #

针对示例中固定的计数差异场景，可通过全连接关联两张表的日期与ID，结合条件判断生成差异列：

SELECT 
    COALESCE(t2.Date, t1.Date) AS Date,
    COALESCE(t2.Id, t1.Id) AS Id,
    CASE
        WHEN t1.Id IS NOT NULL AND t2.Id IS NOT NULL THEN 0
        WHEN t1.Id IS NULL THEN 2
        ELSE -2
    END AS Diff
FROM 
    表1 t1
FULL JOIN 
    表2 t2 ON t1.Date = t2.Date AND t1.Id = t2.Id
ORDER BY 
    COALESCE(t2.Date, t1.Date);

通用计数差异实现 #

如果需要基于每日ID的实际统计计数计算差异，可先统计每日各ID的计数再关联计算：

WITH prev_day_counts AS (
    SELECT Date, Id, COUNT(*) AS count
    FROM 表1
    GROUP BY Date, Id
),
curr_day_counts AS (
    SELECT Date, Id, COUNT(*) AS count
    FROM 表2
    GROUP BY Date, Id
)
SELECT 
    COALESCE(c.Date, p.Date) AS Date,
    COALESCE(c.Id, p.Id) AS Id,
    COALESCE(c.count, 0) - COALESCE(p.count, 0) AS Diff
FROM 
    prev_day_counts p
FULL JOIN 
    curr_day_counts c ON p.Date = c.Date AND p.Id = c.Id
ORDER BY 
    COALESCE(c.Date, p.Date);

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