多分区表查询：获取Case首次跨Stage变更的日期

问题背景 #

现有多个不同时间点的分区快照表，记录了各Case在对应日期的Stage状态，需要提取每个Case首次发生Stage变更的日期。

输入数据 #

三个快照表的数据合并后如下：

期望结果 #

实现思路 #

1. 合并所有快照表的数据，形成完整的时序数据集；
2. 对每个Case按日期排序，用窗口函数获取其前一个快照的Stage值；
3. 筛选出Stage发生变更的记录，再提取每个Case的最早变更日期。

SQL代码示例 #

WITH combined_snapshots AS (
    -- 合并所有分区快照表数据
    SELECT Case, Stage, Date FROM snapshot_20220801
    UNION ALL
    SELECT Case, Stage, Date FROM snapshot_20220803
    UNION ALL
    SELECT Case, Stage, Date FROM snapshot_20220804
),
case_stage_changes AS (
    SELECT 
        Case,
        Date,
        Stage,
        -- 获取当前Case上一个快照的Stage状态
        LAG(Stage) OVER (PARTITION BY Case ORDER BY Date) AS previous_stage
    FROM combined_snapshots
)
SELECT 
    Case,
    MIN(Date) AS first_change_date
FROM case_stage_changes
-- 筛选出状态变更的记录（排除无前置状态的初始记录）
WHERE previous_stage IS NOT NULL 
  AND Stage != previous_stage
GROUP BY Case
ORDER BY first_change_date;

代码说明 #

• combined_snapshots：整合所有时间点的快照数据，确保覆盖每个Case的全周期状态；
• LAG(Stage) OVER (PARTITION BY Case ORDER BY Date)：按Case分组、日期升序，取当前记录的上一条Stage值，用于对比状态是否变更；
• 最终通过MIN(Date)提取每个Case的首次变更日期，过滤掉无前置状态的初始记录。

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