如何用Presto SQL构建商品价格变动日期追踪查询

原查询的问题出在两个核心点：

1. lead函数的排序字段错误，应该按price_date而非price排序
2. 未对连续相同价格的日期段做聚合，导致返回冗余的每日数据

步骤1：标记连续相同价格的分组 #

先用LAG函数对比当前价格与前一日价格，价格变化时生成新分组标识，再通过累计求和得到每个价格段的分组ID：

WITH price_groups AS (
    SELECT 
        item_name,
        supplier_name,
        price_date,
        price,
        -- 当前价格与前一日不同时标记为新分组，累计求和生成分组ID
        SUM(CASE WHEN price != LAG(price) OVER (PARTITION BY item_name ORDER BY price_date) THEN 1 ELSE 0 END) 
        OVER (PARTITION BY item_name ORDER BY price_date) AS group_id
    FROM price
)

步骤2：聚合分组并获取价格变动节点 #

对每个分组取价格段的起始日期（最小price_date），再用LEAD函数获取下一个价格段的起始日期，作为当前价格的变动节点：

SELECT 
    item_name,
    supplier_name,
    price,
    MIN(price_date) AS start_date,
    -- 获取下一个价格段的起始日期，无后续则保留原查询的最终日期
    LEAD(MIN(price_date)) OVER (PARTITION BY item_name ORDER BY MIN(price_date)) AS next_change_date
FROM price_groups
GROUP BY item_name, supplier_name, price, group_id
ORDER BY start_date;

完整SQL代码 #

WITH price_groups AS (
    SELECT 
        item_name,
        supplier_name,
        price_date,
        price,
        SUM(CASE WHEN price != LAG(price) OVER (PARTITION BY item_name ORDER BY price_date) THEN 1 ELSE 0 END) 
        OVER (PARTITION BY item_name ORDER BY price_date) AS group_id
    FROM price
)
SELECT 
    item_name,
    supplier_name,
    price,
    MIN(price_date) AS start_date,
    LEAD(MIN(price_date)) OVER (PARTITION BY item_name ORDER BY MIN(price_date)) AS next_change_date
FROM price_groups
GROUP BY item_name, supplier_name, price, group_id
ORDER BY start_date;

结果验证 #

针对示例数据执行后，会得到符合需求的精简结果：

• price = $4, start_date = 2022-11-21, next_change_date = 2022-11-25
• price = $3, start_date = 2022-11-25, next_change_date = 2022-12-01
• price = $4, start_date = 2022-12-01, next_change_date = 2023-02-14

（注：若期望$3的起始日期为2022-11-26，可调整逻辑为取价格变动后的第一个日期，只需修改聚合时的日期选取规则即可）

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