sql - 在 SQL 中根据时间差删除行

Question

我被一个sql查询困住了。

考虑一个具有如下结构的表 - 带有时间戳的操作列表。这个想法是如果两个相同的动作之间的时间差小于 1 小时，如果它们一个接一个地跟随，则减少行数。如果有这种情况，我只想保留最新记录。

约会时间	行动
2020-11-16T11:53:00	一个
2020-11-16T11:54:00	一个
2020-11-16T11:55:00	一个
2020-11-16T11:56:00	乙
2020-11-16T11:57:00	一个
2020-11-18T16:14:00	一个
2020-11-18T16:32:00	C
2020-11-23T16:37:00	C
2020-11-27T17:24:00	乙
2020-11-27T17:25:00	乙

在前三行都是动作A的情况下，时间差小于一小时，所以我想只保留第3行。第5行也是动作A，距离上一个动作A只有2分钟，但是因为订单被动作B“打断”（所以它必须紧跟在前一个相同的动作记录之后的条件不满足）它将被保留。

所以输出应该是这样的：

约会时间	行动
2020-11-16T11:55:00	一个
2020-11-16T11:56:00	乙
2020-11-16T11:57:00	一个
2020-11-18T16:14:00	一个
2020-11-18T16:32:00	C
2020-11-23T16:37:00	C
2020-11-27T17:25:00	乙

我能够计算与前一个接触点的时间差，但我正在努力寻找一种方法来将接触点与同一动作的所有先前接触点进行比较并摆脱它们。有人可以将我推向正确的方向吗？谢谢

标签： sql

Answer 1

您的问题没有指定 RDBMS，因此我为 SQL Server 提供了一个解决方案，因为这是我最了解的。如果是别的东西，您可能需要对窗口函数和 DATEDIFF 函数进行更改。

这段代码不是最好的，就性能和可读性而言，其他人可能会提供一段更有说服力的代码，但是，这确实可以满足您的需求

/* set up the table you provided */
CREATE TABLE YourTable
(
    [Datetime] DATETIME,
    [Action]    NCHAR(1)
)

INSERT INTO YourTable
VALUES
('2020-11-16T11:53:00','A'),
('2020-11-16T11:54:00','A'),
('2020-11-16T11:55:00','A'),
('2020-11-16T11:56:00','B'),
('2020-11-16T11:57:00','A'),
('2020-11-18T16:14:00','A'),
('2020-11-18T16:32:00','C'),
('2020-11-23T16:37:00','C'),
('2020-11-27T17:24:00','B'),
('2020-11-27T17:25:00','B')

/* add an ID column */
SELECT  ROW_NUMBER() OVER (ORDER BY [Datetime]) AS ID,
        *
INTO    #YourTableWithId
FROM    YourTable
ORDER BY [Datetime]


     
/* get the rows that are the last row in an island */
SELECT  a.*
INTO    #EndIsland
FROM    #YourTableWithId a
        FULL JOIN #YourTableWithId b
            ON a.ID = b.ID - 1
WHERE   a.[Action] <> b.[Action] OR 
        b.ID IS NULL
        

SELECT  t.[Datetime],
        t.[Action]
FROM    #YourTableWithId t
        LEFT JOIN #EndIsland i  
            ON t.ID = i.id
WHERE   (i.id IS NULL AND DATEDIFF(HOUR,t.[Datetime],(
                                                        SELECT  TOP 1
                                                                [Datetime]
                                                        FROM    #EndIsland 
                                                        WHERE   #EndIsland.id > t.id 
                                                        ORDER BY ID
                                                    )
                                    ) > 1) /* its not the end of an island and its over an hour before */OR
        i.id IS NOT NULL /* the end of an island */