- [lag 和 lead](#font_colorFF0000lag__lead_267)
- [first\_value 和 last\_value](#font_colorFF0000first_value__last_value_285)
一、简介
本文主要介绍Hive中的窗口函数,Hive中的窗口函数和SQL中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。
二、概念
我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()
等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的,但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。
在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。
三、数据准备
我们准备一张order表,字段分别为name,orderdate,cost,数据内容如下:
jack 2015-04-03 23
jack 2015-01-01 10
tony 2015-01-02 15
jack 2015-02-03 23
tony 2015-01-04 29
jack 2015-01-05 46
jack 2015-04-06 42
tony 2015-01-07 50
jack 2015-01-08 55
mart 2015-04-08 62
mart 2015-04-09 68
neil 2015-05-10 12
mart 2015-04-11 75
neil 2015-06-12 80
mart 2015-04-13 94
在hive中建立一张表order,将数据插入进去。
四、聚合函数+over()
假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现。
select name,count(\*) over() from order where substring(orderdate,1,7)='2015-04';
得到结果如下:
mart 6
mart 6
mart 6
mart 6
jack 6
jack 6
可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了2次。事实上,大多数情况下,我们是只看去重后的结果的。针对于这种情况,我们想对代码改进,进行去重,该怎么办呢 😉
第一种方式: group by
select j.name,j.e from
(select name,count(\*) over() as e from order where substring(orderdate,1,7)='2015-04') j
group by j.name,j.e;
结果如下:
jack 6
mart 6
第二种方式:distinct
select distinct name,count(\*) over() from order where substring(orderdate,1,7)='2015-04';
结果如下:
jack 2
mart 2
五、partition by 子句
Over子句之后第一个提到的就是Partition By。Partition By子句也可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算。
实例
我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql。
select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from order
结果如下:
tony 2015-01-07 50 205
jack 2015-01-01 10 205
jack 2015-01-05 46 205
tony 2015-01-04 29 205
tony 2015-01-02 15 205
jack 2015-01-08 55 205
jack 2015-02-03 23 23
mart 2015-04-13 94 364
mart 2015-04-11 75 364
mart 2015-04-09 68 364
mart 2015-04-08 62 364
jack 2015-04-06 42 364
jack 2015-04-03 23 364
neil 2015-05-10 12 12
neil 2015-06-12 80 80
这里我们可以看到数据已经完全按照月份进行聚合。
六、order by 子句
上述的场景,假如我们想要将cost按照月进行累加,这时我们引入order by子句。
order by子句会让输入的数据强制排序(窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如Row_Number()
,Lead()
,LAG()
等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则Count()
,Min()
等计算出来的结果就没有任何意义。
我们在上面的代码中加入order by
select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate)
from order;
结果如下:
jack 2015-01-01 10 10 // 10
tony 2015-01-02 15 25 // 10+15
tony 2015-01-04 29 54 // 25+29
jack 2015-01-05 46 100
tony 2015-01-07 50 150
jack 2015-01-08 55 205
jack 2015-02-03 23 23
jack 2015-04-03 23 23
jack 2015-04-06 42 65
mart 2015-04-08 62 127
mart 2015-04-09 68 195
mart 2015-04-11 75 270
mart 2015-04-13 94 364
neil 2015-05-10 12 12
neil 2015-06-12 80 80
七、window 子句
我们在上面已经通过使用partition by子句将数据进行了分组的处理,如果我们想要更细粒度的划分,我们就要引入window子句了。
我们首先要理解两个概念:
- 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合。
- 使用了order by子句,未使用window子句的情况下,默认从起点到当前行。
当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的,每个窗口函数应用自己的规则。
window子句:
- PRECEDING:往前
- FOLLOWING:往后
- CURRENT ROW:当前行
- UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点
我们按照name进行分区,按照购物时间进行排序,做cost的累加。
如下我们结合使用window子句进行查询
select name,orderdate,cost,
sum(cost) over() as fullagg, --所有行相加
sum(cost) over(partition by name) as fullaggbyname, --按name分组,组内数据相加
sum(cost) over(partition by name order by orderdate) as fabno, --按name分组,组内数据累加
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1 --和fabno一样,由最前面的起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as mw3, --当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as mw4 --当前行及后面所有行
from order;
结果如下:
jack 2015-01-01 10 684 199 10 10 10 56 199
jack 2015-01-05 46 684 199 56 56 56 111 189
jack 2015-01-08 55 684 199 111 111 101 124 143
jack 2015-02-03 23 684 199 134 134 78 101 88
jack 2015-04-03 23 684 199 157 157 46 88 65
jack 2015-04-06 42 684 199 199 199 65 65 42
mart 2015-04-08 62 684 299 62 62 62 130 299
mart 2015-04-09 68 684 299 130 130 130 205 237
mart 2015-04-11 75 684 299 205 205 143 237 169
mart 2015-04-13 94 684 299 299 299 169 169 94
neil 2015-05-10 12 684 92 12 12 12 92 92
neil 2015-06-12 80 684 92 92 92 92 92 80
tony 2015-01-02 15 684 94 15 15 15 44 94
tony 2015-01-04 29 684 94 44 44 44 94 79
tony 2015-01-07 50 684 94 94 94 79 79 50
八、窗口函数中的序列函数
主要序列函数是不支持window子句的。
hive中常用的序列函数有下面几个😀:
ntile
- NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值
- NTILE不支持ROWS BETWEEN,
比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING - AND CURRENT ROW) - 如果切片不均匀,默认增加第一个切片的分布
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
3)]
[外链图片转存中…(img-8TGjkucI-1714657620684)]
[外链图片转存中…(img-pR0Gv25v-1714657620684)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新