HiveQL中ROW_NUMBER(),RANK()和DENSE_RANK()的区别和适用场景

一、ROW_NUMBER()

（一）代码样例

SELECT
    uid
   ,uname
   ,update_time
   ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY update_time DESC)    AS rn
FROM xxx
WHERE pt_d = '20210101';

输出
+-----+----------+-------------+------+
| uid |  uname   | update_time |  rn  |
+-----+----------+-------------+------+
| 001 |  coco    | 2021-03-01  |  1   |
+-----+----------+-------------+------+
| 001 |  coco    | 2021-01-01  |  2   |
+-----+----------+-------------+------+
| 002 |  bobo    | 2021-03-01  |  1   |
+-----+----------+-------------+------+
| 002 |  bobo    | 2021-03-01  |  2   |
+-----+----------+-------------+------+
| 002 |  bobo    | 2021-01-01  |  3   |
+-----+----------+-------------+------+

（二）取值逻辑及适用场景

• ROW_NUMBER()是对目标字段直接进行排序编号，即使字段值相同，也会依次连续排序编号，同一分组内不会出现相同的序号。
• 适用于根据时间字段取最新时间的数据。如上图样例，取rn=1，则取到每个uid的最新一条数据。

二、RANK()

（一）代码样例

SELECT
    uid
   ,score
   ,RANK() OVER(ORDER BY score DESC)    AS score_rank
FROM xxx
WHERE pt_d = '20210101'

输出
+-----+-------+------------+
| uid | score | score_rank |
+-----+-------+------------+
| 001 | 10.0  |     1      |
+-----+-------+------------+
| 007 | 10.0  |     1      |
+-----+-------+------------+
| 002 |  9.0  |     3      |
+-----+-------+------------+
| 003 |  9.0  |     3      |
+-----+-------+------------+
| 009 |  8.0  |     5      |
+-----+-------+------------+

（二）取值逻辑及适用场景

• RANK()在进行排序编号时，如果目标字段值相同，则会赋同一个编号，且紧随的下一条数据不依次连续编号，而是根据实际第几条数据继续排序编号。
• 适用于个人分数排名这类场景。

三、DENSE_RANK()

（一）代码样例

SELECT
    score
   ,DENSE_RANK() OVER(ORDER BY score)    AS score_rank
FROM xxx
WHERE pt_d = '20210101'

输出
+-------+------------+
| score | score_rank |
+-------+------------+
|  0.0  |     1      |
+-------+------------+
|  0.0  |     1      |
+-------+------------+
|  1.0  |     2      |
+-------+------------+
|  1.0  |     2      |
+-------+------------+
|  2.0  |     3      |
+-------+------------+

（二）取值逻辑及适用场景

• DENSE_RANK()在进行排序编号时，如果目标字段值相同，则会赋同一个编号，且紧随的下一条数据依次连续编号。
• 适用于查看某个字段值属于第几组的情况。