如何在SSMS中以NULL行作为分隔符将数据行划分为不同周期

针对你需要根据Covereddt的NULL值为每个ID划分周期并标记Period列的需求，可以用窗口函数的累积求和来实现，具体解法如下：

核心思路 #

按ID分区，以行的顺序（需指定排序字段，比如记录生成时间或自增序列）为依据，统计当前行之前出现的Covereddt IS NULL的次数，次数加1就是当前行所属的周期编号。每个NULL值会作为前一个周期的结束标记，后续行自动进入下一个周期。

示例SQL代码 #

假设你的表名为target_table，包含ID、Covereddt字段，还有一个用于确定行顺序的字段（比如record_dt记录时间或seq自增ID）：

SELECT
    ID,
    Covereddt,
    -- 计算周期编号：当前行前的NULL总数 + 1
    1 + SUM(CASE WHEN Covereddt IS NULL THEN 1 ELSE 0 END) OVER (
        PARTITION BY ID
        ORDER BY record_dt  -- 替换成你的实际排序字段
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS Period
FROM target_table

逻辑说明 #

• PARTITION BY ID：确保每个ID的周期独立计算，互不干扰
• ORDER BY record_dt：必须指定排序字段，保证NULL值的分隔顺序正确（否则周期标记会混乱）
• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：限定求和范围是从当前ID的第一行到当前行的前一行，这样当前行的NULL不会影响自身的周期编号

如果你的NULL行需要作为新周期的起始（即NULL行本身属于下一个周期），可以修改求和逻辑为包含当前行：

SELECT
    ID,
    Covereddt,
    SUM(CASE WHEN Covereddt IS NULL THEN 1 ELSE 0 END) OVER (
        PARTITION BY ID
        ORDER BY record_dt
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) + CASE WHEN Covereddt IS NULL THEN 0 ELSE 1 END AS Period
FROM target_table

为什么lead/lag没解决问题？ #

lead/lag只能获取相邻行的字段值，无法对分段进行累积计数，而累积求和的窗口函数可以直接统计ID内的NULL出现次数，以此作为周期划分的依据，更适合这类分段标记场景。

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