在每个GROUP BY组中选择第一行？
正如标题所示，我想选择用GROUP BY分组的每组行的第一行。
具体来说，如果我有一个看起来像这样的purchases表：
SELECT * FROM purchases;
我的输出：
id | customer | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
我想查询每个customer所做的最大购买（ total ） id 。 像这样的东西：
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
在Oracle 9.2+（不像原来的8i +），SQL Server 2005+，PostgreSQL 8.4+，DB2，Firebird 3.0+，Teradata，Sybase，Vertica：
WITH summary AS (
SELECT p.id,
p.customer,
p.total,
ROW_NUMBER() OVER(PARTITION BY p.customer
ORDER BY p.total DESC) AS rk
FROM PURCHASES p)
SELECT s.*
FROM summary s
WHERE s.rk = 1
任何数据库支持：
但是你需要添加逻辑来打破关系：
SELECT MIN(x.id), -- change to MAX if you want the highest
x.customer,
x.total
FROM PURCHASES x
JOIN (SELECT p.customer,
MAX(total) AS max_total
FROM PURCHASES p
GROUP BY p.customer) y ON y.customer = x.customer
AND y.max_total = x.total
GROUP BY x.customer, x.total
在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;
如果total可以是NULL（不会伤害任何一种方式，但是你想匹配现有的索引）：
...
ORDER BY customer, total DESC NULLS LAST, id;主要观点
DISTINCT ON是标准的PostgreSQL扩展（在整个SELECT列表中只定义了DISTINCT ）。
在DISTINCT ON子句中列出任意数量的表达式，组合的行值定义重复项。 手册：
显然，如果至少有一列值不同，则两行被认为是不同的。 在这个比较中，空值被认为是相等的。
大胆重视我的。
DISTINCT ON可以与ORDER BY结合使用。 领先的表达式必须以相同的顺序匹配领先的DISTINCT ON表达式。 您可以向ORDER BY添加额外的表达式以从每组对等中选择一个特定的行。 我添加了id作为最后一项打破关系：
“选择共享最高total每个组中具有最小id的行。”
如果total可以为NULL，那么最有可能需要具有最大非空值的行。 像演示一样添加NULLS LAST 。 细节：
SELECT列表不受DISTINCT ON或ORDER BY中的表达式的限制。 （在上面的简单情况下不需要）：
您不必在DISTINCT ON或ORDER BY包含任何表达式。
您可以在SELECT列表中包含任何其他表达式。 这有助于用子查询和聚合/窗口函数替换更复杂的查询。
我使用8.3-10版本进行了测试。但是至少从版本7.1开始，这个功能一直存在，所以基本上总是这样。
指数
上述查询的完美索引将是一个多列索引，它跨越匹配序列中的所有三列并具有匹配的排序顺序：
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
可能太专门针对真实世界的应用程序。 但如果读取性能至关重要，请使用它。 如果查询中有DESC NULLS LAST ，则在索引中使用相同的名称，以便Postgres知道排序顺序匹配。
有效性/性能优化
在为每个查询创建定制索引之前，您必须权衡成本和收益。 上述指数的潜力很大程度上取决于数据分布 。
使用索引是因为它提供了预排序数据，而在Postgres 9.2或更高版本中，如果索引小于基础表，则查询也可以受益于仅索引扫描 。 但索引必须全部扫描。
对于每个客户几行 ，这是非常有效的（如果你需要排序输出，更是如此）。 随着每位客户数量不断增加，收益也随之缩小。
理想情况下，您有足够的work_mem来处理RAM中涉及的排序步骤，并且不会泄漏到磁盘。 通常将work_mem设置得太高会产生不利影响。 考虑大集合上的奇异查询的SET LOCAL 。 通过EXPLAIN ANALYZE查找您需要多少。 在排序步骤中提及“磁盘：”表示需要更多：
对于每个客户的许多行 ， 松散的索引扫描会（效率更高），但目前尚未在Postgres中实现（最高为v10）。
有更快的查询技术来替代这个。 特别是如果你有一个单独的表格，持有独特的客户，这是典型的用例。 但是，如果你不这样做：
基准
对于Postgres 9.1而言，我有一个简单的基准，到2016年已经过时了。所以我运行了一个新的Postgres 9.4和9.5的更好，可重复的设置，并在另一个答案中增加了详细的结果 。
基准
使用Postgres 9.4和9.5测试最有趣的候选人， purchases中包含200k行的中间真实表格， 10k不同的customer_id （平均每位客户20行）。
对于Postgres 9.5，我对86446个不同的客户进行了第二次测试。 见下文（平均每位客户2.3行）。
建立
主表
CREATE TABLE purchases (
id serial
, customer_id int -- REFERENCES customer
, total int -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);
我使用了一个serial （PK约束下面添加）和一个整数customer_id因为这是一个更典型的设置。 还添加了some_column来弥补通常更多的列。
虚拟数据，PK，索引 - 一个典型的表也有一些死元组：
INSERT INTO purchases (customer_id, total, some_column) -- insert 200k rows
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,200000) g;
ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);
DELETE FROM purchases WHERE random() > 0.9; -- some dead rows
INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int AS customer_id -- 10k customers
, (random() * random() * 100000)::int AS total
, 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM generate_series(1,20000) g; -- add 20k to make it ~ 200k
CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);
VACUUM ANALYZE purchases;
customer表 - 用于优越的查询
CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM purchases
GROUP BY 1
ORDER BY 1;
ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);
VACUUM ANALYZE customer;
在我的第二次 9.5 测试中 ，我使用了相同的设置，但使用random() * 100000生成customer_id ，以便每个customer_id只获得几行。
purchases表格的对象大小
使用此查询生成。
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 20496384 | 20 MB | 102
visibility_map | 0 | 0 bytes | 0
free_space_map | 24576 | 24 kB | 0
table_size_incl_toast | 20529152 | 20 MB | 102
indexes_size | 10977280 | 10 MB | 54
total_size_incl_toast_and_indexes | 31506432 | 30 MB | 157
live_rows_in_text_representation | 13729802 | 13 MB | 68
------------------------------ | | |
row_count | 200045 | |
live_tuples | 200045 | |
dead_tuples | 19955 | |
查询
1.在CTE中的row_number() ，（请参阅其他答案）
WITH cte AS (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
)
SELECT id, customer_id, total
FROM cte
WHERE rn = 1;
2.子查询中的row_number() （我的优化）
SELECT id, customer_id, total
FROM (
SELECT id, customer_id, total
, row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM purchases
) sub
WHERE rn = 1;
3. DISTINCT ON （查看其他答案）
SELECT DISTINCT ON (customer_id)
id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC, id;
4.带有LATERAL子查询的rCTE（请参阅此处）
WITH RECURSIVE cte AS (
( -- parentheses required
SELECT id, customer_id, total
FROM purchases
ORDER BY customer_id, total DESC
LIMIT 1
)
UNION ALL
SELECT u.*
FROM cte c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id > c.customer_id -- lateral reference
ORDER BY customer_id, total DESC
LIMIT 1
) u
)
SELECT id, customer_id, total
FROM cte
ORDER BY customer_id;
5. customer表与LATERAL （见这里）
SELECT l.*
FROM customer c
, LATERAL (
SELECT id, customer_id, total
FROM purchases
WHERE customer_id = c.customer_id -- lateral reference
ORDER BY total DESC
LIMIT 1
) l;
6.使用ORDER BY array_agg() （请参阅其他答案）
SELECT (array_agg(id ORDER BY total DESC))[1] AS id
, customer_id
, max(total) AS total
FROM purchases
GROUP BY customer_id;
结果
使用EXPLAIN ANALYZE （以及所有选项关闭）执行上述查询的执行时间，5次运行的最佳时间。
所有查询都使用了索引只扫描 purchases2_3c_idx （以及其他步骤）。 其中一些仅用于索引的较小尺寸，另一些则更有效。
A. Postgres 9.4具有200k行，每个customer_id约为20
1. 273.274 ms
2. 194.572 ms
3. 111.067 ms
4. 92.922 ms
5. 37.679 ms -- winner
6. 189.495 ms
B.与Postgres 9.5相同
1. 288.006 ms
2. 223.032 ms
3. 107.074 ms
4. 78.032 ms
5. 33.944 ms -- winner
6. 211.540 ms
C.与B.相同，但每个customer_id约2.3行
1. 381.573 ms
2. 311.976 ms
3. 124.074 ms -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms
2011年原始（过时）基准
我在包含65579行的实际生命表上运行了PostgreSQL 9.1的三个测试，并在涉及的三列的每一列上运行了单列btree索引，并且花费了5次运行的最佳执行时间。
比较@OMGPonies的第一个查询（ A ）和上面的DISTINCT ON解决方案（ B ）：
选择整个表格，在这种情况下会产生5958行。
A: 567.218 ms
B: 386.673 ms
使用条件WHERE customer BETWEEN x AND y导致1000行。
A: 249.136 ms
B: 55.111 ms
选择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
上一篇: Select first row in each GROUP BY group?
下一篇: How do I see the script of a table in SQL Server 2008 Management Studio?