原创 | 详解Hive窗口函数及10道实战练习

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

FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）

比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

2、求用户明细并统计每天的用户总数
3、计算从第一天到现在的所有 score 大于80分的用户总数
4、计算每个用户到当前日期分数大于80的天数

20191020,11111,85
20191020,22222,83
20191020,33333,86
20191021,11111,87
20191021,22222,65
20191021,33333,98
20191022,11111,67
20191022,22222,34
20191022,33333,88
20191023,11111,99
20191023,22222,33

create table test_window
(logday string,    #logday时间
userid string,
score int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
#加载数据
load data local inpath '/home/xiaowangzi/hive_test_data/test_window.txt' into table test_window;

select  *  from test_window;

select *, count(userid)over() as total  from  test_window;

select  *,count()over(partition by logday)as day_total from  test_window;

select  *,count()over(order by logday rows between unbounded preceding and current row)as total
from  test_window
where score > 80;

select *,
count()over(partition  by userid order by logday rows between unbounded preceding and current row) as total
from test_window
where score > 80  order by logday, userid;

1、查询在2017年4月份购买过的顾客及总人数
2、查询顾客的购买明细及月购买总额
3、查询顾客的购买明细及到目前为止每个顾客购买总金额
4、查询顾客上次的购买时间----lag()over()偏移量分析函数的运用
5、查询前20%时间的订单信息

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

create table business
(
name string,
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
#加载数据
load  data local inpath "/home/fengGG/hive_test_data/business.txt" into table business;

select   *  from  business;

select   *,count()over() as total  from  business
where substr(orderdate,1,7) = '2017-04';

select
*,
sum(cost) over(partition by name,substr(orderdate,1,7)) total_amount
from
business;

select
*,
sum(cost) over(partition by name order  by  orderdate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_amount
from
business;

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

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

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

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

create table score
(
name string,
subject string,
score int
) row format delimited fields terminated by "\t";
#加载数据
load data local inpath '/home/fengGG/hive_test_data/score.txt' into table score;

select  *  from  score;

select  *,
row_number()over(partition by subject order by score desc),
rank()over(partition by subject order by score desc),
dense_rank()over(partition by subject order by score desc)
from score

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