如何在SQL中筛选出连续两年Sample_Check为Yes的记录

需要筛选出连续两年Sample_Check为Yes的员工的对应记录，相关数据如下：

初始数据 #

Employee_ID Calendar_Year Sample_Value  Sample_Check
1234        2020          55,000        No
1234        2021          70,000        Yes
1234        2022          50,000        No
3456        2020          80,000        Yes
3456        2021          40,000        No
3456        2022          65,000        Yes
5678        2020          30,000        No
5678        2021          70,000        Yes
5678        2022          90,000        Yes

期望结果 #

Employee_ID Calendar_Year Sample_Value  Sample_Check
5678        2022          90,000        Yes

已知LAG/LEAD函数，需确认能否用这类函数实现需求并获取具体方案。

完全可以用LAG函数实现该需求，核心思路是获取每位员工上一年的Sample_Check值，再判断当前年与上一年是否均为Yes。

具体SQL代码 #

WITH employee_yearly_data AS (
    SELECT 
        Employee_ID,
        Calendar_Year,
        Sample_Value,
        Sample_Check,
        -- 按员工分组、年份排序，取出上一年的Sample_Check值
        LAG(Sample_Check) OVER (PARTITION BY Employee_ID ORDER BY Calendar_Year) AS prev_year_check
    FROM your_table_name -- 替换为你的实际表名
)
SELECT 
    Employee_ID,
    Calendar_Year,
    Sample_Value,
    Sample_Check
FROM employee_yearly_data
WHERE 
    Sample_Check = 'Yes' 
    AND prev_year_check = 'Yes';

逻辑说明 #

1. 通过LAG函数为每条记录匹配同员工上一年的Sample_Check值，命名为prev_year_check；
2. 筛选时同时要求当前年Sample_Check为Yes、上一年prev_year_check也为Yes，即可得到连续两年达标当年的记录。

如果需要同时展示连续两年的两条达标记录，可调整筛选逻辑，或改用LEAD函数验证下一年的状态后再筛选。

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