在PostgreSQL中，另一种可能是将first_value窗口函数与SELECT DISTINCT结合使用：

select distinct customer_id,
                first_value(row(id, total)) over(partition by customer_id order by total desc, id)
from            purchases;

我创建了一个组合（id，total），因此两个值都由同一个聚合返回。当然，您可以始终应用first_value（）两次。

在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个相关行

基准

请参见单独的答案。

这是一个常见的最大的每组问题，它已经有了经过充分测试和高度优化的解决方案。就我个人而言，我更喜欢比尔·卡尔温（Bill Karwin）的左联解决方案（原始帖子中有很多其他解决方案）。

请注意，MySQL手册中可以意外地找到解决这个常见问题的一系列解决方案——尽管您的问题是在Postgres中，而不是MySQL中，但给出的解决方案应该适用于大多数SQL变体。请参见常见查询的示例：：保持某一列的组最大值的行。

查询：

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

这是怎么回事！（我去过那里）

我们希望确保每次购买的总金额最高。

---

一些理论知识（如果您只想了解查询，请跳过此部分）

让Total是一个函数T（customer，id），其中它返回一个给定名称和id的值为了证明给定的总数（T（customer，id））是最高的，我们必须证明我们想证明

∀x T（customer，id）>T（customer，x）（这个总数高于所有其他该客户的总计）

OR

∃x T（customer，id）<T（customers，x）（不存在更高的总数该客户）

第一种方法需要我们获取我不太喜欢的名字的所有记录。

第二个将需要一个聪明的方式来表示，没有比这个更高的记录了。

---

返回SQL

如果我们在表的名称和总数小于连接表的情况下留下连接表：

LEFT JOIN purchases as p 
ON 
p.customer = purchases.customer 
AND 
purchases.total < p.total

我们确保要加入的同一用户的另一条记录总数较高的所有记录：

+--------------+---------------------+-----------------+------+------------+---------+
| purchases.id |  purchases.customer | purchases.total | p.id | p.customer | p.total |
+--------------+---------------------+-----------------+------+------------+---------+
|            1 | Tom                 |             200 |    2 | Tom        |     300 |
|            2 | Tom                 |             300 |      |            |         |
|            3 | Bob                 |             400 |    4 | Bob        |     500 |
|            4 | Bob                 |             500 |      |            |         |
|            5 | Alice               |             600 |    6 | Alice      |     700 |
|            6 | Alice               |             700 |      |            |         |
+--------------+---------------------+-----------------+------+------------+---------+

这将有助于我们在不需要分组的情况下筛选每次购买的最高总额：

WHERE p.total IS NULL
    
+--------------+----------------+-----------------+------+--------+---------+
| purchases.id | purchases.name | purchases.total | p.id | p.name | p.total |
+--------------+----------------+-----------------+------+--------+---------+
|            2 | Tom            |             300 |      |        |         |
|            4 | Bob            |             500 |      |        |         |
|            6 | Alice          |             700 |      |        |         |
+--------------+----------------+-----------------+------+--------+---------+

这就是我们需要的答案。

我使用这种方式（仅限postgresql）：https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

那么，您的示例应该大致如下：

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

CAVEAT：它忽略NULL行

---

编辑1-改用postgres扩展名

现在我用这种方式：http://pgxn.org/dist/first_last_agg/

要在ubuntu 14.04上安装：

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

它是一个postgres扩展，为您提供第一个和最后一个函数；显然比上述方式更快。

---

编辑2-排序和筛选

如果使用聚合函数（如以下函数），则可以对结果进行排序，而无需对数据进行排序：

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

因此，具有排序的等效示例如下：

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

当然，您可以根据您认为合适的情况在聚合中进行排序和过滤；这是非常强大的语法。

基准

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

最初使用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