PostgreSQL中rank()窗口函数实用指南与示例

2025-07-11 18:50

本文主要是介绍PostgreSQL中rank()窗口函数实用指南与示例,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

《PostgreSQL中rank()窗口函数实用指南与示例》在数据分析和数据库管理中,经常需要对数据进行排名操作,PostgreSQL提供了强大的窗口函数rank(),可以方便地对结果集中的行进行排名...

一、rank()函数简介

rank()是一个窗口函数,用于计算结果集中每一行的排名。它的基本语法如下:

rank() OVER ([PARTITION BY partition_expression] ORDER BY order_expression)
  • PARTITION BY:可选子句,用于将结果集划分为多个分区,排名在每个分区内独立计算。
  • ORDER BY:指定排名的顺序依据。

特点

  • 相同值的行会获得相同的排名。
  • 下一个排名会跳过相同值的数量。例如,如果有两个第一名,下一个排名是第三名。

二、基础示例:部门内员工薪资排名

假设有一个employees表,包含员工姓名、部门和薪资信息。我们希望计算每个部门内员工的薪资排名。

示例数据

首先,创建示例数据:

WITH sample_data AS (
    SELECT * FROM (
        VALUES 
            ('Alice', 'Sales', 50000),
            ('Bob', 'Marketing', 55000),
            ('Charlie', 'Sales', 52000),
            ('David', 'IT', 60000),
            ('Eve', 'Marketing', 55000),
            ('Frank', 'IT', 62000)
    ) AS t(employee_name, department, salary)
)

排名查询

使用rank()函数按部门分区,按薪资降序排名:

SELECT 
    employee_name, 
    department, 
    salary, 
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM 
    sample_data
ORDER BY 
    department, dept_salary_rank;

结果

employee_namedepartmentsalarydept_salary_rank
FrankIT620001
DavidIT600002
BobMarketing550001
EveMarketing550001
CharlieSales520001
AliceSales500002

解释

  • 在IT部门,Frank薪资最高,排名为1;David次之,排名为2。
  • 在Marketing部门,Bob和Eve薪资相同,均排名为1。
  • 在Sales部门,Charlie薪资最高,排名为1;Alice次之,排名为2。

三、高级应用示例

1. 每组Top N记录

场景:找出每个类别中最贵的两个产品。

示例数据

WITH products AS (
    SjsELECT * FROM (
        VALUES 
            (1, 'A', 100),
            (2, 'A', 80),
            (3, 'B', 200),
            (4, 'B', 180),
            (5, 'B', 150),
   http://www.chinasem.cn         (6, 'C', 120)
    ) AS t(product_id, category, price)
)

查询

SELECT * 
FROM (
    SELECT 
        product_id, 
        category, 
        price, 
        RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank
    FROM 
        products
) ranked
WHERE rank <= 2;

结果

product_idcategorypricerank
1A1001
2A802
3B2001
4B1802
6C1201

解释

  • 每个类别中,价格最高的前两个产品被筛选出来。

2. 百分位数计算

场景:计算每个学生的成绩百分位。

示例数据

WITH scores AS (
    SELECT * FROM (
        VALUES 
            ('Student 1', 85),
            ('Student 2', 92),
            ('Student 3', 78),
            ('Student 4', 90),
            ('Student 5', 88)
    ) AS t(student, score)
)

查询

SELECT 
    student, 
    score, 
    RANK() OVER (ORDER BY score) AS rank,
    ROUND(100.0 * RANK() OVER (ORDER BY score) / (SELECT COUNT(*) FROM scores), 2) AS percentile
FROM 
    sco编程res;

结果

studentscorerankpercentile
Student 378120.00
Student 185240.00
Student 588360.00
Student 490480.00
Student 2925100.00

解释

  • 百分位数通过排名除以总记录数并乘以100计算得出。

四、rank()与其他窗口函数的比较

PostgreSQL提供了多个窗口函数用于排名,各有特点:

javascript
函数描述
rank()相同值的行获得相同排名,下一个排名跳过相同值的数量。
dense_rank()相同值的行获得相同排名,下一个排名不跳过,保持连续。
row_number()每行分配唯一的序号,不考虑相同值,即使值相同也会分配不同序号。

示例:rank() vs dense_rank()

示例数据

WITH scores AS (
    SELECT * FROM (
    mGXfcVJYe    VALUES 
            ('Player 1', 100),
            ('Player 2', 95),
            ('Player 3', 95),
            ('Player 4', 90)
    ) AS t(player, score)
)

PostgreSQL中rank()窗口函数实用指南与示例

查询

SELECT 
    player, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM 
    scores;

结果

playerscorerankdense_rank
Player 110011
Player 29522
Player 39522
Player 49043

解释

  • rank()在遇到相同分数时跳过了排名3。
  • dense_rank()在遇到相同分数时不跳过排名,保持连续。

示例:row_number()

场景:为每日的销售记录分配唯一序号,按销售金额降序排列。

示例数据

WITH sales AS (
    SELECT 
        DATE '2023-01-01' AS sale_date, 
        1000 AS amount
    UNION ALL
    SELECT 
        DATE '2023-01-01', 
        1500
    UNION ALL
    SELECT 
        DATE '2023-01-02', 
        1200
    UNION ALL
    SELECT 
        DATE '2023-01-02', 
        1200
)

查询

SELECT 
    sale_date, 
    amount, 
    ROW_NUMBER() OVER (PARTITION BY sale_date ORDER BY amount DESC) AS row_num
FROM 
    sales;

结果

sale_dateamountrow_num
2023-01-0115001
2023-01-0110002
2023-01-0212001
2023-01-0212002

