DAX 是 PowerBI 中的函数语言，并非通用类编程语言，对于很多问题，无法像编程语言一样设计解决思路，需要另辟蹊径。而使用 DAX 设计的算法是否可以达到性能最优也是一个问题。
本文达成两个预期：
- 编写一个解决复杂业务问题的DAX算法
- 对该算法进行性能优化
并展示一个好玩的现象： - 普通算法与优化算法的性能对比
- 10000行逻辑查询的性能是可能由于1000行查询逻辑的
这里的每个问题都十分惊艳，让我们一起来了解。
问题重述
在很多情况下，我们会遇到以下场景：
- 对于某员工，最近一个月，连续迟到的最大日数是多少？
- 对于某会员，最近12个月，连续每月购买的最大月数是多少？
- 对于某企业，最近10年中，每年发展都增长的最大连续年数是多少？
大家可以自行考虑或尝试实现以上问题的 PowerBI 中 DAX 实现。这并不是一个简单的问题。
问题抽象
为了更好地理解本问题，并为未来扩展留有机会，这里对上述问题进行抽象，如下：
可以看出对于上述问题，均可以描述成由核心两列完成计算的过程。因此，可以对该问题做进一步优化，得到：
对问题进行进一步加工抽象，可以得到：
- Index 列，与行号类似。
- Flag 列，指明该用户或产品在当期有效（真实环境中）。
于是问题转化成了从Index与Flag构成的表中寻找答案。
DAX 算法设计
本案例中描述的问题比较复杂，由于DAX中是没有循环结构，导致无法使用循环结构来处理问题。
在 PowerBI DAX 中，我们可以通过技巧来实现类似循环结构的效果，我们将这个效果用于本案例，首先来看下算法示意图：
大家可以思考本问题的本质是几层循环结构？
按照上图的算法思路，我们考虑如下：
- 对于[Index]的每一行
- 建立从起始位置到当前[Index]位置 n 的结构
- 对于该结构的每行 m
- 建立从 m 到 n 的结构
- 如果 m 到 n 全是 1 ，则该行为连续满足行
- 获取连续满足行的最大值，则得到连续满足条件的最大值
- 再获取连续满足条件的最大值的最大值
因此，可以发现对于这里的业务问题涉及3层循环结构，在DAX中很可惜是不支持循环结构的。
DAX 算法实现
这里使用技巧来实现需求，直接上 DAX 算法如下：
Source 的示意结构以及计算完成的结构为：
通过对 Source 表加入一个 Value 列来计算每行的结果。
DAX 性能评估及优化
如果将下图的面积部分视作 DAX工作的负荷，则：
可以看出，凡是出现 1 的位置，都会做一个从头到当前位置的迭代，因此总的算法规模大致在：
n * ( 1 + n ) * n / 2 ，大致为 n 的三次方规模，其中 n 为行数。
通过增加行数来看看算法的可用性随着时间的变化：
也就是说，当迭代行数达到1000行时，所需时间规模在6分钟（原单位为毫秒，1秒=1000毫秒）。这是一个不可接受的性能。当然在实际的操作中，可能并不需要有大到1000规模的迭代。
算法的优化设计
对于上述的算法，其实已经做了少许优化，算法并不考察每一行，而是仅仅考察Flag=1的行，这样已经减小了计算规模，但远远不够。其实还可以在优化，我们仔细再研究该问题后，可以得到这样的算法思路：
对比之前的算法，从观察面积表示了算法的计算规模（消耗时间）可以看出优化的算法，可以大幅提升性能。其思路是：不从开始位置迭代，不然会产生大量无效迭代计算，优化的算法从1的位置开始迭代，因此可以大幅度缩减计算规模。
如果再进一步仔细观察，会发现如果数据中存在大量的独立点1，也就是说：几乎都是偶尔迟到1次，很少出现连续多次迟到，这是一种稀疏情形，那么还可以做更进一步的优化，将针对第一个 1 的迭代全部去除，以降低大量稀疏的 1 带来的运算量，这种运算也是意义不大的，算法进一步改进如下：
可以再次通过面积来直观对比，可以发现所需面积大幅度下降，也就是性能再次大幅提升。
如果原问题是带有大量的稀疏的 1 的，全部排出后的算法复杂度大致为：
k * ( 1 + k ) * k / 2 ，其中 k << n ，n 为行数，k 为最终的答案值， 且远远小于 n。
DAX 改进算法的实现
我们看看它的DAX表达式：
高亮圈选的内容就是优化的核心所在。
用 DAX Studio 观测性能优化效果
首先来比较一下优化前后，DAX引擎对DAX表达式的处理，也就是翻译成DAX引擎可以执行的逻辑，改良前的逻辑查询达1000行；而改良后的逻辑查询达10000行；问题来了：1000行的效率会比10000行更高吗？截图如下：
优化前：
优化后：
我们分别记录不同量级下的查询耗时来进行分析。
性能实际测试分析
如下所示：
这是在 100 行数据以内，两种算法效果的对比。这反应了在 60个元素以内，优化算法反而看不出优化。
随着数据量的增长，优化算法的优化被慢慢显现出来，如下所示：
可以看出随着时间的变化，优化算法可以保持很好的稳定性，但普通算法在 60 个元素以后就会大幅来到性能瓶颈。
优化算法可以处理5000元素在10秒以内完成。也就是说500个用户在过去12个月的最大连续购买月数。我们在DAX中运行可以看到非常明显的差异。
为何优化后的查询更复杂，而效率反而更高
大家可以留意到优化后的查询多达10000行；而优化前的查询大致是1000行。
由于查询复杂度增加了10倍，因此，表现出：
- 60以内的元素，普通算法胜出；
- 100以上元素，优化算法大幅胜出。
总结
本文通过实际案例讲述了：
- 复杂DAX的算法设计流程
- 算法优化流程
- 算法性能的评估
因此，本文内容在有着巨大的实际业务价值的同时还有着巨大的示范意义。
网友评论