Hive中row_number()、dense_rank()、rank()的區別

JasonCeng發表於2020-06-10
Hive

摘要

本文對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()基本可以滿足需求。

除此之外，排序函式均較耗效能，特別是如果對大資料量進行全域性排序時，一定要考慮效能問題，非必要情況下，避免對大資料量進行全域性排序。

相關文章