使用Snowflake SQL将交易数据转换为月度快照用于Tableau时序分析

需要将员工部门变动的交易数据转换为月末快照（共36个月度，示例为4个），用于Tableau时序分析。交易数据记录员工调岗日期及新部门，员工可能多次调岗或无调岗变动。要求采用高效的递归SQL写法，替代传统单语句或UNION的实现方式。

输入交易数据 #

期望月末快照输出 #

递归SQL实现方案 #

核心思路 #

• 递归生成36个月度的月末日期序列，覆盖目标时间范围
• 为每个员工的调岗记录计算生效区间，明确部门的起止时间
• 关联快照日期与员工部门数据，筛选出每个快照日的最新有效部门，确保全量员工覆盖

完整SQL代码 #

WITH RECURSIVE monthly_snapshots AS (
    -- 初始锚点：设置第一个快照月末日期（按需调整起始月）
    SELECT DATE_TRUNC('MONTH', '2022-08-31'::DATE) + INTERVAL '1 MONTH - 1 DAY' AS snapshot_date
    UNION ALL
    -- 递归生成后续35个月末日期，凑齐36个快照
    SELECT DATE_TRUNC('MONTH', snapshot_date) + INTERVAL '1 MONTH - 1 DAY'
    FROM monthly_snapshots
    WHERE snapshot_date < DATE_TRUNC('MONTH', CURRENT_DATE) - INTERVAL '1 MONTH - 1 DAY'
    LIMIT 36
),
employee_dept_changes AS (
    SELECT 
        emp_id,
        department_code,
        effective_date,
        -- 计算当前部门的失效日期：下一次调岗的前一天，无后续调岗则设为当前月末
        COALESCE(
            DATE_TRUNC('MONTH', LEAD(effective_date) OVER (PARTITION BY emp_id ORDER BY effective_date)) - INTERVAL '1 DAY',
            DATE_TRUNC('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY'
        ) AS end_date
    FROM employee_transactions -- 替换为实际交易数据表名
)
SELECT 
    e.emp_id,
    edc.department_code,
    ms.snapshot_date
FROM (SELECT DISTINCT emp_id FROM employee_transactions) e
CROSS JOIN monthly_snapshots ms
LEFT JOIN employee_dept_changes edc 
    ON e.emp_id = edc.emp_id
    AND ms.snapshot_date BETWEEN edc.effective_date AND edc.end_date
-- 筛选每个员工在对应快照日的最新有效部门
QUALIFY ROW_NUMBER() OVER (PARTITION BY e.emp_id, ms.snapshot_date ORDER BY edc.effective_date DESC) = 1
ORDER BY ms.snapshot_date DESC, e.emp_id;

关键逻辑说明 #

• monthly_snapshots递归CTE：从指定起始月末开始，逐月生成后续月末日期，通过LIMIT 36严格控制快照数量
• employee_dept_changesCTE：用LEAD()函数获取员工下一次调岗时间，以此确定当前部门的失效区间，避免部门覆盖冲突
• 关联与筛选：通过CROSS JOIN确保每个员工在所有快照日都有记录，QUALIFY子句配合ROW_NUMBER()精准定位每个快照日的最新有效部门

注意事项 #

• 替换employee_transactions为实际的交易数据表名
• 可调整递归CTE中的起始日期和结束日期，匹配业务需要的36个月时间范围
• 若员工存在入职日期早于第一条调岗记录的情况，需补充初始部门及入职日期的记录，避免快照中出现NULL部门值

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