选择每个 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

答案

在PostgreSQL 中，这通常更简单，更快 （下面的性能优化更多）：

SELECT <b>DISTINCT ON</b> (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 <b>NULLS LAST</b>, id;

主要观点

DISTINCT ON是标准的 PostgreSQL 扩展（其中只定义了整个SELECT列表中的DISTINCT ）。
在DISTINCT ON子句中列出任意数量的表达式，组合的行值定义重复项。手册：
显然，如果两行在至少一个列值上不同，则认为它们是不同的。 在此比较中，空值被认为是相等的。
大胆强调我的。
DISTINCT ON可以与ORDER BY结合使用。前导表达式必须以相同的顺序匹配前导DISTINCT ON表达式。您可以向ORDER BY添加其他表达式，以从每个对等组中选择一个特定行。我添加了id作为最后一项来打破关系：
“从每个组中选择id最小的行，共享最高total 。”
要以不同于确定每个组的第一个排序顺序的方式对结果进行排序，您可以将查询嵌套在具有另一个ORDER BY的外部查询中。喜欢：
- PostgreSQL DISTINCT ON 与不同的 ORDER BY
如果total可以为 NULL，则您很可能希望具有最大非 null 值的行。像演示一样添加NULLS LAST 。细节：
- PostgreSQL 按日期时间 asc 排序，先是 null 吗？
SELECT列表不受任何方式的DISTINCT ON或ORDER BY中的表达式约束。（在上面的简单案例中不需要）：
- 您不必在DISTINCT ON或ORDER BY包含任何表达式。
- 您可以在SELECT列表中包含任何其他表达式。这有助于用子查询和聚合 / 窗口函数替换更复杂的查询。
我使用 Postgres 版本 8.3 - 11 进行了测试。但是至少从版本 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查找您需要多少。在排序步骤中提到 “ 磁盘： ” 表示需要更多：
- Linux 上 PostgreSQL 中的配置参数 work_mem
- 使用 ORDER BY 日期和文本优化简单查询
对于每个客户的许多行 （列customer低基数）， 松散的索引扫描 （也称为 “跳过扫描”）将会（更高）更高效，但是直到 Postgres 11 还没有实现。（仅索引扫描的实现是计划在 Postgres 12. 看到这里和这里。）
目前，有更快的查询技术来替代它。特别是如果您有一个单独的表，其中包含唯一的客户，这是典型的用例。但如果你不这样做：

基准

我在这里有一个简单的基准，现在已经过时了。我在这个单独的答案中用详细的基准代替了它。

在 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

基准

使用 Postgres 9.4和9.5测试最有趣的候选者， purchases中有20 万行的中间实际表和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. 带有`LATERAL` `customer`表（见这里）

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 年的原始（过时）基准

我在 PostgreSQL 9.1上运行了三次测试，在一个包含 65579 行的实际生命表上，在所涉及的三列中的每一列上都有单列 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

答案

主要观点

指数

有效性 / 性能优化

基准

在 Oracle 9.2+（不是最初的 8i +），SQL Server 2005 +，PostgreSQL 8.4 +，DB2，Firebird 3.0 +，Teradata，Sybase，Vertica：

任何数据库支持：

基准

建立

表purchases对象大小

查询

1. CTE 中的row_number() ，（ 见其他答案 ）

2. 子查询中的row_number() （我的优化）

3. DISTINCT ON （ 见其他答案 ）

4. 带有LATERAL子查询的 rCTE（ 见这里 ）

5. 带有LATERAL customer表（ 见这里 ）

6. 带有ORDER BY array_agg() （ 参见其他答案 ）

结果

A. Postgres 9.4 有 200k 行，每个customer_id约 20