SQL窗口函数：列转置实现SCD Type 2，查询未达预期求助

原始数据表 #

期望SCD Type 2结果表 #

原SQL存在的问题 #

select 
  cust_id,
  max(case when city_type='Physical' then city_name end) as physical_city,
  max(case when city_type='Office' then city_name end) as office_city,
  start_date,
  coalesce(lead(start_date) over(partition by party_id order by start_date), '9999-12-31') as end_date
from table
group by cust_id, start_date

1. 大小写不匹配：原始数据中city_type的值是小写的physical和office，但SQL里写的是大写开头的Physical和Office，导致case语句无法匹配数据，返回null。
2. 分区字段错误：窗口函数lead使用了party_id，但原始表的客户ID字段是cust_id，分区字段不匹配会导致窗口逻辑错误。
3. 分组逻辑缺陷：同一start_date下可能存在多条不同city_type的记录，分组后虽能聚合该日期的两个城市，但lead逻辑仅基于分组后的start_date，无法保证日期区间内城市值的连续性（比如某类城市未更新时，无法沿用之前的最新值）。

修正后的SQL #

简化版（适配多数SQL引擎） #

先通过窗口函数填充每个日期的最新城市值，再分组生成SCD Type 2区间：

WITH filled_cities AS (
    SELECT 
        cust_id,
        start_date,
        -- 向前填充最新的physical城市
        LAST_VALUE(CASE WHEN city_type = 'physical' THEN city_name END) 
            OVER (PARTITION BY cust_id ORDER BY start_date 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS physical_city,
        -- 向前填充最新的office城市
        LAST_VALUE(CASE WHEN city_type = 'office' THEN city_name END) 
            OVER (PARTITION BY cust_id ORDER BY start_date 
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS office_city
    FROM your_table_name
),
distinct_dates AS (
    -- 去重同一日期的重复记录
    SELECT DISTINCT cust_id, start_date, physical_city, office_city
    FROM filled_cities
)
SELECT 
    cust_id,
    physical_city,
    office_city,
    start_date,
    COALESCE(LEAD(start_date) OVER (PARTITION BY cust_id ORDER BY start_date), '9999-12-31') AS end_date
FROM distinct_dates
ORDER BY cust_id, start_date;

注意事项 #

• 将your_table_name替换为实际表名。
• 若start_date是字符串类型，需先转为日期格式（如TO_DATE(start_date, 'MM/DD/YYYY')），避免排序错误。
• 百万级数据场景下，建议在cust_id和start_date字段建立索引，提升查询效率。

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