正如标题所暗示的那样,我想选择与分组的每组行的第一行GROUP BY。
GROUP BY
具体来说,如果我有一个purchases看起来像这样的表:
purchases
SELECT * FROM purchases;
我的输出:
我想查询每个id人的最大购买量(total)customer。像这样的东西:
id
total
customer
SELECT FIRST(id), customer, FIRST(total) FROM purchases GROUP BY customer ORDER BY total DESC;
预期输出:
DISTINCT ON在PostgreSQL中通常是最简单和最快的。 (有关某些工作负载的性能优化,请参见下文。)
DISTINCT ON
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
SELECT
在子句中列出任意数量的表达式DISTINCT ON,组合的行值定义重复项。手册:
显然,如果两行在至少一个列值上不同,则它们被认为是不同的。在此比较中,空值被视为相等。
大胆强调我的。
DISTINCT ON可以与ORDER BY. 中的前导表达式ORDER BY必须在 中的表达式集中DISTINCT ON,但您可以自由地重新排列它们之间的顺序。例子。 您可以添加其他表达式以ORDER BY从每组对等点中选择特定行。或者,正如手册所说:
ORDER BY
DISTINCT ON表达式必须匹配最左边的表达式ORDER BY 。该ORDER BY子句通常包含附加表达式,用于确定每个DISTINCT ON组中所需的行优先级。
我添加了id作为打破平局的最后一项: “从每个共享最高的组 中选择最小的行。”id``total
id``total
要以与确定每组第一个的排序顺序不一致的方式对结果进行排序,您可以将上面的查询嵌套在另一个外部查询中ORDER BY。
如果total可以为 NULL,则您很可能想要具有最大非空值的行。添加NULLS LAST喜欢演示。
NULLS LAST
该SELECT列表DISTINCT ON不受以任何方式或ORDER BY以任何方式表达的约束。(在上面的简单情况下不需要):
我使用 Postgres 版本 8.3 - 13 进行了测试。但该功能至少从 7.1 版本开始就存在,所以基本上一直如此。
上述查询的完美索引将是一个跨所有三列的多列索引,以匹配的顺序和匹配的排序顺序:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
可能太专业了。但如果特定查询的读取性能至关重要,请使用它。如果您DESC NULLS LAST在查询中有,请在索引中使用相同的内容,以便排序顺序匹配并且索引适用。
DESC NULLS LAST
在为每个查询创建定制索引之前权衡成本和收益。上述指标的潜力很大程度上取决于数据分布。
使用索引是因为它提供了预排序的数据。在 Postgres 9.2 或更高版本中,如果索引小于基础表,查询也可以从仅索引扫描中受益。但是,必须完整地扫描索引。
对于每个客户的几*行(列中的高基数customer),这是非常有效的。如果您仍然需要排序输出,则更是如此。随着每个客户的行数增加,收益会缩小。 理想情况下,您有足够的空间work_mem在 RAM 中处理涉及的排序步骤,而不会溢出到磁盘。但通常设置work_mem 过高*会产生不利影响。考虑SET LOCAL异常大的查询。找到你需要多少EXPLAIN ANALYZE。
work_mem
SET LOCAL
EXPLAIN ANALYZE
我在65579 行的真实表上使用 PostgreSQL 9.1运行了三个测试,并且在所涉及的三列中的每一列上都有单列 btree 索引,并获得了 5 次运行的最佳执行时间。 将@OMGPonies的第一个查询 ( A) 与上述DISTINCT ON解决方案( B) 进行比较:
A
B
A: 567.218 ms B: 386.673 ms
WHERE customer BETWEEN x AND y
A: 249.136 ms B: 55.111 ms
WHERE customer = x
A: 0.143 ms B: 0.072 ms
使用另一个答案中描述的索引重复相同的测试
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