窗口函数
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) 收藏 举报