Select first row in each GROUP BY group?

技术背景

在数据库查询中，经常会遇到需要从每个分组中选取第一行数据的需求，例如在统计每个客户的最大购买金额时，需要找出每个客户对应的最大购买金额的那一行记录。这是一个常见的 greatest-n-per-group 问题，不同的数据库有不同的解决方案，本文主要讨论在 PostgreSQL 中的实现方法及性能优化。

实现步骤

1. 使用 `DISTINCT ON`

DISTINCT ON 是 PostgreSQL 对标准 SQL 的扩展，通常是最简单和最快的方法。示例代码如下：

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

如果 total 可能为 null，可以添加 NULLS LAST：

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC NULLS LAST, id;

2. 使用 `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;

3. 使用 `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;

4. 使用递归 CTE（rCTE）与 `LATERAL` 子查询

WITH RECURSIVE cte AS (
   (
   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
      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
   ORDER  BY total DESC
   LIMIT  1
   ) l;

核心代码

以下是几种常见方法的核心代码示例：

`DISTINCT ON` 方法

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

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

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

最佳实践

数据分布与索引：对于每个客户行数较少（customer 列基数高）的情况，使用 DISTINCT ON 并结合合适的多列索引（如 CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);）非常高效。对于每个客户行数较多（customer 列基数低）的情况，在 Postgres 18 及以后可以使用索引跳过扫描（Index skip scan），之前可以考虑使用递归 CTE 或带有 LATERAL 子查询的方法。
work_mem 设置：确保有足够的 work_mem 来处理排序步骤，避免溢出到磁盘。可以使用 EXPLAIN ANALYZE 来确定所需的 work_mem 大小。