SQL开窗函数row_number()、rank()、DENSE_RANK()区别和联系_rownumber和rank区别

1函数说明：

row_number()、rank()、DENSE_RANK()都是分组排序函数，用于对数据分组排序，打上顺序、序次标号。

1.1联系：

排序字段组内无重复值时（即排序字段不相等），三个函数无区别（可参考测试截图5-7行）。
如对7、8、9正序排序，序次都是1、2、3

1.2区别：

当排序字段组内有重复值时（对7、7、9正序排序）（可参考测试截图2-4行）：
ROW_NUMBER()，按照指定顺序正常排序，相同序次序次随机（可参考测试截图D列），两个7都可能排在第一位。
序次情况①1、2、3；②2、1、3；（多次运行可能出现随机排序情况，一般系统会内置其他排序方式，使排序固定）
rank()、DENSE_RANK()序次并列，两个7都排在第一位。
区别是rank()函数（可参考测试截图E列）序次并列后的下一条数据会跳跃，序次为1、1、3
dense_rank()函数,（可参考测试截图F列）接着当前序次往下排，序次为1、1、2

2用法示例：

注：测试环境为DataWorks ODPS SQL。

2.1通用语法

明确排序字段：函数() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 DESC )
整表参与排序：函数() OVER(ORDER BY 排序字段 DESC )
（可参考测试截图G列）
实例：
ROW_NUMBER() OVER(PARTITION BY cinema_id ORDER BY num DESC )
ROW_NUMBER() OVER(ORDER BY num DESC )

2.2测试实例

准备工作：

--创建测试表
CREATE table test (
    cinema_id string COMMENT '电影院编号',
    movie_id string COMMENT '电影编号',
    num DECIMAL (18,0) COMMENT '播放量'
)
;
--写入测试数据
INSERT into test
VALUES 
('01','dd01',2000),
('01','dd02',2000),
('01','dd03',1000),
('02','dd01',3000),
('02','dd02',2000),
('02','dd03',1000)
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

测试说明： 测试场景为查看各电影院的电影播放量，从高到低排序。
测试SQL：

SELECT  cinema_id
        ,movie_id
        ,num
        ,ROW_NUMBER() OVER(PARTITION BY cinema_id ORDER BY num DESC ) row_number_num
        --以电影院编号分组，以播放量倒序排序
        ,rank() OVER(PARTITION BY cinema_id ORDER BY num DESC ) rank_num
        ,dense_rank() OVER(PARTITION BY cinema_id ORDER BY num DESC ) dense_rank_num
        ,ROW_NUMBER() OVER(ORDER BY num DESC ) no_partition_num
FROM    test
;
1
2
3
4
5
6
7
8
9
10

测试结果：
文字结果：

cinema_id movie_id num row_number_num rank_num dense_rank_num no_partition_num
01 dd01 2000 1 1 1 2
01 dd02 2000 2 1 1 3
01 dd03 1000 3 3 2 5
02 dd01 3000 1 1 1 1
02 dd02 2000 2 2 2 4
02 dd03 1000 3 3 3 6
结果截图：

测试结果说明：
D列对应row_number()函数，此时电影院1和2分了两组，组内排序独立，每个影院都有自己的播放量1、2、3名。
E列对应rank()函数，电影院01的dd01和dd02电影播放量都是2000，并列第一，dd03排序跳跃，名次为第三名。
F列对应dense_rank()函数，电影院01的dd01和dd02电影播放量都是2000，并列第一，dd03排序不跳跃，依次往后排，名次为第二名。
G列对应整表row_number()函数排序，不考虑各自影院，只考虑播放量
2-4行对应电影院01，排序字段 播放量 存在相同情况，论证函数差别
5-7行对应电影院02，排序字段 播放量 不存在相同情况，论证函数联系