如何在Snowflake中获取不在结果集的pgft.name与pj.name前序记录？

由于当前查询仅筛选了指定日期范围的任职记录，导致历史数据不在结果集中无法直接使用LAG()，可以通过以下方式获取前序的职级和岗位名称：

1. 先获取该人员的所有任职历史记录（不受原日期范围限制），关联对应日期的职级和岗位信息
2. 按人员ID和任职生效日期排序，使用LAG()窗口函数提取前序记录的职级、岗位名称
3. 最后过滤回原需求的日期范围

具体SQL如下：

WITH all_assignments AS (
    SELECT 
        paaf.person_id,
        paaf.effective_start_date,
        papf.person_number,
        pgft.name AS current_pgft_name,
        pj.name AS current_pj_name
    FROM per_all_assignments_f paaf
    LEFT JOIN per_all_people_f_VW papf 
        ON papf.person_id = paaf.person_id 
        AND CAST(paaf.effective_start_date AS DATE) BETWEEN CAST(papf.effective_start_date AS DATE) AND CAST(papf.effective_end_date AS DATE)                                               
    LEFT JOIN PER_GRADES_F_VL_VW pgft 
        ON pgft.grade_id = paaf.grade_id
        AND CAST(paaf.effective_start_date AS DATE) BETWEEN CAST(pgft.effective_start_date AS DATE) AND CAST(pgft.effective_end_date AS DATE)
    LEFT JOIN PER_JOBS_F_VL_VW pj 
        ON pj.job_id = paaf.job_id
        AND CAST(paaf.effective_start_date AS DATE) BETWEEN CAST(pj.effective_start_date AS DATE) AND CAST(pj.effective_end_date AS DATE)
),
assignments_with_previous AS (
    SELECT 
        person_id,
        effective_start_date,
        person_number,
        current_pgft_name,
        current_pj_name,
        -- 按人员分组，按生效日期升序取前一条的职级名称
        LAG(current_pgft_name) OVER (PARTITION BY person_id ORDER BY effective_start_date) AS previous_pgft_name,
        -- 按人员分组，按生效日期升序取前一条的岗位名称
        LAG(current_pj_name) OVER (PARTITION BY person_id ORDER BY effective_start_date) AS previous_pj_name
    FROM all_assignments
)
-- 过滤回原需求的日期范围
SELECT 
    person_id,
    effective_start_date,
    person_number,
    current_pgft_name AS pgft_name,
    current_pj_name AS pj_name,
    previous_pgft_name,
    previous_pj_name
FROM assignments_with_previous
WHERE effective_start_date BETWEEN '2020-01-01' AND '2023-06-30' -- 注意：原日期的2023-06-31是无效日期，修正为2023-06-30
ORDER BY person_id, effective_start_date;

关键说明 #

• all_assignments CTE：获取人员的全部任职记录，确保历史数据被纳入计算范围
• LAG()窗口函数：按person_id分组，effective_start_date排序，提取前一条记录的职级和岗位名称
• 修正了原查询中的无效日期2023-06-31，替换为合法的2023-06-30

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