假设我有一个简单的表:
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
我的选择应该忽略连续的双值。我知道如何做到这一点——我正在使用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 or prev_DetectorParameterValue <> DetectorParameterValue
但只有当ID差异大于5时,它才应该忽略它们。
所以我的选择应该是
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
ID 4和12应该被忽略,但是ID 8不应该被忽略因为ID 8-ID 4不是>5.
有办法做到这一点吗?
提前谢谢。
这是一个伪装的缺口和孤岛问题。一种方法使用行数差法。
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY ID) rn1,
ROW_NUMBER() OVER (PARTITION BY value ORDER BY ID) rn2
FROM yourTable
),
cte2 AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY value, rn1 - rn2 ORDER BY ID) rn
FROM cte
)
SELECT ID, value
FROM cte2
WHERE rn = 1
ORDER BY ID;