1. 首页
  2. 问题
  3. 在每个 GROUP BY 组中选择第一行？
一尘不染

在每个 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人的最大购买量（total）customer。像这样的东西：

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

阅读 179

收藏
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