基于累计销量计算每日销量的SQL实现问题求助

原始数据表结构 #

期望结果表 #

错误尝试的SQL语句 #

with x as (
    select 
        distinct t1.ID, 
        t1.Sale_Date, 
        t1.Total_volume, 
        rank() over (partition by ID order by Sale_Date) as ranker 
    from t t1 order by t1.Sale_Date) 

    select t2.ID, t2.ranker, t2.Sale_date, t1.Total_volume, t1.Total_volume - t2.Total_volume as Daily_sale
    from x t1, x t2 where t1.ID = t2.ID and t2.ranker = t1.ranker-1 order by t1.ID;

正确的SQL实现方案 #

方法一：使用LAG窗口函数（推荐） #

大部分现代数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持窗口函数，LAG可以直接获取分组内前一行的Total_Volume值，逻辑简洁高效：

SELECT
    ID,
    Sale_Date,
    Total_Volume,
    COALESCE(Total_Volume - LAG(Total_Volume) OVER (PARTITION BY ID ORDER BY Sale_Date), Total_Volume) AS Daily_Sale
FROM t
ORDER BY ID, Sale_Date;

• 说明：LAG(Total_Volume) OVER (PARTITION BY ID ORDER BY Sale_Date)按ID分组、日期排序，取出当前行的前一行Total_Volume；COALESCE处理分组第一行的空值情况，直接取当前行的Total_Volume作为Daily_Sale。

方法二：自连接（兼容旧版本数据库） #

如果数据库不支持窗口函数，可通过自连接实现：

SELECT
    t1.ID,
    t1.Sale_Date,
    t1.Total_Volume,
    CASE
        WHEN t2.Total_Volume IS NULL THEN t1.Total_Volume
        ELSE t1.Total_Volume - t2.Total_Volume
    END AS Daily_Sale
FROM t t1
LEFT JOIN t t2
    ON t1.ID = t2.ID
    AND t2.Sale_Date = DATE_SUB(t1.Sale_Date, INTERVAL 1 DAY)
ORDER BY t1.ID, t1.Sale_Date;

• 说明：用LEFT JOIN关联同表，匹配条件为相同ID且t2日期是t1的前一天；分组第一行无匹配记录时，直接取当前行Total_Volume，其余行用当日值减前一日值。

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