sql-server - 删除时间序列中的重复项

Question

我在 SQL Server 2012 表中存储了大量每 1 毫秒进行一次的测量。每当某些行中有 3 个或更多重复值时，我想删除中间的重复值。此示例数据图像中突出显示的值是我要删除的值。有没有办法用 SQL 查询来做到这一点？

Answer 1

您可以使用 CTE 和 ROW_NUMBER 执行此操作:

SQL Fiddle

WITH CteGroup AS(
    SELECT *,
        grp = ROW_NUMBER() OVER(ORDER BY MS) - ROW_NUMBER() OVER(PARTITION BY Value ORDER BY MS)
    FROM YourTable
),
CteFinal AS(
    SELECT *,
        RN_FIRST = ROW_NUMBER() OVER(PARTITION BY grp, Value ORDER BY MS),
        RN_LAST  = ROW_NUMBER() OVER(PARTITION BY grp, Value ORDER BY MS DESC)
    FROM CteGroup
)
DELETE 
FROM CteFinal 
WHERE
    RN_FIRST > 1
    AND RN_LAST > 1