Hive sql - 常用窗口函数（万字超详解）

Answer 1

Hello~我是高月！我将会在这篇文章中为你详细介绍 Hive sql 中常用的窗口函数。

窗口函数是指在特定的数据滑动窗口内，执行各种统计分析的操作。

在日常使用中，窗口函数通常与分析函数或专用窗口函数结合使用，形成如窗口聚合函数、窗口排序函数等实用函数。

具体使用语法如下：

1.1 什么是分析函数和专用窗口函数？
常用的分析函数包括：sum()、max()、min()、avg()、count()等。
专用窗口函数有：row_number()、rank()、dense_rank()等。

1.2 什么是窗口函数？
窗口函数由三个部分组成，可以单独使用，也可以混合使用，也可以全都不用。以下是语法拆解：

对指定的字段进行分组，后续操作都是以组为单位，将每个分组作为一个独立的窗口进行统计分析。

案例 01：对窗口中的数据求和，并将求和结果分配到对应窗口的每一条数据中。

案例 02：对整体数据求和，并将求和结果分配到每一条数据中。

当使用order by与partition by结合时，可以对各个分组内的数据按照指定的字段进行排序。如果没有partition by指定分组字段，则会对全局数据进行排序。

案例 01：对数据进行全局排序。

案例 02：当排序维度不存在重复时，即order by指定的字段，使用order by + 分析函数sum()，可以产生求整体累计数的效果。

但是当order by指定的字段存在重复时，在不重复的数据中会产生累计效果，而在重复的数据中，会把整体的累计结果分配到每条重复的数据中。

案例 03：当排序维度不存在重复时，即order by指定的字段，partition by与order by结合使用，并使用分析函数sum()，可以产生求窗口中累计数的效果。

但是当order by指定的字段组合存在重复时，在不重复的数据中会产生累计效果，而在重复的数据中，也会把整体的累计结果分配到每条重复的数据中。

rows between用于划分窗口中函数作用的数据范围。以下例子有助于加深rows between的理解。

rows between常用的参数如下：
① n preceding：往前
② n following：往后
③ current row：当前行
④ unbounded：起点（一般结合preceding，following使用）

使用例子如下：

rows between unbounded preceding and current row与partition by、order by结合使用，可以产生对窗口中的数据求累计数的效果。

2. 排序窗口函数
2.1 排序并产生自增编号，自增编号不重复且连续
我们可以使用函数：row_number() over()
数据样例：
具体语法如下：
案例如下：
2.2 排序并产生自增编号，自增编号会重复且不连续
我们可以使用函数：rank() over()
数据样例：
具体语法如下：
案例如下：
2.3 排序并产生自增编号，自增编号会重复且连续
我们可以使用函数：dense_rank() over()
数据样例：
具体语法如下：
案例如下：
3. 聚合窗口函数
3.1 求窗口中的累计值
我们可以使用：sum() over()
3.2 求窗口中3天的平均价格
我们可以使用 avg() over()
3.3 求分组中的最大值/最小值
3.4 求分组中的总记录数
4. 位移窗口函数
4.1 获取分组中往前n行的值
基础语法：
语法解析：
1. field 是指定的列名
2. n 是往前的行数
3. 行往前导致的最后的n行值为null，可以用default_value代替。
使用案例：
4.2 获取分组中往后n行的值
基础语法：
语法解析：
1. field 是指定的列名
2. n 是往前的行数
3. 行往后导致的，前面的n行值为null，可以用default_value代替。
使用案例：
5. 极值窗口函数
5.1 获取分组内第一行的值
我们可以使用first_value(col,true/false) over()，作用是：取分组内排序后，截止到当前行，第一个值。
注意：
案例：将每行数据换成当前窗口指定字段的第一个值
5.2 获取分组内最后一行的值
我们可以使用last_value(col,true/false) over()，作用是：取分组内排序后，截止到当前行，最后一个值。所以，如果使用order by排序时，想要取最后一个值，需要与rows between unbounded preceding and unbounded following连用。
注意：
相信大家都发现了，在本案例中，我们使用order by时与rows between unbounded preceding and unbounded following连用了，这是需要注意的一个点，如果不连用，将会产生以下效果：
6. 分箱窗口函数
ntile() over() 分箱窗口函数，用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加到第一个切片中。
案例：查询成绩前20%的人。
文章到这里就结束啦，如果喜欢这篇文章，请点赞、关注、收藏哦！
如果你有想知道的知识，欢迎评论区留言，我会持续输出相关文章内容的~