查询以获取具有两列唯一组合的最接近时间戳值的记录

Question

+-------+----------------------+----------+------------------+
| isbn  | book_container_id    | shelf_id |   update_time    |
+-------+----------------------+----------+------------------+
|   555 |                    6 | shelf100 | 11/15/2015 19:10 |
|   123 |                    1 | shelf1   | 11/28/2015 8:00  |
|   555 |                    4 | shelf5   | 11/28/2015 9:10  |
|   212 |                    2 | shelf2   | 11/29/2015 8:10  |
|   555 |                    6 | shelf9   | 11/30/2015 22:10 |
|   321 |                    8 | shelf7   | 11/30/2015 8:10  |
|   555 |                    4 | shelf33  | 12/1/2015 7:00   |
+-------+----------------------+----------+------------------+

假设我有一个表(PostgreSQL(，上面称为bookshelf_configuration。如果给我一个 ISBN 和一个时间戳，我希望能够找到最接近(之前(的 isbn 和 book_container_id 的每个唯一组合的记录。

因此，如果我正在查看isbn"555"，时间戳为"12/1/2015 7：00"，我应该返回：

+-------+----------------------+----------+------------------+
| isbn  | book_container_id    | shelf_id |   update_time    |
+-------+----------------------+----------+------------------+
|   555 |                    6 | shelf9   | 11/30/2015 22:10 |
|   555 |                    4 | shelf33  | 12/1/2015 7:00   |
+-------+----------------------+----------+------------------+

我对SQL的了解是非常基本的。我有一个查询，如果我只需要考虑 isbn，它会起作用，但我需要一些帮助来了解如何为组合(isbn, book_container_id)执行此操作。

Answer 1

DISTINCT ON的典型用例：

SELECT DISTINCT ON (book_container_id)
       isbn, book_container_id, shelf_id, update_time 
FROM   bookshelf_configuration
WHERE  isbn = 555
AND    update_time <= '2015-12-01 07:00'  -- ISO 8601 format
ORDER  BY book_container_id, update_time DESC;

假设update_time是NOT NULL定义的，或者你必须添加NULLS LAST。详细说明：

• 选择每个分组依据组中的第一行？

根据基数和值频率，可能会有更快的查询样式：

• 优化分组依据查询以检索每个用户的最新记录

无论哪种方式，(isbn, book_container_id, update_time DESC)上的多列索引都是快速处理非平凡大小表的关键。排序顺序应与查询匹配(或者完全反转(。如果向查询添加NULLS LAST，则也将其添加到索引中。

旁白：最好对所有日期/时间常量使用 ISO 8601 格式，因为这与任何区域设置或日期样式设置都是明确的。相关：

• PostgreSQL：与日期时间之间

Answer 2

有一种

叫做Row_Number的东西可以在这里帮助你。

Select * 
From (
    Select *,
           row_number() OVER (partition by isbn, book_container_id order by update_time desc) rn
    From   bookshelf_configuration
    Where  isbn = 555 and update_time <= '12/1/2015 7:00'   
) q 
Where q.rn = 1