Hive中row_number()、dense_rank()、rank()的区别

摘要

本文对Hive中常用的三个排序函数row_number()dense_rank()rank()的特性进行类比和总结,并通过笔者亲自动手写的一个小实验,直观展现这三个函数的特点。

三个排序函数的共同点与区别

函数 共同点 不同点
row_number() 用于特定场景下实现排序需求;
均从1开始排序
无重复排名(相同排名的按序排名)
dense_rank() 有相同排名,但不会跳过占用的排名
rank() 有相同排名,但会跳过占用的排名

实验示例

set mapreduce.job.queuename=QueueA;

use STUDENT_DB;

--创建学生分数表
DROP TABLE IF EXISTS STUDENT_DB.SCORE_TABLE1;
CREATE TABLE IF NOT EXISTS STUDENT_DB.SCORE_TABLE1
(
    ID          STRING COMMENT '唯一ID',
    NAME        STRING COMMENT '姓名',
    SCORE       INT    COMMENT '分数',
    CLASS_NUM   STRING COMMENT '班级编号'
)
COMMENT '学生分数表'
PARTITIONED BY (pt_dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\27'
STORED AS ORCFILE;

--向学生分数表插入数据
INSERT OVERWRITE TABLE STUDENT_DB.SCORE_TABLE1 PARTITION(pt_dt='2019-12-12') VALUES
('1', '小明', 89, '1班'),
('2', '小红', 90, '1班'),
('3', '小军', 90, '1班'),
('4', '小胖', 91, '1班'),
('5', '小李', 87, '1班'),
('6', '小郭', 99, '1班');

--创建学生分数排序结果表
DROP TABLE IF EXISTS STUDENT_DB.SCORE_RANK_TABLE1;
CREATE TABLE IF NOT EXISTS STUDENT_DB.SCORE_RANK_TABLE1
(
    ID          STRING COMMENT '唯一ID',
    NAME        STRING COMMENT '姓名',
    SCORE       INT    COMMENT '分数',
    CLASS_NUM   STRING COMMENT '班级编号',
    ROW_NUMBERS STRING COMMENT 'ROW_NUMBER排序结果',
    DENSE_RANKS STRING COMMENT 'DENSE_RANKS排序结果',
    RANKS       STRING COMMENT 'RANKS排序结果'
)
COMMENT '学生分数排序结果表'
PARTITIONED BY (pt_dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\27'
STORED AS ORCFILE;

INSERT OVERWRITE TABLE STUDENT_DB.SCORE_RANK_TABLE1 PARTITION(pt_dt='2019-12-12')
SELECT ID,
       NAME,
       SCORE,
       CLASS_NUM,
       ROW_NUMBER() OVER(PARTITION BY CLASS_NUM ORDER BY SCORE DESC) AS ROW_NUMBERS,
       DENSE_RANK() OVER(PARTITION BY CLASS_NUM ORDER BY SCORE DESC) AS DENSE_RANKS,
       RANK() OVER(PARTITION BY CLASS_NUM ORDER BY SCORE DESC) AS RANKS
FROM STUDENT_DB.SCORE_RANK_TABLE1
WHERE pt_dt='2019-12-12';

SELECT ID,
       NAME,
       SCORE,
       CLASS_NUM,
       ROW_NUMBERS,
       DENSE_RANKS,
       RANKS,
       pt_dt
FROM STUDENT_DB.SCORE_RANK_TABLE1
WHERE pt_dt='2019-12-12';

实验结果

SCORE_RANK_TABLE1

ID NAME SCORE CLASS_NUM ROW_NUMBERS DENSE_RANKS RANKS pt_dtpt_dt
6 小郭 99 1班 1 1 1 2019-12-12
4 小胖 91 1班 2 2 2 2019-12-12
3 小军 90 1班 3 3 3 2019-12-12
2 小红 90 1班 4 3 3 2019-12-12
1 小明 89 1班 5 4 5 2019-12-12
5 小李 87 1班 6 5 6 2019-12-12

如上表所示,1班的小军和小红分数均为90,当我们使用ROW_NUMBERS()进行排序时,他们的排名不会并列,而是分别有一个排名。

当我们使用DENSE_RANK()进行排序时,他们的排名会并列,且后续记录的排名会以当前并列排名为基础+1,即不会跳过被占用的位置。

当我们使用RANK()进行排名时,他们的排名会并列,且后续记录的排名会跳过被占用的排名数,而不会顺延下去。

总结

在实际开发过程中,可根据场景的需要去选择具体的排序函数。一个较为常见的场景是根据某个字段partition by之后在该范围内order by进行排序,然后取首条记录,这时候row_number()基本可以满足需求。

除此之外,排序函数均较耗性能,特别是如果对大数据量进行全局排序时,一定要考虑性能问题,非必要情况下,避免对大数据量进行全局排序。

posted @ 2020-06-10 19:10  JasonCeng  阅读(205)  评论(0编辑  收藏