如何调整SQL查询获取员工Health福利数值变更的对应生效记录

前置修正说明 #

原查询存在2处基础问题需先修复：

• petf.person_number 后缺失逗号，会报语法错误
• 原时间过滤条件仅保留生效起始日在参数范围内的记录，会漏掉员工在参数范围外的后续Value变更记录，需调整为抓取所有和参数时间段存在交集、以及后续的变更记录

调整后完整SQL #

WITH raw_data AS (
    -- 拉取所有符合条件的员工福利原始记录
    SELECT 
        petf.person_number,
        petf.base_element_name,
        peef.effective_start_date,
        peef.effective_end_date,
        TO_NUMBER(pivf.value) AS value
    FROM pay_element_types_f petf
    JOIN pay_input_values_f pivf 
        ON pivf.element_type_id = petf.element_type_id
    JOIN per_all_elementries_f peef 
        ON peef.element_entry_id = pivf.element_entry_id
    WHERE petf.base_element_name = 'Health'
      -- 调整时间过滤：抓取所有和参数时间段有交集、以及后续的变更记录
      AND peef.effective_end_date >= :from_date
),
value_change_mark AS (
    -- 标记同一员工的Value是否发生变更，给连续相同Value的记录分配相同分组号
    SELECT 
        person_number,
        base_element_name,
        effective_start_date,
        value,
        SUM(CASE 
            WHEN value = LAG(value, 1, -1) OVER(PARTITION BY person_number ORDER BY effective_start_date) 
            THEN 0 ELSE 1 
        END) OVER(PARTITION BY person_number ORDER BY effective_start_date) AS value_grp
    FROM raw_data
),
grouped_data AS (
    -- 合并连续相同Value的记录，取每组首次生效日期
    SELECT 
        person_number,
        base_element_name,
        MIN(effective_start_date) AS effective_start_date,
        value,
        -- 统计该员工一共有多少个不同的Value分组
        COUNT(DISTINCT value_grp) OVER(PARTITION BY person_number) AS grp_count,
        -- 标记当前分组是否为最新分组
        ROW_NUMBER() OVER(PARTITION BY person_number ORDER BY MIN(effective_start_date) DESC) AS rn
    FROM value_change_mark
    GROUP BY person_number, base_element_name, value_grp, value
)
-- 最终输出符合规则的结果
SELECT 
    person_number AS PERSON_NUMBER,
    CASE WHEN rn = 1 THEN '~' || base_element_name ELSE base_element_name END AS base_element_name,
    TO_CHAR(effective_start_date, 'YYYY-MM-DD') AS effective_start_date,
    value AS Value
FROM grouped_data
-- 过滤规则：仅1个Value分组的员工只留最新行，多分组的员工保留所有行
WHERE (grp_count = 1 AND rn = 1) OR grp_count > 1
ORDER BY person_number, effective_start_date;

规则匹配说明 #

• 若同一员工Value发生变更，多分组的员工所有分组都会输出，通过rn标记最新分组，最新行自动拼接~前缀
• 仅存在一条福利记录的员工grp_count=1，直接保留最新行输出
• 同一员工参数范围内Value无变更的情况下grp_count=1，仅保留最新行输出
• 通过缺口岛逻辑给连续同Value的记录分组，每组取最小的生效起始日，自动合并连续同值记录，取首次生效日期输出

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