详解MySQL中的PERCENT_RANK函数

2024-06-12 04:44
文章标签 mysql 函数 详解 database rank percent

本文主要是介绍详解MySQL中的PERCENT_RANK函数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

目录

    • 1. 引入
    • 1. 基本使用
    • 2：分组使用
    • 3：处理重复值
    • 4. 使用优势
      • 4.1 手动计算百分等级
      • 4.2 使用 `PERCENT_RANK` 的优势
      • 4.3 使用 `PERCENT_RANK`
    • 5. 总结

在 MySQL 中，PERCENT_RANK 函数用于计算一个值在其分组中的百分等级。
image.png

它的返回值范围是从 0 到 1，表示一个值在排序后的数据集中相对于其他值的位置。百分等级的计算公式为：
P E R C E N T _ R A N K = rank − 1 total_rows − 1 {PERCENT\_RANK} = \frac{\text{rank} - 1}{\text{total\_rows} - 1} PERCENT_RANK=total_rows−1rank−1​

其中，rank 是当前行的排序位置，total_rows 是总行数。

1. 引入

下面通过一个具体例子来说明 PERCENT_RANK 的用法。

假设我们有一个包含学生分数的表 students_scores，表结构如下：

CREATE TABLE students_scores (student_id INT,student_name VARCHAR(50),score DECIMAL(5, 2)
);

我们向表中插入一些数据：

INSERT INTO students_scores (student_id, student_name, score) VALUES
(1, 'Alice', 85.0),
(2, 'Bob', 90.5),
(3, 'Charlie', 78.0),
(4, 'David', 92.0),
(5, 'Eve', 88.0);

现在，我们希望计算每个学生分数的百分等级。可以使用以下 SQL 查询：

SELECTstudent_id,student_name,score,PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank
FROMstudents_scores;

执行上述查询后，将得到以下结果：

student_idstudent_namescorepercent_rank
4David92.00.0000
2Bob90.50.2500
5Eve88.00.5000
1Alice85.00.7500
3Charlie78.01.0000

在这个结果集中，percent_rank 列表示每个学生的分数在所有学生中的相对位置。例如，David 的分数是最高的，因此他的 percent_rank 是 0。Charlie 的分数是最低的，因此他的 percent_rank 是 1。其他学生的 percent_rank 介于 0 和 1 之间，反映了他们的分数在整个分数分布中的相对位置。
image.png

通过这个例子，我们可以看到 PERCENT_RANK 函数如何计算并返回数据集中的每个值的百分等级。

1. 基本使用

假设我们有一个表 employees，包含员工的销售数据：

CREATE TABLE employees (employee_id INT,employee_name VARCHAR(50),sales DECIMAL(10, 2)
);INSERT INTO employees (employee_id, employee_name, sales) VALUES
(1, 'John', 1500.00),
(2, 'Jane', 2000.00),
(3, 'Alice', 2500.00),
(4, 'Bob', 3000.00),
(5, 'Eve', 1000.00);

我们希望计算每个员工销售额的百分等级。可以使用以下查询：

SELECTemployee_id,employee_name,sales,PERCENT_RANK() OVER (ORDER BY sales DESC) AS percent_rank
FROMemployees;

查询结果如下：

employee_idemployee_namesalespercent_rank
4Bob3000.000.0000
3Alice2500.000.2500
2Jane2000.000.5000
1John1500.000.7500
5Eve1000.001.0000

2：分组使用

假设我们有一个包含员工销售数据的表 department_sales，每个员工属于不同的部门：

CREATE TABLE department_sales (employee_id INT,employee_name VARCHAR(50),department VARCHAR(50),sales DECIMAL(10, 2)
);INSERT INTO department_sales (employee_id, employee_name, department, sales) VALUES
(1, 'John', 'Electronics', 1500.00),
(2, 'Jane', 'Electronics', 2000.00),
(3, 'Alice', 'Furniture', 2500.00),
(4, 'Bob', 'Furniture', 3000.00),
(5, 'Eve', 'Electronics', 1000.00),
(6, 'Charlie', 'Furniture', 2800.00);

image.png

我们希望计算每个部门中员工销售额的百分等级。可以使用以下查询：

SELECTemployee_id,employee_name,department,sales,PERCENT_RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS percent_rank
FROMdepartment_sales;

查询结果如下：

employee_idemployee_namedepartmentsalespercent_rank
2JaneElectronics2000.000.0000
1JohnElectronics1500.000.5000
5EveElectronics1000.001.0000
4BobFurniture3000.000.0000
6CharlieFurniture2800.000.5000
3AliceFurniture2500.001.0000

3：处理重复值

假设我们有一个包含学生成绩的表 student_grades，其中有些成绩是重复的：

