摘要

本文对Hive中常用的三个排序函数row_number()、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

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

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

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

总结

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

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