【Hive】（九）Hive 窗口函数总结_9 ^hivekdw4vp^，今年大数据开发面试必问的这些技术面

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新大数据全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip204888 （备注大数据）

正文

文章目录

• • 一、简介
    • 二、概念
    • 三、数据准备
    • 四、聚合函数+over()
    • 五、partition by 子句
    • 六、order by 子句
    • 七、window 子句
    • 八、窗口函数中的序列函数
    • • ntile
        • row_number
        • rank
        • dense_rank
        • lag 和 lead
        • first_value 和 last_value

一、简介

本文主要介绍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



**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）**
![img](https://img-blog.csdnimg.cn/img_convert/568bd3033fc415721911b498ae4bd8b2.png)

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

	44	44	94	79
tony	2015-01-07	50	684	94	94	94	79	79	50



**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**需要这份系统化的资料的朋友，可以添加V获取：vip204888 （备注大数据）**
[外链图片转存中...(img-0ZzjkyeG-1713342527917)]

**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**