DENSE_RANK()函数详解：MySQL与Hive中的分组排序实战

DENSE_RANK()函数详解：MySQL与Hive中的分组排序实战

引用

CSDN

1.

https://m.blog.csdn.net/weixin_49114503/article/details/143233557

在数据库查询中，排名函数是数据分析中非常重要的工具。其中，DENSE_RANK()函数因其独特的排名方式，在处理重复值时具有明显优势。本文将详细介绍DENSE_RANK()函数的使用方法，并通过具体案例展示其在MySQL和Hive中的应用。

一、dense_rank() 函数

DENSE_RANK()也是一个窗口函数，用于为结果集中的每一行分配排名。DENSE_RANK() 在遇到相同的排序值时，会为相同的行分配相同的排名，并且下一个排名不会跳过。换句话说，如果有两个排名为1的值，接下来的值将会被标记为2，而不是3。

语法结构：

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

• PARTITION BY：用于将结果集分区，针对每个分区单独排名。
• ORDER BY：指定排序的列，决定了排名的依据。

注意，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

二、使用案例

数据准备：

create table `student`(
    id int(10) not null primary key,
    name varchar(20) not null,
    score int(10) not null
);
insert into `student` values(1,'a',100);
insert into `student` values(2,'b',100);
insert into `student` values(3,'c',95);
insert into `student` values(4,'d',95);
insert into `student` values(5,'e',95);
insert into `student` values(6,'a',90);
insert into `student` values(7,'a',89);

2.1、按成绩进行排名

select *,dense_rank() over(order by score desc) as dr from `student`;

两个并列第一名后，下一个是第二名，不会跳过排名。上图中over中没有使用partition进行分组，默认都是同一组

2.2、获取排名前五的数据

select * from (
  select name, score, dense_rank() over (order by score desc) as dr
  from `student`
) a  where `dr` <=5;

2.3、分组后再排名

select name,score,dense_rank() over(partition by name order by score desc) as dr
from `student`;

首先，PARTITION BY子句按姓名将结果集分成多个分区。
然后，ORDER BY子句按分数对结果集进行排序。

三、总结

在数据分析中，ROW_NUMBER()、RANK() 和 DENSE_RANK() 是非常有用的工具。它们可以帮助用户快速对数据进行排名和分类分析。虽然这三种函数的作用相似，但因其在处理重复值时的行为不同，所以在使用时需要根据具体需求进行选择。

3.1、row_number()、rank() 和 dense_rank() 的区别

• ROW_NUMBER()：为每一行分配唯一的行号，适合唯一标识需求。
• RANK()：为重复值分配相同的排名，并在后续排名中跳过名次，适合需要处理排名的场景。
• DENSE_RANK()：为重复值分配相同的排名，但不跳过名次，适合希望连续排名的场景。

下面表格总结了这三个函数的主要区别：