让决策更智能
新一代智能数据分析平台

窗口函数使用介绍

观小远发表于:2021年07月12日 03:48:36更新于:2021年07月12日 03:59:35

       窗口函数(Window Function),也叫分析函数(Analytics Function),或者OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。

应用场景:

场景1:虽然在观远BI里自带了多种聚合方式,也可以直接用聚合函数新建字段进行计算,但是都是基于维度栏的字段进行聚合,如果需要不按照维度栏字段进行分组聚合,那就需要用到窗口函数来进行计算。

场景2:BI里直接聚合计算得到的数据不能进行筛选和二次计算,如果需要对聚合结果进行筛选和二次计算,需要用到窗口函数来进行计算。

基本语法:

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数> 的位置,可以放以下两种函数:

1) 专用窗口函数,包括rank, dense_rank, row_number等专用窗口函数。

2) 聚合函数,如sum, avg, count, max, min, collect_set 等。

partition by :分组子句,表示窗口函数的计算范围,不同的组互不相干;

order by: 排序子句,表示分组后,组内的排序方式,默认是按照升序(asc)排列;

常见专用窗口函数:

00160eb46d4e9afd451ba3fa0614798

窗口函数有以下功能:

1)同时具有分组(partition by)和排序(order by)的功能;

2)不减少原表的行数。

       窗口函数里的partition by和 order by子句的功能是对分组后的结果进行排序,和普通SQL查询语句中的group by 和order by类似。区别在于,group by分组汇总聚合后改变了表的行数,而partition by不会减少原表中的行数。窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中,在观远BI中可以在新建计算字段里使用。

使用方式:

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) 不分组计算总和,等同于总计。

00160eb46d67e2b7f2a463f45b46002

案例2:分组排序

dense_rank() over(partition by [Class] order by [Number] desc)
row_number() over(partition by 1 order by [Number]desc)

 00160eb46d63c01c2dd2b97a8a266ee

案例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分组日期排序后取当前行数据;

00160eb46d5b41815cfefa38ecdaf63

进阶用法:

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>
                rows/range窗口子句)


rows/range:窗口子句,是在分组(partition by)后,组内的子分组(也称窗口)。窗口有两种,rows和range,主要用来限制行数和数据范围。窗口子句必须和order by 子句同时使用,且如果指定了order by 子句未指定窗口子句,则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW,从当前分组起点到当前行。行比较分析函数lead和lag无窗口子句。

窗口子句常用语法:

PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:无界限(起点或终点)
UNBOUNDED PRECEDING:表示从前面的起点
UNBOUNDED FOLLOWING:表示到后面的终点


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; 以此类推下去,结果如下例中所示。

00160eb46d562cf397c6861a1af6131

场景:计算移动均值和移动累计

近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)

00160eb46d61ac0ee87e369ab1e12f7

Note:

1.  筛选条件先于窗口函数生效,被筛除的数据不会参与计算,所以即使over (partition by 1/null) 也不会计算被筛除的数据;

2.  Partition by分组子句里的字段如果用于页面筛选器,则这个筛选器必须一直有选项被选中并联动卡片,全选或者为空的话计算结果显示不正确,因为设定好的分组不能自动取消;

3.  数据量特别大的情况下,使用窗口函数计算比较耗资源,请按需使用。



    您需要登录后才可以回复