SQLite:如何使用复合键从单个表中选择 "most recent record for each user"？

Question

我不是数据库专家，觉得我缺少一些核心 SQL 知识来解决这个问题。这是我可以解释的情况。

上下文:

我有一个包含时间戳用户事件记录的 SQLite 数据库表。这些记录可以通过时间戳和用户 ID 的组合来唯一标识(即事件发生的时间和事件的对象)。我理解这种情况被称为“复合主键”。该表看起来像这样(当然，删除了一堆其他列):

sqlite> select Last_Updated,User_ID from records limit 4;

Last_Updated   User_ID
-------------  --------
1434003858430  1   
1433882146115  3   
1433882837088  3   
1433964103500  2   

问题:我该怎么做 SELECT结果集只包含每个用户的最新记录？

鉴于上面的例子，我想找回的是一个看起来像这样的表:

Last_Updated   User_ID
-------------  --------
1434003858430  1   
1433882837088  3   
1433964103500  2   

(请注意，结果集仅包含用户 3 的最新记录。)

实际上，我在这个表中有大约 250 万行。

奖金:我一直在阅读有关 JOIN、重复数据删除程序等的答案，我一直在搜索教程/文章，希望能找到我遗漏的内容。我有广泛的编程背景，所以我可以像我以前做过一百次那样在过程代码中删除这个数据集，但我厌倦了编写脚本来完成我认为在 SQL 中应该可以实现的事情。这就是它的用途，对吧？

那么，从概念上讲，您认为我对 SQL 的理解缺少什么，我需要了解为什么您为我的问题提供的解决方案实际上有效？ (引用一篇真正解释实践背后理论的好文章就足够了。)我想知道为什么该解决方案实际上有效，而不仅仅是它确实有效。

非常感谢您的时间!

Answer 1

你可以试试这个:

select user_id, max(last_updated) as latest
from records
group by user_id

这应该为您提供每个用户的最新记录。我假设您在 user_id 和 last_updated 上有一个索引。

在上面的查询中，一般来说 - 我们要求数据库对 user_id 记录进行分组。如果 user_id 1 的记录超过 1 个，它们将全部组合在一起。从该记录集中，将选择最大的 last_updated 进行输出。然后寻找下一组并在那里应用相同的操作。

如果您有一个复合索引，sqlite 可能只会使用该索引，因为该索引包含查询中寻址的两个字段。索引比表本身小，因此扫描或查找速度更快。