DAX 是 PowerBI 中的函數(shù)語(yǔ)言，并非通用類(lèi)編程語(yǔ)言，對(duì)于很多問(wèn)題，無(wú)法像編程語(yǔ)言一樣設(shè)計(jì)解決思路，需要另辟蹊徑。而使用 DAX 設(shè)計(jì)的算法是否可以達(dá)到性能最優(yōu)也是一個(gè)問(wèn)題。
本文達(dá)成兩個(gè)預(yù)期：

• 編寫(xiě)一個(gè)解決復(fù)雜業(yè)務(wù)問(wèn)題的DAX算法
• 對(duì)該算法進(jìn)行性能優(yōu)化
  并展示一個(gè)好玩的現(xiàn)象：
• 普通算法與優(yōu)化算法的性能對(duì)比
• 10000行邏輯查詢(xún)的性能是可能由于1000行查詢(xún)邏輯的

這里的每個(gè)問(wèn)題都十分驚艷，讓我們一起來(lái)了解。

問(wèn)題重述

在很多情況下，我們會(huì)遇到以下場(chǎng)景：

• 對(duì)于某員工，最近一個(gè)月，連續(xù)遲到的最大日數(shù)是多少？
• 對(duì)于某會(huì)員，最近12個(gè)月，連續(xù)每月購(gòu)買(mǎi)的最大月數(shù)是多少？
• 對(duì)于某企業(yè)，最近10年中，每年發(fā)展都增長(zhǎng)的最大連續(xù)年數(shù)是多少？

大家可以自行考慮或嘗試實(shí)現(xiàn)以上問(wèn)題的 PowerBI 中 DAX 實(shí)現(xiàn)。這并不是一個(gè)簡(jiǎn)單的問(wèn)題。

問(wèn)題抽象

為了更好地理解本問(wèn)題，并為未來(lái)擴(kuò)展留有機(jī)會(huì)，這里對(duì)上述問(wèn)題進(jìn)行抽象，如下：

可以看出對(duì)于上述問(wèn)題，均可以描述成由核心兩列完成計(jì)算的過(guò)程。因此，可以對(duì)該問(wèn)題做進(jìn)一步優(yōu)化，得到：

對(duì)問(wèn)題進(jìn)行進(jìn)一步加工抽象，可以得到：

• Index 列，與行號(hào)類(lèi)似。
• Flag 列，指明該用戶(hù)或產(chǎn)品在當(dāng)期有效（真實(shí)環(huán)境中）。

于是問(wèn)題轉(zhuǎn)化成了從Index與Flag構(gòu)成的表中尋找答案。

DAX 算法設(shè)計(jì)

本案例中描述的問(wèn)題比較復(fù)雜，由于DAX中是沒(méi)有循環(huán)結(jié)構(gòu)，導(dǎo)致無(wú)法使用循環(huán)結(jié)構(gòu)來(lái)處理問(wèn)題。

在 PowerBI DAX 中，我們可以通過(guò)技巧來(lái)實(shí)現(xiàn)類(lèi)似循環(huán)結(jié)構(gòu)的效果，我們將這個(gè)效果用于本案例，首先來(lái)看下算法示意圖：

大家可以思考本問(wèn)題的本質(zhì)是幾層循環(huán)結(jié)構(gòu)？

按照上圖的算法思路，我們考慮如下：

• 對(duì)于[Index]的每一行
  • 建立從起始位置到當(dāng)前[Index]位置 n 的結(jié)構(gòu)
  • 對(duì)于該結(jié)構(gòu)的每行 m
    • 建立從 m 到 n 的結(jié)構(gòu)
    • 如果 m 到 n 全是 1 ，則該行為連續(xù)滿(mǎn)足行
  • 獲取連續(xù)滿(mǎn)足行的最大值，則得到連續(xù)滿(mǎn)足條件的最大值
• 再獲取連續(xù)滿(mǎn)足條件的最大值的最大值

因此，可以發(fā)現(xiàn)對(duì)于這里的業(yè)務(wù)問(wèn)題涉及3層循環(huán)結(jié)構(gòu)，在DAX中很可惜是不支持循環(huán)結(jié)構(gòu)的。

DAX 算法實(shí)現(xiàn)

這里使用技巧來(lái)實(shí)現(xiàn)需求，直接上 DAX 算法如下：

Source 的示意結(jié)構(gòu)以及計(jì)算完成的結(jié)構(gòu)為：

通過(guò)對(duì) Source 表加入一個(gè) Value 列來(lái)計(jì)算每行的結(jié)果。

DAX 性能評(píng)估及優(yōu)化

如果將下圖的面積部分視作 DAX工作的負(fù)荷，則：

可以看出，凡是出現(xiàn) 1 的位置，都會(huì)做一個(gè)從頭到當(dāng)前位置的迭代，因此總的算法規(guī)模大致在：
n * ( 1 + n ) * n / 2 ，大致為 n 的三次方規(guī)模，其中 n 為行數(shù)。

通過(guò)增加行數(shù)來(lái)看看算法的可用性隨著時(shí)間的變化：

