【hive】分组求排名

分组求排名

• 相信好多使用Mysql的用户一定对分组求排名的需求感到发怵.
• 但是在hive或者oracle来说就能简单实现.
• 采用窗口函数:rank() over() / row_number() over() / dense_rank() over()函数就能轻松完成.

窗口函数

• 我们可以理解为我们先把需要分析的字段select出来,然后通过窗口函数进行分析.
• 所以窗口函数产生的结果一定是一对一的,即便在窗口中使用分组操作产生相同的值也每行数据都有.

示例

select 
    user_id,user_type,sales, --窗口函数的分析字段
    RANK() over (partition by user_type order by sales desc) as rank,
    ROW_NUMBER() over (partition by user_type order by sales desc) as row_number,
    DENSE_RANK() over (partition by user_type order by sales desc) as dense_rank
from
    order_detail; 

• 根据窗口函数的理解,我们来看上述例子
  • 要分析的字段有 user_type,sales,执行过程对这些字段select出来(可能select不太准确,但是要先取得这些要用于分析的字段)
  • 使用窗口函数进行分析 over() 是窗口函数,over()里面一般要写partition by 和 order by来对分析的数据进行分组和排序
  • over() 函数前边要定义分析策略,比如 rank(), row_number(),dense_rank()都是不同的分析策略.

结果

user_id	user_type	sales	rank	row_number	dense_rank
wutong	new	6	1	1	1
qishili	new	5	2	2	2
lilisi	new	5	2	3	2
wanger	new	3	4	4	3
zhangsa	new	2	5	5	4
qibaqiu	new	1	6	6	5
liiu	new	1	6	7	5
liwei	old	3	1	1	1
wangshi	old	2	2	2	2
lisi	old	1	3	3	3

• 如上述表格,rank() / row_number() / dense_rank() 都是进行排名.
  • rank()在处理相同排名的时候根据同排名数据数量进行顺延排名
  • dense_rank()在处理相同排名的时候不会顺延排名
  • row_number()如函数名,就是单单是排名后的行数

HIVE2.10以后over()函数支持对聚合函数的支持

SELECT rank() OVER (ORDER BY sum(b))
FROM table
GROUP BY a;

上面等于于

SELECT sum_b rank() OVER (ORDER BY sum_b)
FROM(
    SELECT SUM(b) AS sum_b
    FROM table
    GROUP BY a
) t;