如何在SQL Server中用CTE获取Type=NA行前置日期并处理记录

需求规则 #

• 为每个非NA类型的记录，匹配其后续最早的Type=NA记录日期作为结束日期
• 有效记录（非NA）和对应NA行之间的其他非NA记录直接忽略，NA行仍作为该有效记录的结束日期
• 同一人员的有效记录，需在前一个周期的NA行出现后，才开启新周期
• 无对应NA行的有效记录，结束日期为NULL
• 开头的NA记录直接忽略，不参与任何匹配

源数据 #

Person    Type     dt_eff
123       A        2018-10-23 -- 周期起始记录
123       NA       2018-11-19 -- 对应起始记录的结束日期，不输出
123       NA       2018-12-25 -- 忽略，不输出
124       A        2020-01-01 -- 周期起始记录
124       B        2020-02-15 -- 忽略，不输出
124       NA       2020-05-14 -- 对应起始记录的结束日期，不输出
124       C        2020-10-13 -- 新周期起始记录
124       NA       2021-01-15 -- 对应此起始记录的结束日期
124       A        2021-05-22 -- 新周期起始记录
124       T        2021-08-22 -- 忽略，不输出
456       NA       2022-04-19 -- 忽略，无前置有效记录
456       A        2022-05-01 -- 周期起始记录，无对应NA行，结束日期为NULL
456       B        2022-07-15 -- 忽略，不输出

预期输出 #

Person    Type     dt_start     dt_end
123       A        2018-10-23   2018-11-19
124       A        2020-01-01   2020-05-14
124       C        2020-10-13   2021-01-15
124       A        2021-05-22   NULL
456       A        2022-05-01   NULL

源数据DDL&DML #

CREATE TABLE Person (
  Person INTEGER,
  Type VARCHAR(3),
  dt_eff Date
);

INSERT INTO Person (Person, Type, dt_eff)
VALUES
(123,'A','2018-10-23'),
(123,'NA','2018-11-19'),
(123,'NA','2018-12-25'),
(124,'A','2020-01-01'),
(124,'B','2020-02-15'),
(124,'NA','2020-05-14'),
(124,'C','2020-10-13'),
(124,'NA','2021-01-15'),
(124,'A','2021-05-22'),
(124,'T','2021-08-22'),
(456,'NA','2022-04-19'),
(456,'A','2022-05-01'),
(456,'B','2022-07-15')

你的尝试代码问题分析 #

原代码的分组逻辑仅在Type='NA'且与前一行类型不同时才累加分组编号，无法正确划分"有效起始记录到第一个NA行"的完整周期，导致中间的非NA记录干扰分组，无法准确匹配对应结束日期。

正确CTE解法 #

WITH PeriodGroups AS (
    -- 第一步：为每个记录标记所属周期分组
    SELECT 
        Person,
        Type,
        dt_eff,
        -- 累计求和：每遇到NA行就加1，同一周期内的记录归为同一组
        SUM(CASE WHEN Type = 'NA' THEN 1 ELSE 0 END) OVER (
            PARTITION BY Person 
            ORDER BY dt_eff 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM Person
),
CycleStarts AS (
    -- 第二步：提取每个周期的起始记录，匹配对应的结束日期
    SELECT 
        Person,
        -- 取周期内第一个非NA的类型
        FIRST_VALUE(Type) OVER (
            PARTITION BY Person, GroupId 
            ORDER BY dt_eff 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS CycleType,
        -- 取周期内第一个非NA的日期作为起始日期
        FIRST_VALUE(dt_eff) OVER (
            PARTITION BY Person, GroupId 
            ORDER BY dt_eff 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS dt_start,
        -- 取周期内最早的NA日期作为结束日期，无NA则为NULL
        MIN(CASE WHEN Type = 'NA' THEN dt_eff END) OVER (
            PARTITION BY Person, GroupId 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS dt_end
    FROM PeriodGroups
    WHERE Type <> 'NA' -- 仅关注非NA记录
)
-- 第三步：去重，每个周期保留一条起始记录
SELECT DISTINCT
    Person,
    CycleType AS Type,
    dt_start,
    dt_end
FROM CycleStarts
WHERE CycleType IS NOT NULL -- 排除无有效起始记录的分组
ORDER BY Person, dt_start;

代码逻辑说明 #

1. PeriodGroups：通过累计NA行数量划分周期，每出现一个NA就开启新周期，确保同一周期内的所有记录（含中间非NA）归属同一组。
2. CycleStarts：在每个周期内，提取最早的非NA记录作为周期起点，同时匹配该周期内最早的NA日期作为结束日期。
3. 最后去重并过滤无效分组，得到符合要求的结果。

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