本文主要是介绍MySQL使用窗口函数ROW_NUMBER()、DENSE_RANK()查询每组第一名或每组前几名,窗口函数使用详解,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
MySQL数据表结构
创建 tbl_class_info 表,表中有四个字段 id、username、score、group_name
使用 ROW_NUMBER()、DENSE_RANK() 查询每组前三名
-- 查询每组前3名
SELECT username, score, group_name
FROM ( SELECT username, score, group_name, ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY score DESC) AS test_rank,DENSE_RANK() OVER (PARTITION BY group_name ORDER BY score DESC) AS test_dense_rank FROM tbl_class_info
) AS ranked_scores
WHERE test_rank <= 3 OR test_dense_rank <= 3
查询结果:
使用 ROW_NUMBER()、DENSE_RANK() 查询第二名
-- 查询每组第2名
SELECT username, score, group_name
FROM ( SELECT username, score, group_name, ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY score DESC) AS test_rank FROM tbl_class_info
) AS ranked_scores
WHERE test_rank = 2
查询结果:
使用 ROW_NUMBER()、DENSE_RANK() 查询结果是不同的,
ROW_NUMBER()和DENSE_RANK()是常见的窗口函数,它们可以用于对结果集中的行进行排序和编号,它们的主要区别在于 使用DENSE_RANK()函数为每个组(group_name
)中的记录根据成绩(score
)降序排列,并为每组中的行分配一个唯一的序号(rank
)。
与ROW_NUMBER()不同,如果存在相同的成绩,DENSE_RANK()会为它们分配连续的序号,而不会跳过任何数字。下面是实例说明:
ROW_NUMBER()、DENSE_RANK() 区别详解
使用ROW_NUMBER()查询数据:
SELECTusername,score,group_name,ROW_NUMBER() OVER (PARTITION BY group_name
ORDER BYscore DESC) AS rank_number
FROMtbl_class_info;
执行结果可以看到,即使成绩(score)相同,rank_number 序号也是不同的
使用 DENSE_RANK() 查询数据:
SELECTusername,score,group_name,DENSE_RANK() OVER (PARTITION BY group_name
ORDER BYscore DESC) AS rank_number
FROMtbl_class_info;
执行结果可以看到,即使成绩(score)相同,rank_number 序号也是相同的
所以总的来说,ROW_NUMBER() 和 DENSE_RANK() 都是非常有用的窗口函数,它们可以用于各种数据分析任务。但是,它们之间的区别意味着它们适用于不同的场景。如果您需要为每个行分配唯一的数字,即使在有重复值的情况下也是如此,那么 ROW_NUMBER() 是更好的选择。如果您需要为每个行分配唯一的数字,但如果有重复值,则需要跳过重复值,那么 DENSE_RANK() 是更好的选择。
MySQL窗口函数的使用语法
SELECT <窗口函数> OVER ([PARTITION BY <表达式>][ORDER BY <表达式>][ROWS BETWEEN <表达式> AND <表达式>]
)
FROM <表名>
其中:
- <窗口函数> 是窗口函数的名称。
- PARTITION BY <表达式> 是分区表达式。分区表达式用于将表中的行分成多个分区。
- ORDER BY <表达式> 是排序表达式。排序表达式用于对分区中的行进行排序。
- ROWS BETWEEN <表达式> AND <表达式> 是行范围表达式。行范围表达式用于指定窗口函数计算的行的范围。
SELECTgroup_name,AVG(score) OVER (PARTITION BY group_name
ORDER BYscore DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_sales
FROMtbl_class_info;
执行结果:
注:在窗口函数中,ROWS BETWEEN <表达式> AND <表达式> 用于指定窗口的边界范围。这个范围是根据指定的表达式来确定的。通常,第一个表达式指定了窗口的起始行,第二个表达式指定了窗口的结束行。
在计算窗口函数之前,数据库会先确定窗口的范围。然后,根据指定的窗口范围,对范围内的行进行运算。通常,窗口函数会对窗口内的每一行执行计算,并返回一个与窗口范围相对应的结果。
MySQL中常见的窗口函数有哪些
SUM()函数:计算指定列的总和。
SELECT column1, SUM(column2) OVER (PARTITION BY column1) AS total_sum
FROM table;
AVG()函数:计算指定列的平均值。
SELECT column1, AVG(column2) OVER (PARTITION BY column1) AS average_value
FROM table;
MIN()函数:返回指定列的最小值。
SELECT column1, MIN(column2) OVER (PARTITION BY column1) AS minimum_value
FROM table;
MAX()函数:返回指定列的最大值。
SELECT column1, MAX(column2) OVER (PARTITION BY column1) AS maximum_value
FROM table;
COUNT()函数:计算指定列的非空值的数量。
SELECT column1, COUNT(column2) OVER (PARTITION BY column1) AS count_value
FROM table;
RANK()函数:返回一组行的排名。
SELECT column1, RANK() OVER (ORDER BY column2 DESC) AS rank_value
FROM table;
DENSE_RANK()函数:返回一组行的不间断排名。
SELECT column1, DENSE_RANK() OVER (ORDER BY column2 DESC) AS dense_rank_value
FROM table;
ROW_NUMBER()函数:为一组行分配一个唯一的数字。
SELECT column1, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS row_number_value
FROM table;
MySQL窗口函数和聚合函数的区别
窗口函数和聚合函数都是在数据库中对数据进行计算的函数。但是,它们之间有一些关键区别。
- 窗口函数返回一个单个值,该值是基于一组行计算的。这组行称为窗口。窗口函数可以使用窗口中的所有行,也可以使用窗口中的部分行。
- 聚合函数返回一个单个值,该值是基于整个表中的数据计算的。聚合函数不能使用窗口,因为它们需要访问整个表的数据才能计算结果。
SELECT SUM(sales) FROM orders;SELECT SUM(sales) OVER (PARTITION BY product_id ORDER BY order_date);
第一个查询使用聚合函数 SUM 计算整个表中所有订单的总销售额。
第二个查询使用窗口函数 SUM 计算每个产品的总销售额。窗口函数 SUM 使用 ORDER BY 子句对订单按日期排序,然后计算每个产品的总销售额。
窗口函数和聚合函数都是在数据库中对数据进行计算的强大工具。但是,它们之间有一些关键区别,因此在选择使用哪种函数时需要考虑这些区别。
这篇关于MySQL使用窗口函数ROW_NUMBER()、DENSE_RANK()查询每组第一名或每组前几名,窗口函数使用详解的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!