如何删除SQL Server分区中末尾连续的指定值记录？

要实现删除每个_category分区末尾连续的_value=0记录，可以利用间隙与孤岛技巧结合窗口函数完成，完全无需游标。以下是具体实现方案：

思路说明 #

1. 为每个_category分区内连续相同的_value记录分配组编号（识别"孤岛"）；
2. 定位每个分区的最后一个组，判断该组的_value是否为0；
3. 筛选并删除属于该组的所有记录。

具体实现代码 #

第一步：验证目标记录

先运行以下查询确认需要删除的记录：

WITH grouped AS (
    SELECT 
        *,
        -- 为连续相同_value的记录分配组编号
        SUM(CASE WHEN prev_value = _value THEN 0 ELSE 1 END) OVER (PARTITION BY _category ORDER BY _date) AS group_id,
        -- 获取分区内的最大组号
        MAX(SUM(CASE WHEN prev_value = _value THEN 0 ELSE 1 END) OVER (PARTITION BY _category ORDER BY _date)) OVER (PARTITION BY _category) AS max_group_id
    FROM (
        SELECT 
            *,
            -- 获取前一条记录的_value，用于判断连续性
            LAG(_value) OVER (PARTITION BY _category ORDER BY _date) AS prev_value
        FROM #x
    ) t
)
SELECT *
FROM grouped
WHERE group_id = max_group_id AND _value = 0;

该查询会列出所有符合删除条件的记录（对应题目中ID为10、11、12、17的记录）。

第二步：执行删除操作

确认目标记录无误后，运行删除语句：

WITH grouped AS (
    SELECT 
        *,
        SUM(CASE WHEN prev_value = _value THEN 0 ELSE 1 END) OVER (PARTITION BY _category ORDER BY _date) AS group_id,
        MAX(SUM(CASE WHEN prev_value = _value THEN 0 ELSE 1 END) OVER (PARTITION BY _category ORDER BY _date)) OVER (PARTITION BY _category) AS max_group_id
    FROM (
        SELECT 
            *,
            LAG(_value) OVER (PARTITION BY _category ORDER BY _date) AS prev_value
        FROM #x
    ) t
)
DELETE FROM grouped
WHERE group_id = max_group_id AND _value = 0;

简化实现方案（利用LAST_VALUE） #

也可以通过LAST_VALUE直接定位分区内最后一个非零值的日期，筛选并删除该日期之后的所有零值记录：

WITH partition_info AS (
    SELECT 
        *,
        -- 获取分区内最后一个_value≠0的日期
        LAST_VALUE(CASE WHEN _value != 0 THEN _date END) OVER (
            PARTITION BY _category 
            ORDER BY _date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS last_non_zero_date
    FROM #x
)
DELETE FROM partition_info
WHERE _value = 0 AND (_date > last_non_zero_date OR last_non_zero_date IS NULL);

这个逻辑更直接：若零值记录的日期晚于分区内最后一个非零值的日期，或是分区内全为零值（last_non_zero_date为NULL），则删除该记录。

两种方案均能满足需求，可根据实际数据量选择更高效的实现。

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