基于具有重复出现模式的时间戳对表进行重复数据删除

Question

好的。首先，如果这个问题已经被提及，请允许我深表歉意。我确实查看过，但没有一个解决方案能够解决我的问题的具体情况。

我有一个包含超过 1.6 亿行数据的表，用于跟踪一段时间内员工/服务器的状况。我想创建此数据的子集并删除整个过程中发生的重复，但保留更改发生时的顺序。对于大多数员工来说，减少的行数将从 700 行（并且还在不断增加）减少到 1 行。

这是我想要实现的目标的一个简化示例：

Given:

RowID  Employee  Server  Timestamp
-----  --------  ------  ---------
5      E000001   Serv-B  May01
4      E000001   Serv-A  Apr01
3      E000001   Serv-B  Mar01
2      E000001   Serv-A  Feb01
1      E000001   Serv-A  Jan01

Doing a "Min(Timestamp) Group By Employee, Server" would yield:
Employee Server  Timestamp
-------- ------  ---------
E000001  Serv-B  Mar01
E000001  Serv-A  Jan01
.
What I need is:
Employee Server  Timestamp
-------- ------  ---------
E000001  Serv-B  May01
E000001  Serv-A  Apr01
E000001  Serv-B  Mar01
E000001  Serv-A  Jan01

该表和为其提供数据的进程不属于我们的组，所以我不能影响那里的解决方案，我宁愿不被整个事情的副本所困扰。考虑到表的大小，我实际上无法采用游标/RBAR 方法。如果陷入困境，我可以编写一个应用程序来执行此操作，但我想知道 SQoLympus 的大神是否有智慧在存储过程中执行此操作。提前致谢！

编辑：这是 SQL Server 2008 - 抱歉没有提及。

Answer 1

如果SQL Server（假设我已经正确理解了你的要求）

/*Set up test table*/
DECLARE @T TABLE (
  RowID       INT,
  Employee    CHAR(7),
  [Server]    CHAR(6),
  [timestamp] DATETIME );

INSERT INTO @T
SELECT 5,'E000001','Serv-B',  '20010501' UNION ALL
SELECT 4,'E000001','Serv-A',  '20010401' UNION ALL
SELECT 3,'E000001','Serv-B',  '20010301' UNION ALL
SELECT 2,'E000001','Serv-A',  '20010201' UNION ALL
SELECT 1,'E000001','Serv-A',  '20010101';

WITH cte
     As (SELECT ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY RowID) -
                ROW_NUMBER() OVER (PARTITION BY Employee, Server
                                       ORDER BY RowID) AS Grp,
                *
         FROM   @T),
     cte2
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY Employee, Grp ORDER BY RowID) AS
                Rn
         FROM   cte)

/* Edit: Actually - You want a SELECT not a DELETE I think?
DELETE FROM cte2 WHERE  Rn > 1*/

SELECT   RowID, Employee, [Server], [timestamp]
FROM cte2
WHERE  Rn = 1

Answer 2

您没有说明您正在使用什么数据库，但如果例如这是 Oracle，您可以使用 lag 或 lead 分析函数来引用上一行或下一行。

select employee, server, timestamp 
from
   (select employee, server, timestamp,
    lag(employee) over (order by employee, server, timestamp) prev_employee 
    lag(server) over (order by employee, server, timestamp) prev_server 
    from table
   )
where not (employee = prev_employee and server = prev_server)