解释

  • 即使同一天有相同的销售金额,row_number()也会为每条记录分配唯一的序号。

五、性能优化建议

使用窗口函数如rank()时,可能会对查询性能产生影响,尤其是在处理大数据集时。以下是一些优化建议:

  1. 使用PARTITION BY合理分区:将数据划分为较小的分区,可以减少每个窗口函数计算的数据量。
  2. 指定ORDER BY明确排序:确保ORDER BY子句明确,避免全表排序带来的性能开销。
  3. 创建适当的索引:在ORDER BYPARTITION BY涉及的列上创建索引,可以加快排序和分区操作。
  4. 限制结果集:如果只需要前N条记录,结合WHERE rank <= N可以减少计算量。

六、总结

PostgreSQL的rank()窗口函数是一个强大的工具,适用于各种排名需求,如部门内薪资排名、每组Top N记录、百分位数计算等。通过合理使用rank()及其相关函数(如dense_rank()row_number()),可以高效地处理复杂的数据分析任务。

关键点回顾

  • rank()函数为相同值的行分配相同的排名,并跳过后续排名。
  • 结合PARTITION BYORDER BY,可以实现多层次的排名需求。
  • 与其他窗口函数(如dense_rank()row_number())相比,rank()在处理并列排名时有独特的行为。
  • 通过优化查询和索引,可以提升窗口函数的性能表现。

希望本文的示例和解释能帮助你在实际项目中更好地应用rank()函数,提升数据处理的效率和准确性!

到此这篇关于PostgreSQL中rank()窗口函数实用指南与示例的文章就介绍到这了,更多相关PostgreSQL rank()窗口函数内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)!

这篇关于PostgreSQL中rank()窗口函数实用指南与示例的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1155367

相关文章

sky-take-out项目中Redis的使用示例详解

《sky-take-out项目中Redis的使用示例详解》SpringCache是Spring的缓存抽象层,通过注解简化缓存管理,支持Redis等提供者,适用于方法结果缓存、更新和删除操作,但无法实现... 目录Spring Cache主要特性核心注解1.@Cacheable2.@CachePut3.@Ca

QT Creator配置Kit的实现示例

《QTCreator配置Kit的实现示例》本文主要介绍了使用Qt5.12.12与VS2022时,因MSVC编译器版本不匹配及WindowsSDK缺失导致配置错误的问题解决,感兴趣的可以了解一下... 目录0、背景:qt5.12.12+vs2022一、症状:二、原因:(可以跳过,直奔后面的解决方法)三、解决方

MySQL中On duplicate key update的实现示例

《MySQL中Onduplicatekeyupdate的实现示例》ONDUPLICATEKEYUPDATE是一种MySQL的语法,它在插入新数据时,如果遇到唯一键冲突,则会执行更新操作,而不是抛... 目录1/ ON DUPLICATE KEY UPDATE的简介2/ ON DUPLICATE KEY UP

Python中Json和其他类型相互转换的实现示例

《Python中Json和其他类型相互转换的实现示例》本文介绍了在Python中使用json模块实现json数据与dict、object之间的高效转换,包括loads(),load(),dumps()... 项目中经常会用到json格式转为object对象、dict字典格式等。在此做个记录,方便后续用到该方

MySQL分库分表的实践示例

《MySQL分库分表的实践示例》MySQL分库分表适用于数据量大或并发压力高的场景,核心技术包括水平/垂直分片和分库,需应对分布式事务、跨库查询等挑战,通过中间件和解决方案实现,最佳实践为合理策略、备... 目录一、分库分表的触发条件1.1 数据量阈值1.2 并发压力二、分库分表的核心技术模块2.1 水平分

SpringBoot请求参数传递与接收示例详解

《SpringBoot请求参数传递与接收示例详解》本文给大家介绍SpringBoot请求参数传递与接收示例详解,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录I. 基础参数传递i.查询参数(Query Parameters)ii.路径参数(Path Va

RabbitMQ 延时队列插件安装与使用示例详解(基于 Delayed Message Plugin)

《RabbitMQ延时队列插件安装与使用示例详解(基于DelayedMessagePlugin)》本文详解RabbitMQ通过安装rabbitmq_delayed_message_exchan... 目录 一、什么是 RabbitMQ 延时队列? 二、安装前准备✅ RabbitMQ 环境要求 三、安装延时队

从基础到高级详解Python数值格式化输出的完全指南

《从基础到高级详解Python数值格式化输出的完全指南》在数据分析、金融计算和科学报告领域,数值格式化是提升可读性和专业性的关键技术,本文将深入解析Python中数值格式化输出的相关方法,感兴趣的小伙... 目录引言:数值格式化的核心价值一、基础格式化方法1.1 三种核心格式化方式对比1.2 基础格式化示例

sysmain服务可以禁用吗? 电脑sysmain服务关闭后的影响与操作指南

《sysmain服务可以禁用吗?电脑sysmain服务关闭后的影响与操作指南》在Windows系统中,SysMain服务(原名Superfetch)作为一个旨在提升系统性能的关键组件,一直备受用户关... 在使用 Windows 系统时,有时候真有点像在「开盲盒」。全新安装系统后的「默认设置」,往往并不尽编

Redis实现高效内存管理的示例代码

《Redis实现高效内存管理的示例代码》Redis内存管理是其核心功能之一,为了高效地利用内存,Redis采用了多种技术和策略,如优化的数据结构、内存分配策略、内存回收、数据压缩等,下面就来详细的介绍... 目录1. 内存分配策略jemalloc 的使用2. 数据压缩和编码ziplist示例代码3. 优化的