也就是說(shuō)，當(dāng)?shù)袛?shù)達(dá)到1000行時(shí)，所需時(shí)間規(guī)模在6分鐘（原單位為毫秒，1秒=1000毫秒）。這是一個(gè)不可接受的性能。當(dāng)然在實(shí)際的操作中，可能并不需要有大到1000規(guī)模的迭代。

算法的優(yōu)化設(shè)計(jì)

對(duì)于上述的算法，其實(shí)已經(jīng)做了少許優(yōu)化，算法并不考察每一行，而是僅僅考察Flag=1的行，這樣已經(jīng)減小了計(jì)算規(guī)模，但遠(yuǎn)遠(yuǎn)不夠。其實(shí)還可以在優(yōu)化，我們仔細(xì)再研究該問(wèn)題后，可以得到這樣的算法思路：

對(duì)比之前的算法，從觀察面積表示了算法的計(jì)算規(guī)模（消耗時(shí)間）可以看出優(yōu)化的算法，可以大幅提升性能。其思路是：不從開(kāi)始位置迭代，不然會(huì)產(chǎn)生大量無(wú)效迭代計(jì)算，優(yōu)化的算法從1的位置開(kāi)始迭代，因此可以大幅度縮減計(jì)算規(guī)模。

如果再進(jìn)一步仔細(xì)觀察，會(huì)發(fā)現(xiàn)如果數(shù)據(jù)中存在大量的獨(dú)立點(diǎn)1，也就是說(shuō)：幾乎都是偶爾遲到1次，很少出現(xiàn)連續(xù)多次遲到，這是一種稀疏情形，那么還可以做更進(jìn)一步的優(yōu)化，將針對(duì)第一個(gè) 1 的迭代全部去除，以降低大量稀疏的 1 帶來(lái)的運(yùn)算量，這種運(yùn)算也是意義不大的，算法進(jìn)一步改進(jìn)如下：

可以再次通過(guò)面積來(lái)直觀對(duì)比，可以發(fā)現(xiàn)所需面積大幅度下降，也就是性能再次大幅提升。

如果原問(wèn)題是帶有大量的稀疏的 1 的，全部排出后的算法復(fù)雜度大致為：

k * ( 1 + k ) * k / 2 ，其中 k << n ，n 為行數(shù)，k 為最終的答案值， 且遠(yuǎn)遠(yuǎn)小于 n。

DAX 改進(jìn)算法的實(shí)現(xiàn)

我們看看它的DAX表達(dá)式：

高亮圈選的內(nèi)容就是優(yōu)化的核心所在。

用 DAX Studio 觀測(cè)性能優(yōu)化效果

首先來(lái)比較一下優(yōu)化前后，DAX引擎對(duì)DAX表達(dá)式的處理，也就是翻譯成DAX引擎可以執(zhí)行的邏輯，改良前的邏輯查詢(xún)達(dá)1000行；而改良后的邏輯查詢(xún)達(dá)10000行；問(wèn)題來(lái)了：1000行的效率會(huì)比10000行更高嗎？截圖如下：

優(yōu)化前：

優(yōu)化后：

我們分別記錄不同量級(jí)下的查詢(xún)耗時(shí)來(lái)進(jìn)行分析。

性能實(shí)際測(cè)試分析

如下所示：

這是在 100 行數(shù)據(jù)以?xún)?nèi)，兩種算法效果的對(duì)比。這反應(yīng)了在 60個(gè)元素以?xún)?nèi)，優(yōu)化算法反而看不出優(yōu)化。

隨著數(shù)據(jù)量的增長(zhǎng)，優(yōu)化算法的優(yōu)化被慢慢顯現(xiàn)出來(lái)，如下所示：

可以看出隨著時(shí)間的變化，優(yōu)化算法可以保持很好的穩(wěn)定性，但普通算法在 60 個(gè)元素以后就會(huì)大幅來(lái)到性能瓶頸。

優(yōu)化算法可以處理5000元素在10秒以?xún)?nèi)完成。也就是說(shuō)500個(gè)用戶(hù)在過(guò)去12個(gè)月的最大連續(xù)購(gòu)買(mǎi)月數(shù)。我們?cè)贒AX中運(yùn)行可以看到非常明顯的差異。

為何優(yōu)化后的查詢(xún)更復(fù)雜，而效率反而更高

大家可以留意到優(yōu)化后的查詢(xún)多達(dá)10000行；而優(yōu)化前的查詢(xún)大致是1000行。
由于查詢(xún)復(fù)雜度增加了10倍，因此，表現(xiàn)出：

• 60以?xún)?nèi)的元素，普通算法勝出；
• 100以上元素，優(yōu)化算法大幅勝出。

總結(jié)

本文通過(guò)實(shí)際案例講述了：

• 復(fù)雜DAX的算法設(shè)計(jì)流程
• 算法優(yōu)化流程
• 算法性能的評(píng)估

因此，本文內(nèi)容在有著巨大的實(shí)際業(yè)務(wù)價(jià)值的同時(shí)還有著巨大的示范意義。