如何用GROUP BY和INNER JOIN计算当前与往期记录总数差值

我来帮你搞定这个添加差值列的需求，咱们一步步来：

一、现有reports表数据 #

首先看一下咱们的源表数据，执行查询：

SELECT * FROM reports;

返回的表数据如下：

二、原查询语句及结果 #

你已经写好的原查询语句是：

SELECT Date(a.date), a.vendor, a.o_type, a.quantity, b.total 
FROM reports a 
INNER JOIN ( 
    SELECT vendor, date, SUM(quantity) as total 
    FROM reports 
    WHERE date >= '2020-04-06' AND date <= '2020-04-08' 
    GROUP BY vendor, date
) b ON a.date = b.date AND a.vendor = b.vendor

返回的结果如下：

三、需求梳理 #

咱们需要添加一个DIFFERENCE列，根据你给出的示例，统一计算规则为：

• 对于同一个供应商，当前日期的total 减去 前一日期的total（比如示例2中2020-04-08 apple的total为0，前一天是1100，差值为0-1100=-1100）
• 如果前一日期该供应商没有数据，视为前一日期的total为0
• 注：你给出的示例1计算式（150-1100=-950）看起来是前一天减当前，可能是笔误，我这里按照示例2、3的统一逻辑实现，如果需要调整计算方向可以随时修改公式。

四、实现方案 #

要实现这个需求，关键是先补全每个供应商在目标日期范围内每天的total（没有数据的日期total设为0），然后用窗口函数LAG()获取前一天的total，最后计算差值，再关联原表得到明细数据。

完整的SQL语句如下：

WITH date_range AS (
    -- 生成目标日期范围内的所有日期
    SELECT '2020-04-06' AS date UNION ALL
    SELECT '2020-04-07' AS date UNION ALL
    SELECT '2020-04-08' AS date
),
all_vendors AS (
    -- 获取所有唯一的供应商
    SELECT DISTINCT vendor FROM reports
),
vendor_daily_total AS (
    -- 生成每个供应商+日期的组合，补全无数据日期的total为0
    SELECT 
        av.vendor,
        dr.date,
        COALESCE(rt.total, 0) AS total
    FROM date_range dr
    CROSS JOIN all_vendors av
    LEFT JOIN (
        SELECT vendor, date, SUM(quantity) AS total
        FROM reports
        WHERE date >= '2020-04-06' AND date <= '2020-04-08'
        GROUP BY vendor, date
    ) rt ON av.vendor = rt.vendor AND dr.date = rt.date
),
vendor_total_with_diff AS (
    -- 计算差值：当前日期total - 前一天total，无前一天则用0代替
    SELECT 
        vendor,
        date,
        total,
        total - LAG(total, 1, 0) OVER (PARTITION BY vendor ORDER BY date) AS DIFFERENCE
    FROM vendor_daily_total
)
-- 关联原reports表，把差值列加入最终结果
SELECT 
    DATE(a.date) AS date,
    a.vendor,
    a.o_type,
    a.quantity,
    vtwd.total,
    vtwd.DIFFERENCE
FROM reports a
INNER JOIN vendor_total_with_diff vtwd 
    ON a.date = vtwd.date AND a.vendor = vtwd.vendor
WHERE a.date >= '2020-04-06' AND a.date <= '2020-04-08'
ORDER BY a.date, a.vendor;

关键步骤说明 #

1. date_range：生成我们需要的日期范围（2020-04-06到2020-04-08），确保每个日期都被覆盖到。
2. all_vendors：提取所有唯一的供应商，保证每个供应商在每个日期都有对应的记录。
3. vendor_daily_total：通过交叉连接生成供应商和日期的所有组合，左连接原有的total计算结果，用COALESCE()把没有数据的日期的total设为0，解决了示例2中apple在2020-04-08没有数据的问题。
4. vendor_total_with_diff：使用LAG()窗口函数，按供应商分组、日期排序，获取前一天的total（如果是该供应商的第一个日期，就用0代替前一天的total），然后计算当前total与前一天的差值。
5. 最后关联原reports表，把差值列添加到你原来的查询结果中，保持明细数据不变。

如果需要调整差值计算方向 #

如果你确实需要按照示例1的逻辑（前一天total - 当前total），只需要把vendor_total_with_diff中的差值计算语句改成：

LAG(total, 1, 0) OVER (PARTITION BY vendor ORDER BY date) - total AS DIFFERENCE

这样示例1的差值就会是150-1100=-950，符合你给出的示例结果。

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