(全文目录:)

开篇语

哈喽,各位小伙伴们,你们好呀,我是喵手。运营社区:C站/掘金/腾讯云/阿里云/华为云/51CTO;欢迎大家常来逛逛

  今天我要给大家分享一些自己日常学习到的一些知识点,并以文字的形式跟大家一起交流,互相学习,一个人虽可以走的更快,但一群人可以走的更远。

  我是一名后端开发爱好者,工作日常接触到最多的就是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 BYregion 列进行分组,并在每组内部按金额降序生成行号:

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

应用场景案例

  1. 分页查询
    使用 ROW_NUMBER() 为查询结果生成序号,并实现分页显示。
  2. 去重处理
    当一组数据中存在重复项时,可以使用 ROW_NUMBER() 标记每一行,选择行号为 1 的记录,进行数据去重。
  3. 分组内排名
    对各个分组内部进行排名,用于分析和展示。

优缺点分析

优点

  • 生成的行号 唯一,不会重复。
  • 支持分组(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 BYORDER BY,你可以让数据有序呈现,查询结果更清晰明了。


总结

如果你还在为如何给数据添加行号而发愁,不妨试试 ROW_NUMBER()!它不仅简单易用,而且功能强大,是数据分析和查询的得力助手。记住,数据之美在于有序,而 ROW_NUMBER() 正是那把让数据井井有条的钥匙!


如果你还有其他数据库相关主题想要了解,欢迎留言讨论!希望这篇文章让你对 PostgreSQL 的 ROW_NUMBER() 有了更加深入的理解。

... ...

文末

好啦,以上就是我这期的全部内容,如果有任何疑问,欢迎下方留言哦,咱们下期见。

... ...

学习不分先后,知识不分多少;事无巨细,当以虚心求教;三人行,必有我师焉!!!

wished for you successed !!!


⭐️若喜欢我,就请关注我叭。

⭐️若对您有用,就请点赞叭。 ⭐️若有疑问,就请评论留言告诉我叭。


版权声明:本文由作者原创,转载请注明出处,谢谢支持!