如何用SQL分析函数过滤记录，返回每日价格递增的商品行？

表结构与需求回顾 #

已知items表结构及数据如下：

order_name    price    price_date
cheese          10     10/21/2022
cheese          15     10/22/2022
cheese          12     10/23/2022
cake            10     10/22/2022
cake            15     10/23/2022
banana          10     10/25/2022
banana          12     10/26/2022
banana          15     10/27/2022

需要筛选出价格每日持续递增的商品，返回这类商品的最新价格记录（即每个符合条件商品的最后一条数据），最终结果仅保留cake和banana的最新记录。

实现思路 #

1. 用窗口函数LAG()获取每个商品的前一天价格，对比当前价格是否小于前一天价格（即出现降价）；
2. 标记每个商品是否存在降价行为；
3. 筛选出无降价行为的商品，再取每个商品的最新价格记录。

SQL查询语句 #

WITH item_price_check AS (
    SELECT 
        order_name,
        price,
        price_date,
        -- 获取同商品前一条记录的价格
        LAG(price) OVER (PARTITION BY order_name ORDER BY price_date) AS prev_price,
        -- 标记当前记录是否降价（当前价格 < 前一天价格则为1，否则0）
        CASE WHEN price < LAG(price) OVER (PARTITION BY order_name ORDER BY price_date) THEN 1 ELSE 0 END AS has_price_drop
    FROM items
),
valid_items AS (
    SELECT 
        order_name
    FROM item_price_check
    -- 筛选出从未出现降价的商品（所有记录的has_price_drop都为0）
    GROUP BY order_name
    HAVING MAX(has_price_drop) = 0
)
-- 从原表中取符合条件商品的最新记录
SELECT 
    i.order_name,
    i.price,
    i.price_date
FROM items i
JOIN valid_items vi ON i.order_name = vi.order_name
WHERE (i.order_name, i.price_date) IN (
    SELECT order_name, MAX(price_date) 
    FROM items 
    GROUP BY order_name
);

语句解释 #

1. CTE item_price_check：
   • 按商品分组、日期排序，用LAG(price)获取每个商品前一天的价格；
   • 通过CASE语句标记当前记录是否为降价行为。
2. CTE valid_items：
   • 按商品分组，筛选出所有记录中从未出现降价（MAX(has_price_drop) = 0）的商品。
3. 最终查询：
   • 关联原表和valid_items，取每个符合条件商品的最新日期对应的记录。

简化版（适用于支持窗口函数的数据库） #

如果数据库支持ROW_NUMBER()，可以进一步简化：

WITH item_price_check AS (
    SELECT 
        order_name,
        price,
        price_date,
        LAG(price) OVER (PARTITION BY order_name ORDER BY price_date) AS prev_price,
        ROW_NUMBER() OVER (PARTITION BY order_name ORDER BY price_date DESC) AS rn
    FROM items
),
valid_items AS (
    SELECT order_name
    FROM item_price_check
    GROUP BY order_name
    HAVING SUM(CASE WHEN price < prev_price THEN 1 ELSE 0 END) = 0
)
SELECT order_name, price, price_date
FROM item_price_check
WHERE rn = 1 AND order_name IN (SELECT order_name FROM valid_items);

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