阿八博客
  • 100000+

    文章

  • 23

    评论

  • 20

    友链

  • 最近新加了很多技术文章,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

Hive窗口函数案例详解

欢迎来到阿八个人博客网站。本 阿八个人博客 网站提供最新的站长新闻,各种互联网资讯。 喜欢本站的朋友可以收藏本站,或者加QQ:我们大家一起来交流技术! URL链接:https://www.abboke.com/jsh/2019/0930/116254.html

语法:

分析函数 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,costjack,2017-01-01,10tony,2017-01-02,15jack,2017-02-03,23tony,2017-01-04,29jack,2017-01-05,46jack,2017-04-06,42tony,2017-01-07,50jack,2017-01-08,55mart,2017-04-08,62mart,2017-04-09,68neil,2017-05-10,12mart,2017-04-11,75neil,2017-06-12,80mart,2017-04-13,94建表加载数据vi business.txtcreate 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_peoplefrom businesswhere date_format(orderdate,'yyyy-MM')='2017-04';

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

分析:按照顾客分组、sum购买金额select name,orderdate,cost,sum(cost) over(partition by name) total_amountfrom business;

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

分析:按照顾客分组、日期升序排序、组内每条数据将之前的金额累加select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) cumulative_amountfrom business;

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

分析:查询出明细数据同时获取上一条数据的购买时间(肯定需要按照顾客分组、时间升序排序)select name,orderdate,cost,lag(orderdate,1) over(partition by name order by orderdate) last_datefrom business;

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

分析:按照日期升序排序、取前20%的数据select*from(select name,orderdate,cost,ntile(5) over(order by orderdate) sortgroup_numfrom business) twhere t.sortgroup_num=1;

实战案例2:

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

name    subject    score孙悟空    语文    87孙悟空    数学    95孙悟空    英语    68大海    语文    94大海    数学    56大海    英语    84宋宋    语文    64宋宋    数学    86宋宋    英语    84婷婷    语文    65婷婷    数学    85婷婷    英语    78建表加载数据vi score.txtcreate 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) rmpfrom score;

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

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

相关文章

暂住......别动,不想说点什么吗?
  • 全部评论(0
    还没有评论,快来抢沙发吧!