根据时差和出现数选择重复行

Question

我有一张这样的桌子：

​

​

如您所见，一些具有相同farsi_pelak字段的记录已在几秒钟内添加(检测到)超过1次。

这是因为一些已经修复的应用程序错误。

现在我需要选择并删除同时添加的重复行(+-几秒钟)。

这是我的疑问：

SELECT TOP 100 PERCENT
    y.id, y.farsi_pelak , y.detection_date_p , y.detection_time
      FROM dbo._tbl_detection y
          INNER JOIN       
        (SELECT TOP 100 PERCENT
                        farsi_pelak , detection_date_p
                        FROM dbo._tbl_detection WHERE camera_id = 2
                        GROUP BY farsi_pelak , detection_date_p
                        HAVING COUNT(farsi_pelak)>1) dt 
                        ON 
                        y.farsi_pelak=dt.farsi_pelak AND y.detection_date_p =dt.detection_date_p
                        ORDER BY farsi_pelak , detection_date_p DESC

但是我无法计算时差，因为我的detection_time字段不应该被分组。

Answer 1

如果使用Server 2012或更高版本，则可以使用LAG函数从“上一”行获取值。

然后计算相邻时间戳之间的差异，找出这些行之间的差异很小。

WITH
CTE
AS
(
    SELECT
        id
        ,farsi_pelak
        ,detection_date_p
        ,detection_time
        ,LAG(detection_time) OVER (PARTITION BY farsi_pelak 
            ORDER BY detection_date_p, detection_time) AS prev_detection_time
    FROM dbo._tbl_detection
)
,CTE_Diff
AS
(
    SELECT
        id
        ,farsi_pelak
        ,detection_date_p
        ,detection_time
        ,prev_detection_time
        ,DATEDIFF(second, prev_detection_time, detection_time) AS diff
    FROM CTE
)
SELECT
    id
    ,farsi_pelak
    ,detection_date_p
    ,detection_time
    ,prev_detection_time
    ,diff
FROM CTE_Diff
WHERE
    diff <= 10
;

当运行此查询并验证它只返回要删除的行时，可以将最后一个SELECT更改为DELETE。

WITH
CTE
AS
(
    SELECT
        id
        ,farsi_pelak
        ,detection_date_p
        ,detection_time
        ,LAG(detection_time) OVER (PARTITION BY farsi_pelak 
            ORDER BY detection_date_p, detection_time) AS prev_detection_time
    FROM dbo._tbl_detection
)
,CTE_Diff
AS
(
    SELECT
        id
        ,farsi_pelak
        ,detection_date_p
        ,detection_time
        ,prev_detection_time
        ,DATEDIFF(second, prev_detection_time, detection_time) AS diff
    FROM CTE
)
DELETE
FROM CTE_Diff
WHERE
    diff <= 10
;

Answer 2

我想您需要行号检查时间，如下所示，保留最早的时间数据，并丢弃大于1的行数的其余检测时间。

     Select   y.id, y.farsi_pelak , 
     y.detection_date_p , y.detection_time, 
     row_number() over (partition by  
       y.farsi_pelak, 
     y.detection_date_p order by 
      y.detection_time) rn
       from ( the above query)  where rn>1