(全文目录:)
开篇语
今天我要给大家分享一些自己日常学习到的一些知识点,并以文字的形式跟大家一起交流,互相学习,一个人虽可以走的更快,但一群人可以走的更远。
我是一名后端开发爱好者,工作日常接触到最多的就是Java语言啦,所以我都尽量抽业余时间把自己所学到所会的,通过文章的形式进行输出,希望以这种方式帮助到更多的初学者或者想入门的小伙伴们,同时也能对自己的技术进行沉淀,加以复盘,查缺补漏。
小伙伴们在批阅的过程中,如果觉得文章不错,欢迎点赞、收藏、关注哦。三连即是对作者我写作道路上最好的鼓励与支持!
前言
在上一期,我们聊了 MySQL 和 PostgreSQL 数据迁移的利器 Ape-DTS,相信你已经对数据同步有了更深的了解。不过在日常开发中,我们处理数据时总会遇到 "排序" 和 "标记序号" 的需求。比如,如何为查询结果自动编号,或者挑出每组数据中的第一条记录?这时候,PostgreSQL 提供的 ROW_NUMBER()
函数就闪亮登场了!今天,我们将深入解析 ROW_NUMBER()
的用法,让你轻松玩转数据的排序与分组。
摘要
ROW_NUMBER()
是 PostgreSQL 中的一个窗口函数,它根据指定的 排序规则 为查询结果中的每一行生成一个唯一的行号。在数据分析、去重、分组排名等场景中,ROW_NUMBER()
都是不可或缺的利器。本文将从基本语法、常用示例、应用场景到性能优化,为你全面剖析 ROW_NUMBER()
的强大用法。
概述
什么是 ROW_NUMBER()?
ROW_NUMBER()
是 PostgreSQL 的一个窗口函数(Window Function)。它的作用是为结果集中的每一行生成一个序号,序号会按照指定的 排序规则 递增,且每行的编号是唯一的。
基本语法
ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)
OVER
:指定窗口函数的工作范围。PARTITION BY
:按指定的列对数据分组(可选)。ORDER BY
:指定行号生成的排序规则。
源码解析
假设我们有一个简单的 sales
表:
id | salesperson | region | amount |
---|---|---|---|
1 | Alice | East | 500 |
2 | Bob | West | 600 |
3 | Alice | East | 700 |
4 | Bob | West | 200 |
5 | Alice | East | 300 |
使用案例分享
1. 基本用法:为结果集生成序号
为 sales
表中的记录生成序号:
SELECT id, salesperson, region, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM sales;
结果:
id | salesperson | region | amount | row_num |
---|---|---|---|---|
3 | Alice | East | 700 | 1 |
2 | Bob | West | 600 | 2 |
1 | Alice | East | 500 | 3 |
5 | Alice | East | 300 | 4 |
4 | Bob | West | 200 | 5 |
2. 分组排名:按组生成序号
使用 PARTITION BY
按 region 列进行分组,并在每组内部按金额降序生成行号:
SELECT id, salesperson, region, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num
FROM sales;
结果:
id | salesperson | region | amount | row_num |
---|---|---|---|---|
3 | Alice | East | 700 | 1 |
1 | Alice | East | 500 | 2 |
5 | Alice | East | 300 | 3 |
2 | Bob | West | 600 | 1 |
4 | Bob | West | 200 | 2 |
3. 获取分组中第一条记录
通过 ROW_NUMBER()
和 WHERE
子句,获取每个区域中金额最高的记录:
SELECT id, salesperson, region, amount
FROM (
SELECT id, salesperson, region, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num
FROM sales
) AS ranked
WHERE row_num = 1;
结果:
id | salesperson | region | amount |
---|---|---|---|
3 | Alice | East | 700 |
2 | Bob | West | 600 |
应用场景案例
- 分页查询
使用ROW_NUMBER()
为查询结果生成序号,并实现分页显示。 - 去重处理
当一组数据中存在重复项时,可以使用ROW_NUMBER()
标记每一行,选择行号为 1 的记录,进行数据去重。 - 分组内排名
对各个分组内部进行排名,用于分析和展示。
优缺点分析
优点
- 生成的行号 唯一,不会重复。
- 支持分组(
PARTITION BY
)和排序(ORDER BY
),灵活性高。 - 可以用于 去重 和 分页查询,减少复杂 SQL 的编写。
缺点
- 在大数据集上,性能可能受到排序和分组的影响。
ROW_NUMBER()
只能在同一查询中生成,不会自动更新。
核心语法回顾
ROW_NUMBER()
:生成唯一的行号。OVER
:定义窗口函数的范围。PARTITION BY
:按列分组。ORDER BY
:按指定列排序。
测试用例
测试场景 1:基本排序
- 输入:一个无分组的表。
- 操作:按
amount
列降序生成序号。 - 预期结果:序号从 1 递增。
测试场景 2:分组内排序
- 输入:一个含
region
列的表。 - 操作:按
region
分组,并按amount
降序生成行号。 - 预期结果:每个分组的行号从 1 开始。
小结
ROW_NUMBER()
是 PostgreSQL 提供的一个强大的窗口函数,它能灵活地为查询结果生成行号,无论是分组排序、去重处理还是分页查询,都能轻松应对。通过合理利用 PARTITION BY
和 ORDER BY
,你可以让数据有序呈现,查询结果更清晰明了。
总结
如果你还在为如何给数据添加行号而发愁,不妨试试 ROW_NUMBER()
!它不仅简单易用,而且功能强大,是数据分析和查询的得力助手。记住,数据之美在于有序,而 ROW_NUMBER()
正是那把让数据井井有条的钥匙!
如果你还有其他数据库相关主题想要了解,欢迎留言讨论!希望这篇文章让你对 PostgreSQL 的 ROW_NUMBER()
有了更加深入的理解。
... ...
文末
好啦,以上就是我这期的全部内容,如果有任何疑问,欢迎下方留言哦,咱们下期见。
... ...
学习不分先后,知识不分多少;事无巨细,当以虚心求教;三人行,必有我师焉!!!
wished for you successed !!!
⭐️若喜欢我,就请关注我叭。
⭐️若对您有用,就请点赞叭。 ⭐️若有疑问,就请评论留言告诉我叭。
版权声明:本文由作者原创,转载请注明出处,谢谢支持!