怎么在PostgreSQL 16中使用SQL子查询实现实时去重

轻敏小哥_2584

轻敏小哥_2584

2026-09-05

657人浏览

原创

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

怎么在postgresql 16中使用sql子查询实现实时去重

子查询去重必须配合窗口函数或聚合,单纯 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 技巧绕过,得从数据源头约束或增加校验字段。

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5043

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1329

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7101

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3743

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7100

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

9941

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1350

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1172

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
热门推荐
/
最新课程
phpStudy极速入门视频教程
phpStudy极速入门视频教程

共6课时 | 54.7万人学习

独孤九贱(4)_PHP视频教程
独孤九贱(4)_PHP视频教程

共89课时 | 134.3万人学习