查询ID值变更行及原值，优化低效SQL查询语句

核心优化思路 #

原查询依赖DATEADD进行自关联匹配上月数据，这类逻辑容易触发全表扫描或低效关联，导致执行缓慢。改用**窗口函数LAG()**可以直接在单表扫描中获取每条记录的上一周期数据，彻底消除DATEADD依赖，同时提升鲁棒性（无需依赖日期连续性）。

适配多数SQL引擎的实现代码 #

假设原表名为monthly_data，字段为id（实体ID）、value（数值）、date（月度日期，格式如2024-01-01）：

WITH data_with_prev_value AS (
    SELECT
        id,
        date AS change_date,
        value AS current_value,
        -- 按ID分组、日期排序，获取上一条记录的value和日期
        LAG(value) OVER (PARTITION BY id ORDER BY date) AS previous_value,
        LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date
    FROM monthly_data
)
SELECT
    id,
    change_date,
    previous_date,
    previous_value,
    current_value
FROM data_with_prev_value
-- 过滤出value发生变更的记录（可选保留首次出现的记录）
WHERE 
    previous_value <> current_value
    OR previous_value IS NULL
ORDER BY id, change_date;

针对支持QUALIFY子句的引擎（如BigQuery、Snowflake）的简化版 #

SELECT
    id,
    date AS change_date,
    LAG(date) OVER (PARTITION BY id ORDER BY date) AS previous_date,
    LAG(value) OVER (PARTITION BY id ORDER BY date) AS previous_value,
    value AS current_value
FROM monthly_data
QUALIFY 
    LAG(value) OVER (PARTITION BY id ORDER BY date) <> value
    OR LAG(value) OVER (PARTITION BY id ORDER BY date) IS NULL
ORDER BY id, change_date;

性能与鲁棒性提升说明 #

1. 性能优化

   • 移除了基于DATEADD的自关联逻辑，窗口函数仅需单次表扫描即可完成计算，避免了多次表访问或笛卡尔积风险。
   • 建议创建联合索引加速分组排序：CREATE INDEX idx_monthly_id_date ON monthly_data(id, date);

2. 鲁棒性提升

   • 不再依赖月度数据的连续性：如果某ID存在月份数据缺失，LAG()会自动取最近的上一条有效记录，而非因DATEADD匹配不到返回空值。
   • 逻辑更贴合业务实际：直接对比相邻记录的value变化，无需假设日期严格按月递增。

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