postgresql - 查询每组的最新记录

Question

我有一个表名价格:

CREATE TABLE price (
  price_id bigint pk,
  product_id bigint,
  start_date timestamp with timezone
)

查询:

select price_id, product_id, price, start_date 
from price 
where start_date <= current_timestamp

我明白了，按 UI 排序:

price_id | product_id  | start_date
5 1 2018-12-31  17:00:00.000000
60  1   2019-07-18 17:00:00.000000
66  1   2019-07-19 17:00:00.000000
6   2   2018-12-31 17:00:00.000000
69  2   2019-07-19 17:00:00.000000
7   3   2018-12-31 17:00:00.000000
8   4   2018-12-31 17:00:00.000000
9   5   2018-12-31 17:00:00.000000
10  6   2018-12-31 17:00:00.000000
11  7   2018-12-31 17:00:00.000000
59  7   2019-07-18 17:00:00.000000
67  8   2019-07-19 17:00:00.000000
71  8   2019-07-22 17:00:00.000000
12  8   2018-12-31 17:00:00.000000
64  8   2019-07-17 17:00:00.000000
13  9   2018-12-31 17:00:00.000000
14  10  2018-12-31 17:00:00.000000
15  11  2018-12-31 17:00:00.000000
16  12  2018-12-31 17:00:00.000000
70  12  2019-07-21 17:00:00.000000
17  13  2018-12-31 17:00:00.000000
18  14  2018-12-31 17:00:00.000000
19  15  2018-12-31 17:00:00.000000
46  16  2019-07-05 17:00:00.000000
54  16  2019-07-17 17:00:00.000000
20  16  2019-06-01 07:46:25.737000
44  16  2019-07-04 17:00:00.000000
53  16  2019-07-15 17:00:00.000000
55  16  2019-07-18 17:00:00.000000
21  17  2018-12-31 17:00:00.000000
22  18  2018-12-31 17:00:00.000000
23  19  2018-12-31 17:00:00.000000
24  20  2018-12-31 17:00:00.000000
25  21  2018-12-31 17:00:00.000000
74  21  2019-07-22 17:00:00.000000
26  22  2018-12-31 17:00:00.000000
27  23  2018-12-31 17:00:00.000000
68  23  2019-07-20 17:00:00.000000
28  24  2018-12-31 17:00:00.000000
29  25  2018-12-31 17:00:00.000000
30  26  2018-12-31 17:00:00.000000
31  27  2018-12-31 17:00:00.000000
32  28  2018-12-31 17:00:00.000000
33  29  2018-12-31 17:00:00.000000
34  30  2018-12-31 17:00:00.000000
35  31  2018-12-31 17:00:00.000000
36  32  2018-12-31 17:00:00.000000
37  33  2018-12-31 17:00:00.000000
63  34  2019-07-17 17:00:00.000000
38  34  2018-12-31 17:00:00.000000
62  34  2019-07-14 17:00:00.000000
39  35  2018-12-31 17:00:00.000000
43  35  2019-06-01 07:46:25.737000
61  35  2019-07-21 17:00:00.000000

如您所见，有多个 product_id，每个 product_id 都有多个 start_date。

我需要一个 postgre native 查询，它只能为每个 product_id 获取最接近 current_timestamp 的 1 个开始日期。

如果可能，之后翻译成 HQL。如果没有，仅 postgre 查询就可以了。

Answer 1

demo:db<>fiddle

DISTINCT ON 子句为您提供有序组的第一条记录。您的案例中的组是 productId，它们按 start_date 降序排列，将最新的一组带到每个组的顶部。这是由 DISTINCT ON 获取的:

SELECT DISTINCT ON (product_id)
    *
FROM
    price
ORDER BY product_id, start_date DESC