SQL需求：日期范围产品变更计数与初始值回退归零逻辑

要实现这个需求，我们可以通过**公共表表达式(CTE)**一次性获取每个产品的初始值、查询范围内的最后值，再结合基础变更计数逻辑完成统计，全程用单SQL实现：

WITH product_initial_values AS (
    -- 获取每个产品全表中的初始值（最早记录的字段值）
    SELECT 
        fk_product_id,
        FIRST_VALUE(product_name) OVER (PARTITION BY fk_product_id ORDER BY timestamp ASC) AS initial_name,
        FIRST_VALUE(product_cost) OVER (PARTITION BY fk_product_id ORDER BY timestamp ASC) AS initial_cost
    FROM product_history
    GROUP BY fk_product_id, product_name, product_cost, timestamp
),
product_last_in_range AS (
    -- 获取指定日期范围内每个产品的最后一条记录的字段值
    SELECT DISTINCT
        fk_product_id,
        LAST_VALUE(product_name) OVER (PARTITION BY fk_product_id ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_name_in_range,
        LAST_VALUE(product_cost) OVER (PARTITION BY fk_product_id ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_cost_in_range
    FROM product_history
    WHERE timestamp BETWEEN '开始日期' AND '结束日期' -- 替换为你的查询日期范围
)
SELECT 
    ph.fk_product_id,
    -- 处理名称变更计数：如果最后值等于初始值则归0，否则用统计的变更次数
    CASE 
        WHEN pli.last_name_in_range = piv.initial_name THEN 0
        ELSE COUNT(CASE WHEN ph.product_name_changed = 1 THEN 1 END)
    END AS name_change_count,
    -- 处理成本变更计数：逻辑同上
    CASE 
        WHEN pli.last_cost_in_range = piv.initial_cost THEN 0
        ELSE COUNT(CASE WHEN ph.product_cost_changed = 1 THEN 1 END)
    END AS cost_change_count
FROM product_history ph
JOIN product_initial_values piv ON ph.fk_product_id = piv.fk_product_id
LEFT JOIN product_last_in_range pli ON ph.fk_product_id = pli.fk_product_id
WHERE ph.timestamp BETWEEN '开始日期' AND '结束日期' -- 同样替换为查询范围
GROUP BY ph.fk_product_id, piv.initial_name, piv.initial_cost, pli.last_name_in_range, pli.last_cost_in_range;

关键逻辑说明 #

• product_initial_values：通过FIRST_VALUE窗口函数，按产品分组并按时间升序排序，取每个产品的最早记录字段值作为初始值。
• product_last_in_range：在指定日期范围内，用LAST_VALUE窗口函数（需指定ROWS BETWEEN子句确保覆盖范围内所有记录）获取每个产品的最后一条记录字段值。
• 主查询：先统计基础变更次数，再通过CASE语句对比最后值和初始值，若相等则将计数置为0，否则保留统计结果。

注意事项 #

• 请将SQL中的'开始日期'和'结束日期'替换为实际的日期参数（比如'2024-01-01'和'2024-06-30'）。
• 若产品在查询范围内无记录，LEFT JOIN会让对应字段值为NULL，此时CASE判断会自动保留0计数，符合业务逻辑。

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