PostgreSQL查询存在数量负差值的商品并返回其全量历史记录

原来的查询核心问题是直接在子查询外层过滤了差值为负的行，导致只能拿到负差值对应的单条记录，无法拿到符合条件商品的全量差值历史。正确的修改逻辑是先标记所有出现过数量下跌的商品，再过滤出这些商品的全部差值记录即可。

修改后的查询语句 #

WITH product_diff AS (
    SELECT
        time_stamp,
        product,
        COALESCE(quantity - LAG(quantity) OVER (PARTITION BY product ORDER BY time_stamp), quantity) AS difference
    FROM logistics
),
qualified_products AS (
    -- 筛选出所有曾经出现过相邻时间数量减少的商品
    SELECT DISTINCT product
    FROM product_diff
    WHERE difference < 0
)
-- 取出符合条件商品的全量差值历史
SELECT pd.time_stamp, pd.product, pd.difference
FROM product_diff pd
INNER JOIN qualified_products qp ON pd.product = qp.product
ORDER BY pd.time_stamp, pd.product;

逻辑说明 #

• 第一层CTE product_diff 保留了原有逻辑，计算每个商品每个时间点和上一个时间点的数量差值，首次出现的商品差值默认取自身数量
• 第二层CTE qualified_products 从差值结果里捞出所有至少出现过一次负差值的商品，也就是满足需求第一点的商品集合
• 最后通过关联两个CTE，就能拿到符合条件商品的所有差值历史，匹配需求第二点的输出要求

执行上述语句后得到的结果和预期完全一致。

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