子查询去重必须配合窗口函数或聚合,单纯SELECT DISTINCT不算;PostgreSQL 16中真正常用方式是ROW_NUMBER()窗口函数嵌套子查询,按PARTITION BY和确定性ORDER BY标序后取rn=1;NOT EXISTS适用于“无更优记录”逻辑但需索引优化;物化CTE可复用去重结果但非实时;核心在于业务去重依据的稳定性。

子查询去重必须配合窗口函数或聚合,单纯 SELECT DISTINCT 不算子查询去重
很多人以为写个 SELECT * FROM (SELECT DISTINCT ...) 就是“用子查询去重”,其实这只是把去重逻辑包了一层壳,执行计划和直接 DISTINCT 完全一样,没解决实时性或业务逻辑定制问题。真正在 PostgreSQL 16 中靠子查询实现实时去重,核心是让子查询动态决定“哪一条该保留”——比如按时间取最新、按分数取最高、或按业务权重排序后取首行。
ROW_NUMBER() 窗口函数嵌套子查询是最常用且可控的方式
PostgreSQL 16 对窗口函数的优化很成熟,ROW_NUMBER() 在子查询中按分组排序后标序号,外层筛出 rn = 1,就能稳定拿到每组“最新/最优”的那条记录。关键点在于:分区字段、排序字段、是否允许并列(用 RANK() 还是 ROW_NUMBER())要严格匹配业务语义。
- 排序字段必须有确定性:如果只按
created_at排序,但存在毫秒级重复值,需加二级排序(如id)避免结果不稳定 - 分区键不能漏:比如按用户去重,
PARTITION BY user_id缺失会导致全表只标一个序号 - 子查询必须有别名:PostgreSQL 要求内联子查询带别名,否则报错
subquery in FROM must have an alias
示例:取每个用户的最新订单
SELECT id, user_id, amount, created_at
FROM (
SELECT id, user_id, amount, created_at,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC, id DESC
) AS rn
FROM orders
) ranked
WHERE rn = 1;
关联子查询 + NOT EXISTS 适合小数据量或带条件的去重
当去重要基于“不存在更优记录”这种逻辑(比如“没有比它更新的同用户订单”),用 NOT EXISTS 关联子查询反而更直观,且 PostgreSQL 16 的查询规划器对这类结构优化得不错。但它在大数据量下容易变慢,因为每行都要触发一次子查询检查。
- 子查询里的
WHERE条件必须严格对应外层字段,漏写o1.user_id = o2.user_id会变成全局比较 - 记得给被比较字段建索引,例如
(user_id, created_at)复合索引能极大加速NOT EXISTS子查询 - 不能用
!=或替代NOT EXISTS,语义不同且性能差几个数量级
示例:查所有“没有更新订单”的订单(即每个用户的最新订单)
SELECT o1.id, o1.user_id, o1.amount, o1.created_at
FROM orders o1
WHERE NOT EXISTS (
SELECT 1
FROM orders o2
WHERE o2.user_id = o1.user_id
AND o2.created_at > o1.created_at
);
物化 CTE 不是实时去重,但能缓解重复计算压力
如果子查询本身很重(比如含多表 JOIN 或复杂计算),而你要在多个地方复用去重结果,PostgreSQL 16 的物化 CTE(WITH ... AS MATERIALIZED)可强制先算完再复用,避免多次执行相同子查询。但它不是“实时”的——CTE 结果在当前查询内固定,不随外部数据变更动态刷新。
- 默认 CTE 在 PostgreSQL 12+ 已自动物化,但显式写
MATERIALIZED更明确意图,也避免未来版本行为变化 - 非物化 CTE(
NOT MATERIALIZED)可能被内联展开,导致子查询重复执行,尤其在WHERE中多次引用时 - 物化 CTE 会占用临时内存,超大结果集可能触发磁盘落写,观察
EXPLAIN ANALYZE中的Materialize节点耗时
示例:先去重生成用户快照,再关联统计
WITH latest_orders AS MATERIALIZED ( SELECT user_id, MAX(created_at) AS max_time FROM orders GROUP BY user_id ) SELECT u.name, lo.max_time FROM users u JOIN latest_orders lo ON u.id = lo.user_id;
真正难的不是写对语法,而是想清楚“去重依据”在业务上是否稳固——比如用 created_at 当主判据,但应用层时钟不同步或批量导入导致时间乱序,结果就会错。这类问题没法靠 SQL 技巧绕过,得从数据源头约束或增加校验字段。










