在 2022 年 12 月的更新中,Power BI 正式推出了 DAX 窗口函数。
这是对于 DAX 的一种怎样的补充呢?从大部分 SQL 程序员的角度会认为是加入了窗口函数。其实,将这个内容称为 DAX 窗口函数是不准确的,它和 SQL 的窗口函数也有着一些区别。如果你根本没有 SQL 背景,也丝毫不用担心,因为,DAX 窗口函数的称呼也是为了大致说明它是个什么,而它真正被设计出来的动机一定是为了解决某些 DAX 天生的不足的,因此,才需要加入一个基因片段。我们会用几篇文章来做非常彻底的讲解,并将精华和深度体会纳入《BI 真经》。
关于这个问题的本质阐述,我们将在这个系列的讲解后,再给出与众不同的本质洞察讲解。
准确讲,DAX 窗口函数是一族函数,包括三个:WINDOW,INDEX,OFFSET。以及其中会涉及到的内部函数:ORDERBY 和 PATITIONBY。它们的运作主流程框架是完全相同的,只是在最后一步返回的内容不同。因此,理解任何一个都可以理解这一族。
根据微软官方以及 DAX 之父的描述,OFFSET, INDEX 和 WINDOW 它们统称为窗口函数,因为它们与 SQL 窗口函数密切相关,SQL 窗口函数是 SQL 语言的一个强大特性,允许用户对与当前行相关的一组行进行计算。因为这些函数常用于数据分析,所以有时也称为分析函数。相比之下,DAX 这种专门为数据分析而发明的语言反而还没加入这个功能,显得不能接受。这种缺失导致用户发现很难编写跨行计算,例如计算两行之间某列值的差值或某列值在一组行上的移动平均值。通常,即使利用其他 DAX 函数和技巧可以实现此类计算,实现出来的表达式也很复杂,并导致 DAX 引擎运行效率低下,消耗过多的时间和内存,导致这种方案往往不能适用于大规模的数据。出于这些原因,DAX 产品团队非常兴奋地向 DAX 社区推出了第一批窗口函数作为圣诞礼物。与其对应的 SQL 一样,DAX 窗口函数功能强大,但比大多数其他 DAX 函数更复杂,因此需要更多的学习努力。
另外,微软官方也表示:如果你觉得这几个函数比较难,也不用担心,因为这三个函数是为了通用且极度灵活而考虑的。正如:SUM 与 SUMX 的关系,SUMX 比 SUM 要复杂一些,也比 SUM 更灵活和强大,在 DAX 实际上并没有 SUM 函数,SUM 函数会被翻译成 SUMX 来处理。相信未来 DAX 窗口函数也会出现类似的简化版函数。但作为 DAX 的高级玩家,理解和精通 DAX 窗口函数无疑又将为你的 DAX 武器库增加一套新的装备。
DAX 窗口函数,比我们直觉上理解的要更灵活和适配复杂场景,我们准备用一系列文章来说清楚每个细节。先从体验开始,本文先来实现:
第一,体验下,什么是 DAX 窗口函数。
第二,体验下,DAX 窗口函数存在的意义。
先来看一个典型例子来理解窗口函数的意义。
体验窗口函数 - 移动平均
移动平均往往是移动平均多少天的案例,可以很容易用时间智能函数实现,但如果是移动平均几个月的话,就稍微复杂点了。举例子如下:
当 X 为 3 时候,累计销售额指的是包括自己在内的最近 3 个月的销售额的累计求和,移动平均则是对前者的平均。(注意:如果有年月没有销售额,则不应该记录进入移动平均的分母。)
作为一道考试题(不存在于任何微软的认证中),请不用 DAX 窗口函数实现这个效果。(请自行思考实现)
DAX 窗口函数实现方法,如下:
WindowFun.CaseStudy.按年月移动平均销售额.年月合并 =
VAR xNumber = MIN( 'Var X'[X] ) - 1
RETURN
AVERAGEX(
WINDOW(
-xNumber , REL , 0 , REL ,
ALLSELECTED( 'Dim Calendar'[YearMonthNameCN] , 'Dim Calendar'[YearMonthNum] ) ,
ORDERBY( 'Dim Calendar'[YearMonthNum] )
),
[Sales]
)
这种算法将年和月连接到一起考虑了。但如果将年和月分开使用,则有这样的效果:
DAX 窗口函数实现方法,如下:
WindowFun.CaseStudy.按年月移动平均销售额.年月独立 =
VAR xNumber = MIN( 'Var X'[X] ) - 1
RETURN
AVERAGEX(
WINDOW(
-xNumber , REL , 0 , REL ,
ALLSELECTED(
'Dim Calendar'[YearNameCN] , 'Dim Calendar'[YearNum] ,
'Dim Calendar'[MonthNameCN] , 'Dim Calendar'[MonthNum]
) ,
ORDERBY( 'Dim Calendar'[YearNum] , ASC , 'Dim Calendar'[MonthNum] , ASC )
),
[Sales]
)
注意 这里涉及到一个非常重要的模式 / 技巧,当清除或覆盖一列时,也应该处理它的按列排序列。 后面,我们会再来详细说明这个问题。
至此,这个案例让我们体会到了 WINDOW 的作用,那就是:
将年和月的组合进行排序,取出包括当前行在内的前 X 行,再进行计算。
当你不用 DAX 窗口函数来写出这个案例时,大概能体会出一些 DAX 窗口函数的作用和价值,但不够 WOW。
窗口函数是不是鸡肋
至此日,整个互联网尚未给出一个关于 DAX 窗口函数真正魅力的运用,可见的案例无非是去年同期,移动平均,与上一行的差异这种反而在不断体现 DAX 窗口函数的鸡肋之处的应用,其鸡肋之处在于:
第一,DAX 窗口函数的能力似乎完全可以由传统函数取代。
第二,DAX 窗口函数写东西太复杂了。
第三,DAX 窗口函数没看出来有啥(性能)优势。
第四,DAX 窗口函数实现不了有些传统方法的应用。
如果是以上四点的话,那么 DAX 窗口函数就没啥意义了。
因此,我们必须给出一个案例,能够证明 DAX 窗口函数不仅不是鸡肋,甚至有着重要的存在意义。至少满足:
第一,DAX 窗口函数将大大简化问题解法,尤其在某些场景下。
第二,DAX 窗口函数将大大提升算法性能,尤其在某些场景下。
用窗口函数突破极限
还记得在 2019 年,我们给出的全网最快的最大连续元素数算法,并从数学算法逻辑层面证明:这是不可被超越的算法性能。
那么,这个问题可以被突破吗?答案是从逻辑上是不可以的,但从物理上是可以的。
什么意思?
意思就是当计算同一道数学题的方法类似时,要看谁的底层硬件强,i7 的 CPU 一定比 i5 的更快。
【最大连续元素数问题】可以参考以前的文章,例如:
抽象为数学问题后,就是:
1,0,1,1,0,0,0,1,1,1,0,1,1,0,0,1,1,...
的序列中,1 连续出现的最大次数。
此前已经有了非常充分的探讨,这里就不再重复 DAX 窗口函数出现之前的做法,已经给出了可被数学严格证明的最好的答案。
感兴趣的伙伴可以自行思考和实现无 DAX 窗口函数算法和用有 DAX 窗口函数算法。
注意 这两种方法的实现背后是对 DAX 函数和模式重要的思考,非常重要。
那么,我们要来比较的是:
【A 方法】无 DAX 窗口函数算法,算法逻辑复杂度在:O (n/a)
【B 方法】有 DAX 窗口函数算法,算法逻辑复杂度在:O (n/a)
其中,【A 方法】和【B 方法】持有相同的算法复杂度,都是 O (n/a)。
提示 什么是算法复杂度?这指的是,随着数据量级的增加,整个算法复杂度的攀升趋势,O (n/a) 的算法复杂度级别预示着这种算法已经是理论上最快的了。当数据量级增加的时候,算法的时间消耗增加的量比数据增加的速度还慢。
因此,我们在逻辑上已经设计了理论上足够快的两种算法。那么,它们之间的时间差异就取决于每次原子运算所耗费的时间了,而这个原子运算就是 DAX 的底层操作,如果窗口函数更快,那么,其底层的原子操作一定是更快的,到底快多少呢?
直接给出结果,在这个问题背景下,有 DAX 窗口函数算法的【B 方法】的性能要比无 DAX 窗口函数的【A 方法】快 2 倍,也即是其所消耗时间的 1/3。
直接看结果对比,如下:
对于 100W 数据,无 DAX 窗口函数的【A 方法】用时:2.7 秒,有 DAX 窗口函数的【B 方法】用时:1.0 秒,约为前者 1/3。
对于 500W 数据,无 DAX 窗口函数的【A 方法】用时:15.9 秒,有 DAX 窗口函数的【B 方法】用时:6.1 秒,约为前者 1/3。
对于 1000W 数据,无 DAX 窗口函数的【A 方法】用时:33.1 秒,有 DAX 窗口函数的【B 方法】用时:12.2 秒,约为前者 1/3。
可以看出:
其实,无 DAX 窗口函数的【A 方法】已经非常快了,计算 100W 行数据(序列:1,0,1,1,0,0,.... 有 100W 个元素)只需 2.7 秒;然而有 DAX 窗口函数的【B 方法】将这个时间进一步提升到只需要 1.0 秒。
这是令人惊叹的性能提升。
更重要的事情来了,大家会问这个无 DAX 窗口函数的【A 方法】和有 DAX 窗口函数的【B 方法】是怎么写的?
对于无 DAX 窗口函数的【A 方法】在《BI 真经》中已经有非常彻底而充分的描述,总体说来,大致需要:20 行久经思考且具有高度技巧的 DAX 代码;而对于有 DAX 窗口函数的【B 方法】仅仅只需要 2 行通俗易懂的 DAX 代码即可。
对于其具体实现的详细解读,我们将单独写文章介绍。
总结
首先,我们通过一个例子,年月的移动平均来体会 WINDOW 这个函数,进而体验整个窗口函数系统的作用。如果你自己做实验的话,会发现不用 DAX 窗口函数实现这个需求是需要辅助表或辅助列的,但也还好。
因此,我们提出 DAX 窗口函数的设计鸡肋性,如果要证明它并非鸡肋,我们必须做到:
给出一个案例,能够证明 DAX 窗口函数不仅不是鸡肋,甚至有着重要的存在意义。至少满足:
第一,DAX 窗口函数将大大简化问题解法,尤其在某些场景下。
第二,DAX 窗口函数将大大提升算法性能,尤其在某些场景下。
我们通过对同一个问题:最大连续元素数算法的研究,首次给出了证明:
是的,第一,DAX 窗口函数将大大简化问题解法,尤其在某些场景下,由极度复杂的 20 行缩减为 2 行 DAX 代码。
是的,第二,DAX 窗口函数将大大提升算法性能,尤其在某些场景下,由传统的极限水平再提升 2 到 3 倍。
因此,DAX 窗口函数并非鸡肋,而是具有相当重要的意义。
作为强调,要指出,本文的未尽兴之处会在后续给出。虽然 DAX 窗口函数并非鸡肋,但有一些限制,甚至是很严重的限制,后续给出。DAX 窗口函数看着复杂,是因为它故意被设计成底层模式,未来可能会推出简化版方便使用。
本文留出了两个实验题目,一个是自己实现不用 DAX 窗口函数来实现年月移动平均;一个是实现最大连续元素数算法。
DAX 窗口函数的终极意义的体验已经给出,接下来就是不断说明它的细节的问题,这些我们另外描述。