递归Snowflake月度快照查询问题：部门变动员工历史记录缺失

这是Stack Exchange上的后续提问，原问题明确了查询目标并提供了源数据示例。已实现递归查询，比重复执行36次再union的非递归查询更高效，但当前代码存在问题：对于发生部门变动的员工，仅返回最近部门变动后的月度末部门值，缺失变动前的记录。

预期输出（部门变动员工） #

Month - Department Code
0 - 100
1 - 100
2 - 200
3 - 200

当前实际输出 #

Month - Department Code
0 - 100
1 - 100

当前查询代码 #

WITH Q AS (
    select 
        row_number() over(order by null) as q_level,
        last_day(dateadd(month, -q_level, CURRENT_DATE), month) as last_day_month
    from table(generator(ROWCOUNT=>36))
), Q1 AS (
    select 
        q.q_level
        ,q.last_day_month
        ,v_dept_history_adj.associate_id             
        ,v_dept_history_adj.home_department_code
        ,v_dept_history_adj.position_effective_date
        ,max(position_effective_date) OVER(PARTITION BY v_dept_history_adj.associate_id) AS most_recent_record 
    from datawarehouse.srctable
        ,Q
    where v_dept_history_adj.position_effective_date <= q.last_day_month
)
select 
    associate_id
    ,position_effective_date
    ,home_department_code
    ,most_recent_record
    ,last_day_month AS month
FROM Q1
where position_effective_date = most_recent_record
order by month desc, position_effective_date desc

问题原因及修复方案 #

问题根源 #

当前代码中max(position_effective_date) OVER(PARTITION BY v_dept_history_adj.associate_id)是取员工所有历史记录里的全局最新生效日期，后续筛选position_effective_date = most_recent_record只会保留该员工最后一次部门变动后的记录，导致变动前的月份数据被过滤。

修复后的代码 #

WITH Q AS (
    select 
        row_number() over(order by null) as q_level,
        last_day(dateadd(month, -q_level, CURRENT_DATE), month) as last_day_month
    from table(generator(ROWCOUNT=>36))
), Q1 AS (
    select 
        q.q_level
        ,q.last_day_month
        ,v_dept_history_adj.associate_id             
        ,v_dept_history_adj.home_department_code
        ,v_dept_history_adj.position_effective_date
        -- 按员工+月末日期分组，取该日期前最新的生效记录
        ,row_number() over(partition by v_dept_history_adj.associate_id, q.last_day_month 
                           order by v_dept_history_adj.position_effective_date desc) as rn
    from datawarehouse.srctable
    cross join Q
    where v_dept_history_adj.position_effective_date <= q.last_day_month
)
select 
    associate_id
    ,position_effective_date
    ,home_department_code
    ,last_day_month AS month
FROM Q1
where rn = 1  -- 保留每个员工每个月末对应的最新部门记录
order by month desc, associate_id, position_effective_date desc

关键修改点 #

1. 将全局的max(position_effective_date)替换为row_number()窗口函数，分组维度改为员工ID+月末日期，按生效日期倒序排序，确保每个月末日期对应的是该员工当时最新的部门记录。
2. 筛选条件从position_effective_date = most_recent_record改为rn = 1，保留每个分组内的第一条（即最新）记录。
3. 显式使用cross join替代隐式连接，提升代码可读性。

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