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;