Hive分析函数row_number()

业务背景

最近有一个需求是给用户浏览的商品进行评分, 然后将商品评分按降序排序, 最后取 Top 50 推荐给终端用户。评分的计算公式为: 该商品一个月内的购买次数 / 该商品两个月内的浏览次数。在实现该需求的时候就用到了 Hive 中内置的分析函数 row_number()。

row_number()函数简介

row_number() 函数的简单用法如下:

ROW_NUMBER() OVER (DISTRIBUTE BY COLUMN_A SORT BY COLUMN_B ASC/DESC)

说明: DISTRIBUTE BY COLUMN_A 的意思是按照 COLUMN_A 进行分组, SORT BY COLUMN_B 的意思是按照 COLUMN_B 进行排序, 后面跟着 ASC/DESC 指定是按照升序还是降序排序。row_number() 按指定的列进行分组生成行序列, 从 1 开始, 如果两行记录的分组列相同, 则行序列+1。

需求实现

数据表 user_item_score 结构大致如下：

user_id	item_id	item_score
U_AAAA	I_AAA1	0.5
U_BBBB	I_BBB1	0.3
U_AAAA	I_AAA2	0.6
U_CCCC	I_CCCC	0.7
U_AAAA	I_AAA3	0.55
U_BBBB	I_BBB2	0.4

实现 SQL 如下:

select user_id, item_id, item_score from (
    select *, row_number() over ( distribute by user_id sort by item_score desc) rownum from user_item_score
) temp
where rownum <= 50;

最终结果如下:

user_id	item_id	item_score	row_num
U_AAAA	I_AAA2	0.6	1
U_AAAA	I_AAA3	0.55	2
U_AAAA	I_AAA1	0.5	3
U_BBBB	I_BBB2	0.4	1
U_BBBB	I_BBB1	0.3	2
U_CCCC	I_CCCC	0.7	1