窗口函数over()笔记

Hive中函数有4类：单行函数、聚合函数、炸裂函数、窗口函数。

概述

在使用前面学过的 单行函数、聚合函数、炸裂函数时，都是对 全局数据 进行处理，并不能对 部分数据 处理。而窗口函数能够做到只对 部分数据 进行处理，这也是“窗口”二字的含义。

窗口函数可以实现：
分组聚合【思考：与group by + select后面使用聚合函数 实现的分组聚合有何区别别？】
分组排序【思考：与group by + order by 实现的分组排序有何区别别？】

1.1 窗口函数的partition by与group by 的分组有什么区别？
表现为3点：
窗口函数保留原本数据，会将分组聚合后的结果拼接在原数据上，最终返回的行数与原始据行数相同。而 group by 只能得到分组处理后的数据，最终有几组就返回几行数据。
使用group by分组后，select后待查询字段如果是非分组字段，则必须套上聚合函数；而窗口函数没有这个限制
窗口函数能够限制对每一组的部分数据进行处理，而 group by 只能对每一组的所有数据进行处理。
这三个区别往往能够帮助我们判断在写代码时使用group by还是窗口函数。

1.2 窗口函数的order by和结尾 + order by 的排序有区别吗？
order by：是全局排序（窗口函数也会改变原数据的顺序）
窗口函数的order by：是组内排序

1.3 哪些函数可以开窗变为窗口函数？
聚合函数：sum、count、max、min、avg
排名函数：rank、dense_rank、row_number、ntile
分析函数：lead、lag、first_value、last_value

语法

语法
语法非常简单，就是在上述函数的后面加上over()函数：

func(arg1,…, argn) over ([partition by 分组字段] [order by 排序字段] [rows between 窗口表达式1 and 窗口表达式2])
-其中partition by关键字用于指定分组字段。如果没有PARTITION BY 那么整张表的所有行就是一组
- 其中order by关键字用于指定排序字段以及每组的排序规则
- 其中rows between关键字指定对每组中的进行数据处理的行范围.默认是选中每组中的所有行
窗口表达式有：- x preceding：往前x行；- x following：往后x行；- current row：当前行；- unbounded preceding: 首行；- unbounded following：尾行

关于 order by 后面接聚合函数：
对于窗口函数的 order by + 聚合函数：
一般情况下，over()函数内的partition by和order by后面都是字段名
然而order by后面可以是聚合函数。但是一旦后面是聚合函数，返回的行就与原来的行不匹配，所以必须用group by代替partition by。
反过来说，也就是 group by 之后的 聚合函数 可以写在窗口函数中

select *,
row_number() over(order by count(*) desc) as rn 
from Orders 
group by customer_number; 

此处聚合函数的本质：是一个临时字段，避免了取别名。通过这个例子可以理解【586. 订单最多的客户】
对于select中的order by + 聚合函数：其本质也是临时字段，避免了取别名。类似于这样

例子：586. 订单最多的客户

注意：
窗口函数是一行一行执行的
窗口函数在哪sql语句的哪一部执行？？？ 将其当做聚合函数，因此，在 group by关键字之后执行
关于窗口范围：
并不是所有函数都需要写窗口范围，只有 窗口聚合函数 可以，窗口排名函数 和 窗口分析函数 不支持写窗口范围：rank、dense_rank、row_number、ntile、lead、lag、first_value、last_value
对于 窗口聚合函数：
当有order by 但是缺少窗口范围时(即rows between 窗口表达式1 and 窗口表达式2)，范围是上无边界到当前行，适用累计、累加
当order by和窗口范围都缺少时，范围是上无边界