SELECT DISTINCT ON (expr) ... 为 expr 的每个不同值返回一行:也就是按 ORDER BY 顺序排在该组最前面的那一行。先按分组键排序,再按决定“谁排第一”的列排序。要取每位客户的最新订单,就写 ORDER BY customer, created_at DESC。
结果有三行:asha 的订单 2、ben 的订单 4 和 chen 的订单 5,都是各自最近的一单。DISTINCT ON 是 PostgreSQL 的扩展。其他数据库需要用下面介绍的窗口函数写法。
ORDER BY 如何决定保留哪一行
PostgreSQL 先对行排序,然后每当 DISTINCT ON 的值发生变化时保留第一行。所以:
ORDER BY必须以DISTINCT ON的表达式开头,否则 PostgreSQL 会报错“SELECT DISTINCT ON expressions must match initial ORDER BY expressions”。- 如果
ORDER BY在这些表达式之后没有别的排序列,保留哪一行是无法预测的,每次运行都可能不同。
把 created_at DESC 改成 total DESC,同样的查询就会返回每位客户金额最大的订单:
现在 asha 保留的是订单 1(40),ben 保留的是订单 3(60)。
主动处理并列
当两行在排序列上并列时,哪一行都有可能胜出。最后加一个唯一的排序键,比如主键,这样每次的结果都一样:
第 2 行和第 3 行都是 95 分。用 id 作为最后一个键,第 2 行胜出。
按多列分组
列出多个表达式,就能为每种组合返回一行:
每个传感器和类型的组合保留最新的读数:s1 humidity 40、s1 temp 22、s2 temp 19。
可移植的 ROW_NUMBER 写法
ROW_NUMBER() 为每个分区内的行编号。保留编号为 1 的行,结果和 DISTINCT ON 相同,而且在 MySQL 8、SQL Server 和 SQLite 中也能用:
SELECT customer, id, total, created_at
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
FROM orders o
) ranked
WHERE rn = 1;
需要每组前 n 行(rn <= 3),或者查询要在其他数据库上运行时,就用这种写法。窗口函数页面讲解了 PARTITION BY。
让它更快
DISTINCT ON 会对输入排序,所以在大表上要给它一个和 ORDER BY 一致的索引:
CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);
这样 PostgreSQL 就能按顺序读取行,不必再排序。如果分组很少而每组的行很多,用 LATERAL 连接从同一个索引中为每位客户取 LIMIT 1,通常读取的行更少。LATERAL 的用法见连接页面。在你的数据上用 EXPLAIN ANALYZE 比较这两种写法。
常见问题
DISTINCT 和 DISTINCT ON 有什么区别?
DISTINCT 去掉所有选中列都完全相同的行。DISTINCT ON (a) 为 a 的每个值保留一行,其他列来自该组中排在最前面的那一行。
DISTINCT ON 是标准 SQL 吗?
不是。它是 PostgreSQL 的扩展,所以在 MySQL 或 SQL Server 中不能用。可移植的写法是在子查询中使用 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...),再筛选出行号为 1 的行。
为什么会报错“SELECT DISTINCT ON expressions must match initial ORDER BY expressions”?
ORDER BY 必须以和 DISTINCT ON 相同的表达式开头,顺序也要一样。应该写 DISTINCT ON (customer) ... ORDER BY customer, created_at DESC,而不是只写 ORDER BY created_at DESC。
DISTINCT ON 能返回每组的前 3 行吗?
不能,它每组只返回一行。要取前 n 行,用 ROW_NUMBER() 给行编号并保留 rn <= 3,或者用带 LIMIT 3 的 LATERAL 子查询。