小编典典

在每个 GROUP BY 组中选择第一行?

javascript

正如标题所暗示的那样,我想选择与分组的每组行的第一行GROUP BY

具体来说,如果我有一个purchases看起来像这样的表:

SELECT * FROM purchases;

我的输出:

id customer total
1 Joe 5
2 Sally 3
3 Joe 2
4 Sally 1

我想查询每个id人的最大购买量(totalcustomer。像这样的东西:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

预期输出:

FIRST(id) customer FIRST(total)
1 Joe 5
2 Sally 3

阅读 216

收藏
2022-02-17

共2个答案

小编典典

DISTINCT ON在PostgreSQL中通常是最简单和最快的。
(有关某些工作负载的性能优化,请参见下文。)

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

或者更短(如果不是很清楚)输出列的序数:

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Iftotal可以为 NULL (不会有任何伤害,但你会想要匹配现有的索引):

...
ORDER  BY customer, total DESC NULLS LAST, id;

要点

DISTINCT ON是标准的 PostgreSQL 扩展(仅DISTINCT在整个SELECT列表中定义)。

在子句中列出任意数量的表达式DISTINCT ON,组合的行值定义重复项。手册:

显然,如果两行在至少一个列值上不同,则它们被认为是不同的。在此比较中,空值被视为相等。

大胆强调我的。

DISTINCT ON可以与ORDER BY. 中的前导表达式ORDER BY必须在 中的表达式集中DISTINCT ON,但您可以自由地重新排列它们之间的顺序。例子。
您可以添加其他表达式以ORDER BY从每组对等点中选择特定行。或者,正如手册所说

DISTINCT ON表达式必须匹配最左边的表达式ORDER BY 。该ORDER BY子句通常包含附加表达式,用于确定每个DISTINCT ON组中所需的行优先级。

我添加了id作为打破平局的最后一项: “从每个共享最高的组
中选择最小的行。”id``total

要以与确定每组第一个的排序顺序不一致的方式对结果进行排序,您可以将上面的查询嵌套在另一个外部查询中ORDER BY

如果total可以为 NULL,则您很可能想要具有最大非空值的行。添加NULLS LAST喜欢演示。

SELECT列表DISTINCT ON不受以任何方式或ORDER BY以任何方式表达的约束。(在上面的简单情况下不需要):

  • 不必DISTINCT ON在or中包含任何表达式ORDER BY
  • 可以SELECT在列表中包含任何其他表达式。这有助于用子查询和聚合/窗口函数替换更复杂的查询。

我使用 Postgres 版本 8.3 - 13 进行了测试。但该功能至少从 7.1 版本开始就存在,所以基本上一直如此。

指数

上述查询的完美索引将是一个跨所有三列的多列索引,以匹配的顺序和匹配的排序顺序:

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

可能太专业了。但如果特定查询的读取性能至关重要,请使用它。如果您DESC NULLS LAST在查询中有,请在索引中使用相同的内容,以便排序顺序匹配并且索引适用。

有效性/性能优化

在为每个查询创建定制索引之前权衡成本和收益。上述指标的潜力很大程度上取决于数据分布

使用索引是因为它提供了预排序的数据。在 Postgres 9.2 或更高版本中,如果索引小于基础表,查询也可以从仅索引扫描中受益。但是,必须完整地扫描索引。

对于每个客户的几*行(列中的高基数customer),这是非常有效的。如果您仍然需要排序输出,则更是如此。随着每个客户的行数增加,收益会缩小。
理想情况下,您有足够的空间work_mem在 RAM 中处理涉及的排序步骤,而不会溢出到磁盘。但通常设置work_mem
过高*会产生不利影响。考虑SET LOCAL异常大的查询。找到你需要多少EXPLAIN ANALYZE

2022-02-17
小编典典

我在65579 行的真实表上使用 PostgreSQL 9.1运行了三个测试,并且在所涉及的三列中的每一列上都有单列 btree 索引,并获得了 5 次运行的最佳执行时间
将@OMGPonies的第一个查询 ( A) 与上述DISTINCT ON解决方案( B) 进行比较:

  1. 选择整个表,在这种情况下会产生 5958 行。
A: 567.218 ms
B: 386.673 ms
  1. 使用WHERE customer BETWEEN x AND y导致 1000 行的条件。
A: 249.136 ms
B:  55.111 ms
  1. 用 选择一个客户WHERE customer = x
A:   0.143 ms
B:   0.072 ms

使用另一个答案中描述的索引重复相同的测试

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
1A: 277.953 ms  
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms  
3B:   0.048 ms
2022-02-17