如何在SQL中仅忽略ID差值大于5的重复值？

原始数据表（示例） #

ID value
1   15
2   30
3   10
4   10
5   16
6   20
7   15
8   15
9   40
10  70
11  50
12  50
13  19
14  11
15  3

需求说明 #

需要编写SQL语句过滤重复值，规则如下：

• 不删除所有连续重复项
• 仅当重复值与最近一次同值记录的ID差值大于5时，保留该重复项；若ID差值≤5，则忽略（注：根据示例，首次出现的同值的连续重复仅保留第一个；间隔大于5后再次出现的同值，其连续重复项全部保留）

现有使用LEAD函数的代码会删除所有连续重复值，无法满足需求：

SELECT   [DetectorParameterValue] 
FROM  (
   SELECT lead(DetectorParameterValue,1) over (partition by runid order by runtime) AS prev_DetectorParameterValue 
   FROM table_Detector 
   WHERE  RunID = @run_id
) AS [InnerDetector] 
WHERE (prev_DetectorParameterValue is null or prev_DetectorParameterValue <> DetectorParameterValue)

期望查询结果 #

ID Value
1   15
2   30
3   10
5   16
6   20
7   15
8   15
9   40
10  70
11  50
13  19
14  11
15  3

其中ID4和ID12被忽略（属于首次出现的同值的连续重复），ID8被保留（属于间隔大于5后再次出现的同值的连续重复，与前一个同值ID差值未超过5）

解决方案 #

可以结合LAG窗口函数，先定位每条记录最近的同值记录，再根据ID差值判断是否保留，以下是适配实际表结构的SQL：

SELECT runtime AS ID, DetectorParameterValue AS Value
FROM (
    SELECT 
        runtime,
        DetectorParameterValue,
        -- 获取最近一次同值记录的runtime（对应示例中的ID）
        LAG(runtime) OVER (PARTITION BY DetectorParameterValue ORDER BY runtime) AS last_same_value_runtime,
        -- 获取前一条记录的参数值，用于判断是否连续重复
        LAG(DetectorParameterValue) OVER (ORDER BY runtime) AS prev_value
    FROM table_Detector
    WHERE RunID = @run_id
) AS sub
WHERE 
    -- 首次出现的记录直接保留
    last_same_value_runtime IS NULL
    -- 间隔大于5后再次出现的同值记录保留
    OR (runtime - last_same_value_runtime > 5)
    -- 属于间隔大于5后的连续重复记录，保留
    OR (DetectorParameterValue = prev_value AND last_same_value_runtime = runtime - 1)
ORDER BY runtime;

逻辑说明 #

1. 使用LAG(runtime) OVER (PARTITION BY DetectorParameterValue ORDER BY runtime)获取每条记录最近一次同值记录的runtime（即示例中的ID）
2. 首次出现的记录（last_same_value_runtime IS NULL）直接保留
3. 若当前记录与最近同值记录的runtime差>5，说明是间隔较久后再次出现的同值，保留该记录
4. 若当前记录与前一条记录参数值相同，且最近同值记录就是前一条（即属于新的同值组内的连续重复），则保留该记录

这样就能精准匹配需求中的过滤规则，得到期望的结果。

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