删除时间序列中的副本

I have a large set of measurements taken every 1 millisecond stored in a SQL Server 2012 table. Whenever there are 3 or more duplicate values in some rows that I would like to delete the middle duplicates. Highlighted values in this image of sample data are the ones that I want to delete. Is there a way to do this with a SQL query?

我在SQL Server 2012表中每1毫秒就有一组测量数据。当某些行中有3个或多个重复值时，我想删除中间的重复值。在这个示例数据映像中突出显示的值是我要删除的值。有一种方法可以用SQL查询来实现这一点吗?

3 个解决方案

#1

You can do this using a CTE and ROW_NUMBER:

您可以使用CTE和ROW_NUMBER实现这一点:

SQL Fiddle

SQL小提琴

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 

#2

I'm sure there must be a more efficient way to do this, but you could join the table to itself twice to find the previous and next value in the list, and then delete all of the entries where all three values are the same.

我确信一定有一种更有效的方法来实现这一点，但是您可以将表连接到本身两次，以找到列表中的前一个和下一个值，然后删除所有三个值相同的条目。

DELETE FROM tbl
WHERE ms IN
(
  SELECT T.ms
  FROM tbl T
  INNER JOIN tbl T1 ON T.ms = T1.ms + 1
  INNER JOIN tbl T2 ON T.ms = T2.ms - 1
  WHERE T.value = T1.value AND T.value = T2.value
)

If the table is really big, I can see this blowing tempdb though.

如果桌子真的很大，我可以看到这个吹tempdb。

#3

Yes there is

是的,有

 select * from table group by table.field ->value