如何从累计总量计算每日增量 解决SQL自连接查询重复数据问题

你的自连接使用b.Date < a.Date作为关联条件，会将同ID下所有早于当前日期的历史记录都和当前行关联，例如ID为A、日期为2021-09-05的记录会同时匹配到2021-09-03、2021-09-04两条关联记录，最终产生大量重复行。

方案1：使用LAG窗口函数（推荐） #

主流数据库（MySQL 8.0+、PostgreSQL、Spark SQL、Hive等）均支持窗口函数，可直接通过LAG函数取同ID分组下前一行的累计值计算，写法简单性能更优：

CREATE TABLE table_b AS 
SELECT 
    ID,
    Date,
    Total,
    Total - COALESCE(LAG(Total) OVER (PARTITION BY ID ORDER BY Date), 0) AS Daily
FROM table_a;

其中PARTITION BY ID实现按ID分组，ORDER BY Date保证组内按日期升序排序，LAG(Total)取当前行上一行的Total值，无前置行时返回NULL，通过COALESCE转为0即可符合计算规则。

方案2：修改自连接逻辑（兼容低版本数据库） #

如果使用的是不支持窗口函数的低版本数据库（如MySQL 5.x），可在原有自连接基础上分组取最大前置日期对应的累计值，即可去重得到正确结果：

CREATE TABLE table_b AS 
SELECT 
    a.ID,
    a.Date,
    a.Total,
    a.Total - COALESCE(MAX(b.Total), 0) AS Daily
FROM table_a a 
LEFT OUTER JOIN table_a b
    ON a.ID = b.ID AND b.Date < a.Date
GROUP BY a.ID, a.Date, a.Total;

这里通过MAX(b.Total)取到同ID下所有早于当前日期的记录中最新的累计值，再按当前行的ID、日期、累计值分组，即可消除重复行。

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