Hive - ROW_NUMBER() OVER 排序&去重

一.引言

需求：

每日更新的全量数据表，根据用户去重获取7日内的最新的用户行为

二.ROW_NUMBER() OVER 实现

全量数据表 user_action ，根据 dt 分区，共包含三列

(user STRING,

action STRING,

dt STRING)

假设有过去一年共 365 个 dt 分区，取过去7天内用户最新的行为并对用户去重：

SELECT tmp.user, tmp.action FROM
(
SELECT user, action, ROW_NUMBER() over (PARTITION BY user ORDER BY dt DESC) rn
FROM user_action
WHERE dt >= '${startDay}' AND dt <= '${endDay}'
) tmp
WHERE tmp.rn = 1

ROW_NUMBER() 为每行数据添加行号，这里 Partition By 后的字段代表根据哪个字段分区，Order By 后的字段标识用哪个字段排序，本例中将7天的用户数据按每个用户 user 分区得到若干个小分区，然后小分区内用户的行为按最新的 dt 排序，所以采用降序 DESC + row_number = 1 获取最新用户行为，默认是 ASC 升序。

三.扩展

ROW_NUMBER() OVER 会给每行数据增加序号，除此之外还有 RANK() 和 DENSE_RANK() 函数，区分 RANK() 和 DENSE_RANK() 的方法就是 RANK() 最终的 rank 数目和对应 Partition 内条数是一致的(所有人分数都一样排除)，而 DENSE_RANK() 最终的 rank 数目则可能小于对应 Partition 内的数据条数。使用的话只需把 ROW_NUMBER() over 换成 RANK() over 或者 DENSE_RANK() over。

1.RANK()

RANK 支持二者值相同 - 序号也相同，排序相同的 rank 会累加，举个栗子:

User	Score	Rank
ZhangSan	99	1
LiSI	99	1
WangWu	98	3

2.DENSE_RANK()

DENSE_RANK 支持二者值相同 - 序号也相同，排序相同的 rank 不会累加，举个栗子：

User	Score	Rank
ZhangSan	99	1
LiSI	99	1
WangWu	98	2