开窗函数详解(保姆级实操)
窗口函数类似于聚合函数，但又不同于聚合函数。聚合函数是将组内多个数据聚合成一个值，而窗口函数除了可以将组内数据聚合成一个值，还可以保留原始的每条数据。
本文举例场景：分组求和以及累计求和分组排序行偏移计算移动均值和移动累计聚合函数和条件函数的组合 sum over + case when
小编最近开通了群，欢迎知乎的读者公号 求知鸟，添加好友，加群一起讨论。
应用场景：
场景1：BI里自带了多种聚合方式，可以直接用聚合函数新建字段进行计算，但是都是基于维度栏的字段进行聚合，如果需要不按照维度栏字段进行分组聚合，那就需要用到窗口函数来进行计算。
场景2：BI里直接聚合计算得到的数据不能进行筛选和二次计算，如果需要对聚合结果进行筛选和二次计算，需要用到窗口函数来进行计算。
基本语法：
<窗口函数> 的位置，可以放以下两种函数：
1） 专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数。
2） 聚合函数，如sum, avg, count, max, min, collect_set 等。
partition by ：分组子句，表示窗口函数的计算范围，不同的组互不相干；
order by： 排序子句，表示分组后，组内的排序方式，默认是按照升序（asc）排列；
求知鸟
窗口函数有以下功能：
1）同时具有分组（partition by）和排序（order by）的功能；
2）不减少原表的行数。
窗口函数里的partition by和 order by子句的功能是对分组后的结果进行排序，和普通SQL查询语句中的group by 和order by类似。区别在于，group by分组汇总聚合后改变了表的行数，而partition by不会减少原表中的行数。窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
使用方式：
over (partition by xxx)按照xxx分组，无分组时写法为over (partition by 1/null) ；
over (partition by xxx order by xx)按照xxx分组，并以xx排序
Note：以上为Spark SQL的窗口函数使用方式，其他类型数据库可能存在不同使用方式。
案例：
案例1：分组求和以及累计求和
sum1: sum([Number])over(partition by [Class]) 按Class分组求和，等同于小计
sum2: sum([Number])over(partition by [Class] order by [Date]) 按Class分组进行累计求和；
sum3: sum([Number])over(partition by 1)/ sum([Number])over(partition by null) 不分组计算总和，等同于总计。
案例2：分组排序
dense_rank() over(partition by [Class] order by [Number] desc)
row_number() over(partition by 1 order by [Number]desc)
案例3：行偏移
lag([Number])over(partition by [Class] order by [Date] ) 按Class分组日期排序后取上一行数据，取不到值则默认为null.下图中数据相当于环比；
first([Number])over(partition by [Class] order by [Date]) 按Class分组日期排序后取第一行数据，order by [Date]；
last([Number])over(partition by [Class])：按Class分组日期排序后取最后一行数据；last([Number])over(partition by [Class] order by [Date])：按Class分组日期排序后取当前行数据；
进阶用法：
rows/range：窗口子句，是在分组(partition by)后，组内的子分组（也称窗口）。窗口有两种，rows和range，主要用来限制行数和数据范围。窗口子句必须和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW，从当前分组起点到当前行。行比较分析函数lead和lag无窗口子句。
窗口子句常用语法：
rows 和range区别：
1) rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。
如下例中sum_rows：sum([Number]) over (partition by 1 order by [Date] rows between 2 preceding and current row)，是按照日期排序后取前2行和当前行数据的求和。尽管很多日期对应的数据不止一行，有两个Class存在，但是默认窗口内会对Class进行升序排列再选取前2行和当前行进行累计求和计算。
2) range是逻辑窗口，是指定当前行对应值的范围取值，包含子分组（或窗口）里的所有行，和当前行有相同order by值，如果更多的行有同个order by值，当使用range会有更多的行参与计算。
如下例中sum_range: sum([Number]) over (partition by 1 order by [Date] range between 2 preceding and current row), 是按照日期排序后取前2天和当前日期（连续3天）数据的求和。卡片里筛除了2021-01-04的数据。当Date=2021-01-01时，没有前两天日期数据，仅读取当天2条数据，sum=1+3=4；
当Date=2021-01-03时，取2021-01-01，2021-01-02和2021-01-03连续3天的6条数据，sum=(1+3)+(2+3)+(2+4)=15;
当Date=2021-01-05时，没有2021-01-04数据，只取2021-01-03和2021-01-05 两天的4条数据，sum=(2+4)+(4+6) =15; 以此类推下去，结果如下例中所示。
场景：计算移动均值和移动累计
近3天平均值（包含当天）：avg([Number]) over (partition by [Class] order by [Date] rows between 2 preceding and current row)
近3天累计（包含当天）：sum([Number]) over (partition by [Class] order by [Date] rows between 2 preceding and current row)
场景：聚合函数和条件函数的组合 sum over + case when
当满足xx条件，才去做sum聚合；
一般的做法需要用筛选器多拉一条分支，再拼接回原逻辑，利用下面的组合公式，减少步骤且准确。
小编最近开通了群，欢迎知乎的读者公号 求知鸟，添加好友，加群一起讨论。
相关文章：
数据分析笔试题01
数据分析笔试题02
数据分析笔试题03
数据分析笔试题04
数据分析笔试题05
数据分析笔试题06
数据分析笔试题07
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/94140.html
