PostgreSQL 如何用 DISTINCT ON 实现分组取最新记录

DISTINCT ON是postgresql特有语法，用于每组保留排序后的首行；而DISTINCT是对整行全字段去重。DISTINCT ON必须与ORDER BY配合，且ORDER BY前导列须与DISTINCT ON列一致并同序。

DISTINCT ON 是什么，和普通 DISTINCT 有什么区别

DISTINCT ON 不是标准 SQL，是 PostgreSQL 特有的语法，用来在去重时保留“每组第一条”记录，而不是像 DISTINCT 那样对整行做全字段比对。它必须配合 ORDER BY 使用，且 ORDER BY 的前导列要和 DISTINCT ON 的列完全一致（顺序也要一致），否则会报错：select DISTINCT ON expressions must match initial ORDER BY expressions。

典型场景是“每个用户取最新一条订单”“每个设备取最近一次上报”——这类需求用 GROUP BY 很难直接满足，因为非分组字段的聚合逻辑不明确；而 DISTINCT ON 能靠排序天然定义“最新”。

怎么写才能正确取出每组最新记录

核心是：把分组字段放在 DISTINCT ON 里，把时间戳/序号字段放在 ORDER BY 的后续位置，并按降序排（DESC）。

• DISTINCT ON (user_id) 表示“每个 user_id 只留一行”
• ORDER BY user_id, created_at DESC 确保同一 user_id 下，created_at 最大的那条排最前，被 DISTINCT ON 拿到
• 如果漏掉 user_id 在 ORDER BY 的开头，会直接报错
• 如果 created_at 允许 NULL，记得加 NULLS LAST，避免 NULL 被当成“最新”

示例：

SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC NULLS LAST;

为什么不能只靠 ORDER BY + LIMIT 1？

单独用 ORDER BY created_at DESC LIMIT 1 只能取全局最新一条，不是“每组最新”。有人试图用子查询或窗口函数替代，但 DISTINCT ON 在多数情况下更简洁、可读性更高、执行计划也更可控。

注意几个易错点：

• 如果分组字段有重复值但没出现在 SELECT 列表里，PostgreSQL 会报错：“column ‘xxx’ must appear in the GROUP BY clause or be used in an aggregate function” —— 实际上是因为 DISTINCT ON 要求所有非 DISTINCT ON 字段都得在 ORDER BY 中有定义依据，最稳妥做法是把分组字段显式写进 SELECT
• 没有索引时，ORDER BY user_id, created_at DESC 可能很慢；建议建联合索引：CREATE INDEX idx_user_created ON orders (user_id, created_at DESC);
• DISTINCT ON 不支持在视图或物化视图中直接用于 INSERT/UPDATE 目标，仅限 SELECT 场景

和窗口函数 ROW_NUMBER() 对比选哪个

功能上等价，但写法和性能表现不同。窗口函数更通用（比如要取每组第 2 条、倒数第 1 条），但写起来啰嗦：

SELECT * FROM (   SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC NULLS LAST) rn   FROM orders ) t WHERE rn = 1;

实际测试中，当数据量大、分组多、排序字段有索引时，DISTINCT ON 通常更快，因为优化器能更早剪枝；而窗口函数必须先算完整个结果集再过滤。但如果需要复用序号做其他判断（比如标记“是否为最新”），窗口函数更灵活。

真正容易被忽略的是：DISTINCT ON 的“第一条”完全依赖 ORDER BY 的稳定性。如果 created_at 有重复，且没用其他字段进一步排序（比如 id DESC），结果可能每次查询不一致 —— 这不是 bug，是设计使然。