sql - 删除具有特定列值的所有行(每个用户的最新行除外)

Question

我有一个表events，其中包含用户的事件，例如：

PK | user | event_type | timestamp
--------------------------------
1  | ab   | DTV        | 1
2  | ab   | DTV        | 2
3  | ab   | CPVR       | 3
4  | cd   | DTV        | 1
5  | cd   | DTV        | 2
6  | cd   | DTV        | 3

我想做的是每个 user仅保留一个事件，即具有最新 timestamp和 event_type = 'DTV'的事件。

将删除应用于上面的示例之后，该表应如下所示：

PK | user | event_type | timestamp
--------------------------------
2  | ab   | DTV        | 2
6  | cd   | DTV        | 3

你们中的任何人都能提出完成这项任务的东西吗？

更新：我正在使用Sqlite。这是我到目前为止的内容：

delete from events
where id not in (
  select id from (
    select id, user, max(timestamp)
    from events
    where event_type = 'DTV'
    group by user)
);

我很确定可以对此进行改进。有任何想法吗？

Answer 1

我认为您应该可以执行以下操作：

delete from events
where (user, timestamp) not in (
    select user, max(timestamp)
    from events
    where event_type = 'DTV'
    group by user
)

您可能会做一些更复杂的技巧，例如替换表或分区，这取决于您使用的数据库。