sql - 为表中的每个ID选择最新的3条记录

Question

我有一个包含复合主键(ID、Date)的表，如下所示。

+------+------------+-------+|  ID  |    Date    | Value |+------+------------+-------+|   1  | 1433419200 |   15  ||   1  | 1433332800 |   23  ||   1  | 1433246400 |   41  ||   1  | 1433160000 |   55  ||   1  | 1432900800 |   24  ||   2  | 1433419200 |   52  ||   2  | 1433332800 |   23  ||   2  | 1433246400 |   39  ||   2  | 1433160000 |   22  ||   3  | 1433419200 |   11  ||   3  | 1433246400 |   58  ||  ... |    ...     |  ...  |+------+------------+-------+

There is also a separate index on Date column. The table is of moderate size, currently ~600k row and growing by ~2k everyday.

I want to do a single SELECT query that returns the latest 3 records (ordered by Date timestamp) for each ID. For each given ID, the Date values are always unique, so no need to worry about ties for Date here.

I've tried a self-join approach, inspired by this answer, but it took quite a few seconds to run and returned nothing:

SELECT p1.ID, p1.Date, p1.Value FROM MyTable AS p1
LEFT JOIN MyTable AS p2 
ON p1.ID=p2.ID AND p1.Date<=p2.Date
GROUP BY p1.ID
HAVING COUNT(*)<=5
ORDER BY p1.ID, p1.Date DESC;

什么是快速解决方案？

Answer 1

您可以查找每个 ID 的三个最近日期:

SELECT ID, Date, Value
FROM MyTable
WHERE Date IN (SELECT Date
               FROM MyTable AS T2
               WHERE T2.ID = MyTable.ID
               ORDER BY Date DESC
               LIMIT 3)

或者，查找每个 ID 的第三个最近日期，并将其用作限制:

SELECT ID, Date, Value
FROM MyTable
WHERE Date >= IFNULL((SELECT Date
                      FROM MyTable AS T2
                      WHERE T2.ID = MyTable.ID
                      ORDER BY Date DESC
                      LIMIT 1 OFFSET 2),
                     0)

两个查询都应该从主键的索引中获得良好的性能。