<svg xmlns="http://www.w3.org/2000/svg" style="display: none;"> <path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path> </svg> 讯享网
窗口函数的基本概念
1. 窗口函数的作用
窗口函数本质是在线分析处理（OLAP）函数，相比常用的SQL语句可以实现更加复杂的业务功能，例如产品的累计销售额统计、分类排名、同比/环比分析等，这些功能通常很难通过聚合函数和分组操作来实现，但是应用窗口函数却可以轻松解决。窗口函数的最大特点在于不会改变原始查询结果的行数，这使得窗口函数不会像聚合函数那样使得一部分数据“不可见”，这个特性也使得窗口函数可以配合SQL的嵌套查询实现更加灵活的功能。
2. 窗口函数的语法
讯享网
- 为可实现窗口的应用函数，根据效用可以分为以下三类。
- 排序窗口函数：包括、、、、、。
- 聚合窗口函数：包括、、、、。
- 取值窗口函数：包括、、、、。
- 表示前方的函数为一个窗口函数，若括号内为空则表示将所有数据作为一个分组进行汇总。
- 为分组关键词，会根据后方的参数指定本次窗口函数执行依据的分组字段。
- 为排序关键词，会根据后方的参数指定本次窗口函数执行结果呈现的排序方式，默认升序。
- 用于指定一个移动的分析窗口，窗口总是位于分区的范围之内，是分区的一个子集。在指定了分析窗口之后，窗口函数不再基于分区进行分析，而是基于窗口内的数据进行分析。
3. 移动窗口
移动窗口需要在窗口函数语法的末尾指定，共分为两种模式，其中表示以数据行为单位计算窗口的偏移量，表示以数值（例如10天、5km等）为单位计算窗口的偏移量。
- 代表移动窗口的起始位置，可选值为：
- 表示窗口从分区的第一行开始。
- 表示窗口从当前行之前的第N行开始。
- 表示窗口从当前行开始。
- 代表移动窗口的结束位置，可选值为：
- 表示窗口到当前行结束。
- 表示窗口到当前行之后的第M行结束。
- 表示窗口到分区的最后一行结束。
- 若不指定则默认
4. 应用窗口函数的思维范式
- 根据应用场景判断使用何种窗口函数。
- 根据应用场景判断是否需要指定移动窗口。
- 确定分组字段，若无则跳过。
- 确定排序字段及排序方式。
5. 窗口函数与聚合函数的区别
- 语法不同
聚合函数通常需要搭配关键词和使用；而窗口函数的关键词为、等。 - 作用范围不同
聚合函数的作用范围是整个整个数据表或者数据集；而窗口函数的作用范围是一组记录（即窗口）。 - 返回结果不同
聚合函数的计算结果只有一个，代表着原始数据集应用聚合函数计算的下钻结果；而窗口函数会保留原始数据集的行数。 - 性能消耗不同
窗口函数通常比聚合函数更消耗资源，因为它需要在每个行上进行计算，而聚合函数只需要计算一次。
排序窗口函数的应用
1. 排序窗口函数详解
- 可以为分区中的每行数据分配一个序列号，序列号从1开始，且不会重复。形如1,2,3,4,…。
- 返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃。形如1,2,2,4,…。
- 返回当前行在分区中的名次。即使存在名次相同的数据，后续的排名也是连续值。形如1,2,2,3,…。
- 以百分比的形式返回当前行在分区中的名次，计算方式为：（分组内当前行RANK值-1）/（分组内总行数-1），值域为[0,1]。如果存在名次相同的数据，后续的排名将会产生跳跃。
- 计算并返回当前行在分区内的累积分布，计算方式为：分组内小于等于当前值的行数/分组内总行数，值域为[0,1]。
- 调用时需要传入参数N，将分区内的数据分为N等份，并返回当前行所在的分片位置，当切片不均时，默认增加第一个切片的分布。
2. TopN问题
eg：
原题链接：184. 部门工资最高的员工 - 力扣（LeetCode）
查找出每个部门（departmentId）中薪资（salary）最高的员工部门（department）、员工姓名（name）及薪资。
讯享网
3. TopN%问题
eg：
销量表
查找销量（SALES_VOLUME）占比前10%的商品信息。
聚合窗口函数的应用
1. 移动平均值问题
函数在作为窗口函数使用时，可以用于计算随着当前行移动的窗口内数据行的平均值。
eg：
产品表
滚动求近三个月每一产品的平均GMV、每一产品从上架开始到上月的平均GMV以及每一产品从上架开始到本月的平均GMV。
讯享网
2. 累计求和问题
函数在作为窗口函数使用时，可以用于统计指定窗口内的累计值。
eg：
产品表如上例，求每一产品截至当前月份的累计GMV。
3. 历史最高/最低问题
/函数在作为窗口函数使用时，可以统计某一指标的历史最高/最低取值。
eg：
产品表如上例，求每一产品历史最高/最低GMV。
讯享网
取值窗口函数的应用
1. 取值窗口函数详解
- 返回窗口内当前行之前第N行的指定列数据。
- 返回窗口内当前行之后第N行的指定列数据。
- 返回窗口内第一行的指定列数据。
- 返回窗口内最后一行的指定列数据。
- 返回窗口内第N行的指定列数据。
注意：与不支持动态窗口大小，以整个分区作为分析窗口。
2. 环比问题
环比增长率计算公式：（本期-上一期）/上一期*100%。
eg：
产品表如上例，求每一产品GMV的环比增长率。
3. 同比问题
同比增长率计算公式：（本年同期-去年同期）/去年同期*100%
eg：
产品表如上例，求每一产品GMV的同比增长率。
讯享网
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/151145.html