一文体会 Power BI 新推出 DAX 窗口函数的终极意义
一文体会 Power BI 新推出 DAX 窗口函数的终极意义
在 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 方法】用时的增长与数据量增长呈现同样倍数。
- 对于有 DAX 窗口函数的【B 方法】用时的增长与数据量增长呈现同样倍数。
其实，无 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 窗口函数的终极意义的体验已经给出，接下来就是不断说明它的细节的问题，这些我们另外描述。