窗口函数

1、over()窗口函数的语法结构

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

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

over()函数中包括三个函数:包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置

over()函数中的这三个函数可组合使用也可以不使用。

over()函数中如果不使用这三个函数,窗口大小是针对查询产生的所有数据,如果指定了分区,窗口大小是针对每个分区的数据。

2. rows between 开始位置 and 结束位置

over (rows between 开始位置 and 结束位置)搭配分析函数时,分析函数按照这个范围进行计算的。常用该窗口来计算累加。

preceding:往前 

following:往后  

unbounded:起点(一般结合preceding,following使用)

unbounded preceding表示该窗口最前面的行(起点)

unbounded following:表示该窗口最后面的行(终点)

courrent row:当前行

3、常与over()一起使用的分析函数

3.1 聚合类

avg()、sum()、max()、min()

3.2 其他类

lag(列名,往前的行数),往前第n行数据
lead(列名,往后的行数),往后第n行数据
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号

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



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


load  data local inpath "/home/kg/qiaoruihua/hive/business.txt" into table business;
1.查询在2017年4月份购买过的顾客及总人数

1.1 查询在2017年4月份购买过的总人数
select count(distinct(name)) from business where substr(orderdate,1,7)="2017-04";
2

1.2 查询在2017年4月份购买过的顾客及总人数
select name from business where substr(orderdate,1,7)="2017-04";
jack
mart
mart
mart
mart

1.3 查询在2017年4月份购买过的顾客及总人数
select name,count()over() from business where substr(orderdate,1,7)="2017-04";
mart    5
mart    5
mart    5
mart    5
jack    5
2.查询每个顾客的购买明细及月购买总额
2.1 查询每个顾客的月购买总额
select name,substr(orderdate,1,7),sum(cost) from business group by name,substr(orderdate,1,7);
jack    2017-01    111
jack    2017-02    23
jack    2017-04    42
mart    2017-04    299
neil    2017-05    12
neil    2017-06    80
tony    2017-01    94
2.2 查询每个顾客的购买明细
select name,orderdate,cost from business;
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

2.3 查询每个顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(distribute by name,substr(orderdate,1,7)) from business;
jack    2017-01-05    46    111
jack    2017-01-08    55    111
jack    2017-01-01    10    111
jack    2017-02-03    23    23
jack    2017-04-06    42    42
mart    2017-04-13    94    299
mart    2017-04-11    75    299
mart    2017-04-09    68    299
mart    2017-04-08    62    299
neil    2017-05-10    12    12
neil    2017-06-12    80    80
tony    2017-01-04    29    94
tony    2017-01-02    15    94
tony    2017-01-07    50    94
3.查询顾客的购买明细及到目前为止每个顾客购买总金额
select name,orderdate,cost,sum(cost) over(distribute by name sort by orderdate rows between unbounded preceding and current row) total_amount from business;
jack    2017-01-01    10    10
jack    2017-01-05    46    56
jack    2017-01-08    55    111
jack    2017-02-03    23    134
jack    2017-04-06    42    176
mart    2017-04-08    62    62
mart    2017-04-09    68    130
mart    2017-04-11    75    205
mart    2017-04-13    94    299
neil    2017-05-10    12    12
neil    2017-06-12    80    92
tony    2017-01-02    15    15
tony    2017-01-04    29    44
tony    2017-01-07    50    94
4、查询顾客上次的购买时间----lag()over()偏移量分析函数的运用
select name,orderdate,cost,lag(orderdate,1,"1970-01-01") over(distribute by name sort by orderdate) from business;
jack    2017-01-01    10    1970-01-01
jack    2017-01-05    46    2017-01-01
jack    2017-01-08    55    2017-01-05
jack    2017-02-03    23    2017-01-08
jack    2017-04-06    42    2017-02-03
mart    2017-04-08    62    1970-01-01
mart    2017-04-09    68    2017-04-08
mart    2017-04-11    75    2017-04-09
mart    2017-04-13    94    2017-04-11
neil    2017-05-10    12    1970-01-01
neil    2017-06-12    80    2017-05-10
tony    2017-01-02    15    1970-01-01
tony    2017-01-04    29    2017-01-02
tony    2017-01-07    50    2017-01-04
5、查询前20%时间的订单信息

select name,orderdate,cost,ntile(5)over(order by orderdate)n_tile_date from business;t1
jack    2017-01-01    10    1
tony    2017-01-02    15    1
tony    2017-01-04    29    1
jack    2017-01-05    46    2
tony    2017-01-07    50    2
jack    2017-01-08    55    2
jack    2017-02-03    23    3
jack    2017-04-06    42    3
mart    2017-04-08    62    3
mart    2017-04-09    68    4
mart    2017-04-11    75    4
mart    2017-04-13    94    4
neil    2017-05-10    12    5
neil    2017-06-12    80    5

select name,orderdate,cost,n_tile_date from
(select name,orderdate,cost,ntile(5)over(order by orderdate)n_tile_date from business) t1 
where n_tile_date=1;
jack    2017-01-01    10    1
tony    2017-01-02    15    1
tony    2017-01-04    29    1

 

3.3 排名类

rank() (如:1、1、2、4)
dense_rank() (如:1、1、2、3)

row_number() (如:1、2、3、4)

drop table score;
create table if not exists score
(
name string,
subject string,
score int
) row format delimited fields terminated by ",";

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


load data local inpath '/home/kg/qiaoruihua/hive/score.txt' into table score;
1、每门学科学生成绩排名
select name,subject,score,
rank() over(distribute by subject sort by score desc),
dense_rank() over(distribute by subject sort by score desc),
row_number() over(distribute by subject sort by score desc)
from score;t1

孙悟空    数学    95    1    1    1
宋宋    数学    86    2    2    2
婷婷    数学    85    3    3    3
大海    数学    56    4    4    4
宋宋    英语    84    1    1    1
大海    英语    84    1    1    2
婷婷    英语    78    3    2    3
孙悟空    英语    68    4    3    4
大海    语文    94    1    1    1
孙悟空    语文    87    2    2    2
婷婷    语文    65    3    3    3
宋宋    语文    64    4    4    4
2、每门学科成绩排名top n的学生
select name,subject,score,rank_score from
(select name,subject,score,rank() over(distribute by subject sort by score desc) rank_score from score) t1 where rank_score<=3;
孙悟空    数学    95    1
宋宋    数学    86    2
婷婷    数学    85    3
大海    英语    84    1
宋宋    英语    84    1
婷婷    英语    78    3
大海    语文    94    1
孙悟空    语文    87    2
婷婷    语文    65    3

 

posted on 2020-12-07 15:58  happygril3  阅读(217)  评论(0)    收藏  举报

导航