基准

我测试了最有趣的候选人：

最初使用Postgres 9.4和9.5。后来为Postgres 13添加了重音测试。

基本测试设置

主表：采购：

CREATE TABLE purchases (
  id          serial  -- PK constraint added below
, customer_id int     -- REFERENCES customer
, total       int     -- could be amount of money in Cent
, some_column text    -- to make the row bigger, more realistic
);

虚拟数据（带有一些死元组），PK，索引：

INSERT INTO purchases (customer_id, total, some_column)    -- 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k distinct 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;

客户表-用于优化查询：

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的第二次测试中，我使用了相同的设置，但使用了100000个不同的customer_id来获得每个customer_id的几行。

表购买的对象大小

基本设置：购买200k行，10k个不同的customer_id，平均每个客户20行。对于Postgres9.5，我添加了第二个测试，共有86446个不同的客户-平均每个客户2.3行。

使用此处的查询生成：

测量PostgreSQL表行的大小

为Postgres 9.5收集：

               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的客户表（见此处）

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、TIMING OFF、COSTS OFF）执行上述查询的执行时间，与热缓存相比，这是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.与A.相同，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

2021-08-11年与Postgres 13重新测试

简化的测试设置：没有删除的行，因为VACUUM ANALYZE完全清除了简单情况下的表。

Postgres的重要变化：

一般性能改进。CTE可以从Postgres 12开始内联，因此查询1。和2。现在执行基本相同的（相同的查询计划）。

D.类似B.每个customer_id约20行

1. 103 ms
2. 103 ms  
3.  23 ms  -- winner  
4.  71 ms  
5.  22 ms  -- winner
6.  81 ms  

db<>fiddle在这里

E.类似C.每个customer_id约2.3行

1. 127 ms
2. 126 ms  
3.  36 ms  -- winner  
4. 620 ms  
5. 145 ms
6. 203 ms  

db<>fiddle在这里

参加Postgres 13考试

每个客户有1百万行，10.000对100对1.6行。

F.每个客户约10.000行

1. 526 ms
2. 527 ms  
3. 127 ms
4.   2 ms  -- winner !
5.   1 ms  -- winner !
6. 356 ms  

db<>fiddle在这里

G.每个客户约100行

1. 535 ms
2. 529 ms  
3. 132 ms
4. 108 ms  -- !
5.  71 ms  -- winner
6. 376 ms  

db<>fiddle在这里

H.每个客户约1.6行

1.  691 ms
2.  684 ms  
3.  234 ms  -- winner
4. 4669 ms
5. 1089 ms
6. 1264 ms  

db<>fiddle在这里

结论

DISTINCT ON有效地使用索引，并且通常对每个组的几行执行最佳。即使每个组有很多行，它的性能也很好。对于每个组的许多行，使用rCTE模拟索引跳过扫描的性能最好，仅次于使用单独查找表（如果可用）的查询技术。在当前接受的答案中演示的row_number（）技术从未赢得任何性能测试。那时不行，现在不行。它从未接近DISTINCT ON，即使数据分布对后者不利。row_number（）唯一的优点是：它的扩展性不是很好，只是一般。

更多基准

在Postgres11.5上通过“ogr”进行基准测试，拥有1000万行和60万个独特的“客户”。结果与我们目前所看到的一致：

访问每个标识符的最新行的正确方法？

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客户=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

这可以通过MAX FUNCTION on total和GROUP by id和customer轻松实现。

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

这是我们如何通过使用windows函数实现的：

    create table purchases (id int4, customer varchar(10), total integer);
    insert into purchases values (1, 'Joe', 5);
    insert into purchases values (2, 'Sally', 3);
    insert into purchases values (3, 'Joe', 2);
    insert into purchases values (4, 'Sally', 1);
    
    select ID, CUSTOMER, TOTAL from (
    select ID, CUSTOMER, TOTAL,
    row_number () over (partition by CUSTOMER order by TOTAL desc) RN
    from purchases) A where RN = 1;

在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;

如果总数可以为NULL，请添加NULLS LAST:

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

无论哪种方法都有效，但您需要匹配现有索引

db<>fiddle在这里

主要要点

DISTINCT ON是标准的PostgreSQL扩展，其中只定义了整个SELECT列表中的DISTINCT。

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

显然，如果两行至少在一列值。空值在以下情况下被视为相等比较

我的大胆强调。

DISTINCT ON可以与ORDER BY组合使用。ORDER BY中的前导表达式必须在DISTINCT ON中的表达式集合中，但您可以自由地重新排列这些表达式之间的顺序。实例您可以向ORDERBY添加其他表达式，以从每组对等对象中选择特定的行。或者，如手册所述：

DISTINCT ON表达式必须与最左边的ORDER BY匹配表达式。ORDER BY子句通常包含表达式，用于确定每个DISTINCT ON组。

我添加了id作为最后一个打破联系的项目：“从共享最高总数的每个组中选择id最小的行。”

为了以与确定每组第一个结果的排序顺序不一致的方式对结果进行排序，可以使用另一个ORDERBY.Example将上面的查询嵌套在外部查询中。

如果total可以为NULL，则很可能需要具有最大非空值的行。添加NULLS LAST，如图所示。参见：

按ASC列排序，但先按NULL值排序？

SELECT列表不受DISTINCT ON或ORDER by中表达式的任何约束：

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

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

指数

上述查询的理想索引是一个多列索引，以匹配的顺序和匹配的排序顺序跨越所有三列：

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

可能过于专业化。但如果特定查询的读取性能至关重要，请使用它。如果查询中有DESC NULLS LAST，请在索引中使用相同的名称，以便排序顺序匹配，索引完全适用。

效率/性能优化

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

使用索引是因为它提供了预排序的数据。在Postgres 9.2或更高版本中，如果索引小于基础表，则查询也可以从仅索引扫描中受益。不过，必须对索引进行整体扫描。实例

对于每个客户很少的行（列客户中的基数很高），这是非常有效的。如果您仍然需要排序输出，则更是如此。随着每个客户的行数不断增加，这一优势也在缩小。理想情况下，您有足够的work_mem在RAM中处理相关的排序步骤，而不会溢出到磁盘。但通常将work_mem设置得过高会产生不利影响。对于异常大的查询，请考虑SET LOCAL。通过EXPLAIN ANALYZE了解您需要多少。在排序步骤中提到“磁盘：”表示需要更多：

Linux上PostgreSQL中的配置参数work_mem使用ORDERBY日期和文本优化简单查询

对于每个客户的许多行（列客户中的基数较低），“索引跳过扫描”或“松散索引扫描”将更有效。但直到Postgres 15都没有实现。多年来，以这样或那样的方式实施它的严肃工作一直在进行，但迄今为止尚未成功。看看这里和这里。目前，有更快的查询技术可以替代这一点。特别是如果您有一个单独的表，其中包含唯一的客户，这是典型的用例。但如果你没有：

SELECT DISTINCT比PostgreSQL中我的表上的预期速度慢优化GROUP BY查询以检索每个用户的最新行优化分组最大查询查询每行最后N个相关行

基准

请参见单独的答案。

在支持CTE和窗口功能的数据库上：

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 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

在SQL Server中，可以执行以下操作：

SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1

解释：这里，分组依据是根据客户进行的，然后按总数进行排序，然后给每个这样的组指定序列号为StRank，我们将选出第一个StRank为1的客户