CREATE TABLE student_grades (student_id INT,student_name VARCHAR(50),grade DECIMAL(5, 2)
);INSERT INTO student_grades (student_id, student_name, grade) VALUES
(1, 'Tom', 85.00),
(2, 'Jerry', 90.00),
(3, 'Anna', 85.00),
(4, 'Mike', 95.00),
(5, 'Sue', 90.00);

我们希望计算每个学生成绩的百分等级。可以使用以下查询：

SELECTstudent_id,student_name,grade,PERCENT_RANK() OVER (ORDER BY grade DESC) AS percent_rank
FROMstudent_grades;

查询结果如下：

student_idstudent_namegradepercent_rank
4Mike95.000.0000
2Jerry90.000.2500
5Sue90.000.2500
1Tom85.000.7500
3Anna85.000.7500

通过以上例子可以看到，PERCENT_RANK 函数在处理不同数据集和需求时都非常灵活和有用。它可以帮助我们更好地理解和分析数据中的分布和排名情况。

4. 使用优势

如果不使用 PERCENT_RANK 函数，我们可以通过子查询和一些数学计算来手动计算百分等级。这种方法相对繁琐，需要多次嵌套查询和排序。下面是一个手动计算百分等级的例子，使用与之前例子相同的 students_scores 表。

4.1 手动计算百分等级

假设我们有以下表数据：

CREATE TABLE students_scores (student_id INT,student_name VARCHAR(50),score DECIMAL(5, 2)
);INSERT INTO students_scores (student_id, student_name, score) VALUES
(1, 'Alice', 85.0),
(2, 'Bob', 90.5),
(3, 'Charlie', 78.0),
(4, 'David', 92.0),
(5, 'Eve', 88.0);

手动计算每个学生分数的百分等级可以通过以下查询实现：

SELECTstudent_id,student_name,score,(SELECT COUNT(*) FROM students_scores AS sub WHERE sub.score < main.score) / (SELECT COUNT(*) - 1 FROM students_scores) AS percent_rank
FROMstudents_scores AS main
ORDER BYscore DESC;

上述查询的结果与使用 PERCENT_RANK 函数的结果是相同的。

4.2 使用 PERCENT_RANK 的优势

  1. 简洁性和易读性：使用 PERCENT_RANK 函数可以简化查询的编写，使得代码更为简洁和易读。手动计算百分等级需要嵌套查询和计算，增加了复杂性。

  2. 性能优化：数据库引擎通常会对窗口函数进行优化，使其执行效率更高。手动计算可能无法充分利用这些优化，从而导致查询性能较低。

  3. 维护性：使用内置函数减少了自定义计算逻辑，当需求发生变化时，代码的维护和修改也更加方便。

  4. 减少错误：手动计算时容易出错，例如在计算总行数、排序以及分组等过程中，使用 PERCENT_RANK 函数可以减少这些人为错误。

4.3 使用 PERCENT_RANK

我们再来回顾一下如何使用 PERCENT_RANK 函数：

SELECTstudent_id,student_name,score,PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank
FROMstudents_scores;

这个查询简单明了，直接利用 PERCENT_RANK 函数计算百分等级，避免了复杂的嵌套查询和计算逻辑。

5. 总结

使用 PERCENT_RANK 函数在简化查询编写、提高性能和减少错误方面具有明显的优势。因此，在可以使用窗口函数的场景下，推荐优先使用 PERCENT_RANK 而不是手动计算百分等级。

这篇关于详解MySQL中的PERCENT_RANK函数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1053224。

相关文章

Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
hdu1171（母函数或多重背包）

hdu1171（母函数或多重背包）

题意：把物品分成两份，使得价值最接近 可以用背包，或者是母函数来解，母函数（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v）（1 + x^v+x^2v+.....+x^num*v） 其中指数为价值，每一项的数目为（该物品数+1）个 代码如下： #include<iostream>#include<algorithm>
阅读更多...
OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

1、简介 无感配网是指在设备联网过程中无需输入热点相关账号信息，即可快速实现设备配网，是一种兼顾高效性、可靠性和安全性的配网方式。 2、配网原理 2.1 通信原理 手机和智能设备之间的信息传递，利用特有的NAN协议实现。利用手机和智能设备之间的WiFi 感知订阅、发布能力，实现了数字管家应用和设备之间的发现。在完成设备间的认证和响应后，即可发送相关配网数据。同时还支持与常规Sof
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
6.1.数据结构-c/c++堆详解下篇（堆排序，TopK问题）

6.1.数据结构-c/c++堆详解下篇（堆排序，TopK问题）

上篇：6.1.数据结构-c/c++模拟实现堆上篇（向下，上调整算法，建堆，增删数据）-CSDN博客 本章重点 1.使用堆来完成堆排序 2.使用堆解决TopK问题 目录 一.堆排序 1.1 思路 1.2 代码 1.3 简单测试 二.TopK问题 2.1 思路(求最小)： 2.2 C语言代码（手写堆） 2.3 C++代码（使用优先级队列 priority_queue）
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2