Hive开窗函数和Rank函数

开窗函数

over()：跟在聚合函数后面，指定分析函数工作的数据窗口大小，决定了数据的聚合范围，默认范围是整个数据窗口，即所有行。可使用partition by将数据进行分组聚合；使用order by除了排序（默认asc升序），还会改变聚合范围（从开始行到当前行的聚合），使用distribute by…sort by…和partition by…order by…效果一样。

如下的窗口子句，能对聚合数据进一步的划分：

current row：当前行

n preceding：往前n行数据

n following：往后n行数据

unbounded：起点，unbounded preceding表示从前面的起点开始，unbounded following表示到后面的终点结束

其他分析函数：

lag(col, n[, default_val])：往前第n行数据

lead(col, n[, default_val])：往后第n行数据

ntile(n)：把有序分区中的行分发到指定数据的组中，各个组的编号从1开始，一共分成n片。

例子

create table business(name string, orderdate string, cost int);
           

（1）查询在2019年1月份购买过的顾客及总人数

select name,count(*) over()
from business
where date_format(orderdate, 'yyyy-MM')='2019-01'
group by name;
           

注意“总人数”指顾客数量，如果不使用over()，结果将是每位顾客对应的购买次数，此处over()省略窗口字句ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，即针对所有行。

（2）查询顾客的购买明细及月购买总额

select name, orderdate, cost, sum(cost) over(partition by month(orderdate))
from business;
或：
select name, orderdate, cost, sum(cost) over(distribute by month(orderdate))
from business;
           

由于查询字段中含有与orderdate无关的字段，故使用不了group by。

（3）查询顾客购买明细，以及每个顾客的cost按照日期进行累加

select name, orderdate, cost, sum(cost) over(partition by name order by orderdate)
from business;
           

order by的聚合范围即从开始行到当前行的聚合，加上窗口语句“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”效果一样。

（4）查询顾客上次的购买时间

select name, orderdate, lag(orderdate, 1, '0000-00-00') over(partition by name order by orderdate) last_time
from business;
           

lag函数如果没有上n次的值，返回null，或者指定的默认值。

（5）查询前20%时间的订单信息

select name, orderdate, cost from(
select name, orderdate, cost, ntile(5) over(order by orderdate) arr
from business
)
where arr = 1;
           

需要查询前20%（即1/5）的数据可以使用ntile函数，切成5片数据，再从中取第一份数据。

Rank函数

rank()：相同时排名值会重复，总数不会变，故相同时排名值会跳跃而不连续

row_number()：相同时排名值会根据顺序计算，不会重复

dense_rank()：相同时排名值会重复，总数会减少

例子

create table score(name string, subject string, score int);
           

计算每门学科成绩排名

select name, subject,score,rank()
over(partition by subject order by score desc)
from score;