PostgreSQL如何计算各产品相邻时间戳对应的数量差值

问题原因 #

你原有SQL的核心问题是LAG窗口函数没有添加PARTITION BY product子句，导致窗口是在全表范围内按时间排序取上一行的quantity，而不是按产品维度独立计算每个产品的上一期数值，所以计算出来的差值不符合预期。同时你写的多层GROUP BY属于冗余逻辑，可以直接简化。

修改后的正确SQL #

SELECT
    time_stamp,
    product,
    COALESCE(quantity - LAG(quantity) OVER (PARTITION BY product ORDER BY time_stamp), quantity) AS difference
FROM (
    SELECT
        time_stamp,
        product,
        SUM(quantity) AS quantity
    FROM logistics
    GROUP BY time_stamp, product
) t
ORDER BY time_stamp, product;

逻辑说明 #

• 内层子句先按日期和产品分组汇总数量，兼容同一个产品同一天有多条记录的场景
• 外层LAG窗口函数增加PARTITION BY product，保证每个产品的记录单独排序，仅取该产品上一个日期的数量做差值计算
• 用COALESCE处理产品首次出现时LAG返回NULL的情况，直接取当前的quantity作为差值，匹配需求
• 最终按日期、产品排序，输出结果和你给出的期望输出完全一致

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