Hive窗口函数案例详解

语法：

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

常用分析函数：

• 聚合类
  avg()、sum()、max()、min()

• 排名类
  row_number() 按照值排序时产生一个自增编号，不会重复
  rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位
  dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位

• 其他类
  lag(列名,往前的行数,[行数为null时的默认值，不指定为null])
  lead(列名,往后的行数,[行数为null时的默认值，不指定为null])
  ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号

注意点：

• over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的业务需求结合使用
• over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据

over()函数中的窗口范围说明：

current row：当前行

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

n preceding ：往前n行数据

n following：往后n行数据

实战案例1：

原始数据(用户购买明细数据)

name，orderdate，cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94


建表加载数据
vi business.txt

create table business
(
name string, 
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/data/business.txt" into table business;

需求

(1)查询在2017年4月份购买过的顾客及总人数

分析：按照日期过滤、分组count求总人数(分组为什么不是用group by？自己思考)

select 
name,
orderdate,
cost,
count(*) over() total_people
from 
business
where date_format(orderdate,'yyyy-MM')='2017-04';

(2)查询顾客的购买明细及月购买总额

分析：按照顾客分组、sum购买金额

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

(3)上述的场景,要将cost按照日期进行累加

分析：按照顾客分组、日期升序排序、组内每条数据将之前的金额累加

select 
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) cumulative_amount
from 
business;

(4)查询顾客上次的购买时间

分析：查询出明细数据同时获取上一条数据的购买时间(肯定需要按照顾客分组、时间升序排序)

select 
name,
orderdate,
cost,
lag(orderdate,1) over(partition by name order by orderdate) last_date
from 
business;

(5)查询前20%时间的订单信息

分析：按照日期升序排序、取前20%的数据

select
*
from
(
select 
name,
orderdate,
cost,
ntile(5) over(order by orderdate) sortgroup_num
from 
business
) t
where t.sortgroup_num=1;

实战案例2：

原始数据(学生成绩信息)

name	subject	score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78


建表加载数据
vi score.txt

create table score
(
name string,
subject string, 
score int
) row format delimited fields terminated by "\t";

load data local inpath '/opt/module/data/score.txt' into table score;

需求：

(1)每门学科学生成绩排名(是否并列排名、空位排名三种实现)

分析：学科分组、成绩降序排序、按照成绩排名

select 
name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from 
score;

(2)每门学科成绩排名top n的学生

select 
*
from 
(
select 
name,
subject,
score,
row_number() over(partition by subject order by score desc) rmp
from score
) t
where t.rmp<=3;