如何在SQL中高效查询商品价格首次出现时间与无变动天数？

需求回顾 #

现有一张包含id（商品ID）、Price（价格）、Day、Month、Year字段的表，每日记录所有商品价格。需要筛选最新日期中价格未发生变动的商品，输出：

• Date：最新记录日期
• id：商品ID
• Last_Price：当前价格
• First_Price_Appearance：该价格首次出现的日期
• DaysWOchange：价格保持不变的天数（从首次出现到最新日期的天数差）

示例输入表：

期望输出表：

高效SQL实现方案 #

针对百万级数据，核心思路是利用窗口函数划分价格连续区间，避免全表循环，确保查询效率。以下是兼容主流SQL引擎（MySQL 8+/PostgreSQL/SQL Server）的代码：

WITH date_normalized AS (
    -- 第一步：将分散的日期字段合并为标准日期类型
    SELECT
        id,
        Price,
        STR_TO_DATE(CONCAT(Year, '-', Month, '-', Day), '%Y-%m-%d') AS record_date
    FROM your_table_name
),
price_groups AS (
    -- 第二步：为每个商品的连续相同价格段分组
    SELECT
        id,
        Price,
        record_date,
        -- 用ROW_NUMBER计算分组标识：当价格变化时，分组ID递增
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY record_date) 
        - ROW_NUMBER() OVER (PARTITION BY id, Price ORDER BY record_date) AS price_group_id
    FROM date_normalized
),
group_stats AS (
    -- 第三步：计算每个价格段的首次出现日期、最新日期和天数差
    SELECT
        id,
        Price AS Last_Price,
        MIN(record_date) AS First_Price_Appearance,
        MAX(record_date) AS latest_date,
        DATEDIFF(MAX(record_date), MIN(record_date)) AS DaysWOchange
    FROM price_groups
    GROUP BY id, Price, price_group_id
),
latest_overall AS (
    -- 第四步：获取全表的最新记录日期
    SELECT MAX(record_date) AS global_latest_date
    FROM date_normalized
)
-- 第五步：筛选出最新日期仍处于价格稳定段的商品
SELECT
    l.global_latest_date AS Date,
    gs.id,
    gs.Last_Price,
    gs.First_Price_Appearance,
    gs.DaysWOchange
FROM group_stats gs
CROSS JOIN latest_overall l
WHERE gs.latest_date = l.global_latest_date
ORDER BY gs.id;

关键优化点说明 #

1. 日期标准化：先将Day/Month/Year合并为DATE类型，避免后续日期计算的性能损耗，同时确保排序准确性。
2. 连续价格分组：通过双重ROW_NUMBER()窗口函数生成分组ID，快速识别每个商品的连续相同价格区间——当价格不变时，两个ROW_NUMBER的差值保持一致；价格变化时差值递增，实现无循环的区间划分。
3. 聚合统计：对每个价格区间聚合计算首次日期、最新日期和天数差，避免逐行处理。
4. 全局最新日期过滤：先获取全表最新日期，再筛选出该日期仍处于稳定价格段的商品，精准定位目标数据。

性能建议 #

• 为id、Price和record_date（或原表的Year/Month/Day）建立复合索引，窗口函数和分组操作会大幅受益。
• 如果是MySQL环境，确保开启ONLY_FULL_GROUP_BY模式的同时，利用窗口函数生成的分组ID保证聚合逻辑的正确性。

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