本文并非標(biāo)題黨驻民,實事求是講道理翻具,而且讓小白也能讀懂,體會一份喜悅,還不快到PowerBI的碗里來,不信你就讀完它妆距。想象一下,如果從一開始到最后晾虑,你將一個案例的性能優(yōu)化了10W倍,是怎樣一種體驗,這種體驗終于出現(xiàn)在PowerBI中帜篇。首先感謝PowerBI戰(zhàn)友聯(lián)盟的戰(zhàn)友【天行】糙捺,他重新提及了一個問題,隨后開始一場優(yōu)化的旅程笙隙,其實這背后還有很多的故事洪灯,本文就一起來看看這些好玩的事情。
業(yè)務(wù)背景
我們先來看幾個很有意思的業(yè)務(wù)問題再說竟痰。
- 對于某員工签钩,最近一個月,連續(xù)遲到的最大日數(shù)是多少坏快?
- 對于某會員铅檩,最近12個月,連續(xù)每月購買的最大月數(shù)是多少莽鸿?
- 對于某企業(yè)昧旨,最近10年中,每年都虧損的最大連續(xù)年數(shù)是多少祥得?
- 對于某系統(tǒng)兔沃,最近一個月,連續(xù)異常的最大分鐘數(shù)是多少级及?
- 對于某銷售乒疏,最近6個月,連續(xù)業(yè)績最后兩名的最大周數(shù)是多少饮焦?
- 對于某APP怕吴,最近一年,各用戶連續(xù)不登錄的最大天數(shù)是多少追驴?
- ...
請您自行補充更多的案例械哟。這個業(yè)務(wù)背景可以給出兩部分價值:
- 要求是連續(xù)的
- 找其中最大的
這其實蘊含著一個很重要的顯而易見的常識,找到了最大的連續(xù)殿雪,就找到了問題的關(guān)鍵。
問題抽象
問題的抽象锋爪,是一個非常重要的技能丙曙。這項技能來自小學(xué)和初中的交界處。初中時還記得一直好奇一個問題其骄,算術(shù)亏镰、數(shù)學(xué)和代數(shù)為啥名字變了,算術(shù)變成了代數(shù)拯爽。算術(shù)是 1 + 2索抓,而代數(shù)是 X + Y,其本質(zhì)就在于這幫助我們建立了抽象的思維習(xí)慣。代數(shù)的正確解釋是:用字母代替數(shù)字逼肯,簡稱代數(shù)耸黑。(讓你家小朋友問老師,很多初中數(shù)學(xué)老師居然回答不出這個問題~)
數(shù)字是具體的篮幢,字母是抽象而通用的大刊,那么,我們把業(yè)務(wù)問題抽象后三椿,去解決抽象一些的問題缺菌,如果問題可以得到解決,那就解決了一堆同質(zhì)的問題搜锰。
例如:
通過觀察伴郁,我們可以將如上所述的很多具體問題抽象為:
其中,Index表示序號蛋叼,F(xiàn)lag表示標(biāo)記蛾绎,例如:1可以表示該序號處遲到,虧損鸦列,購買租冠,異常等。
問題徹底轉(zhuǎn)化為一個純數(shù)字問題:一列由若干不確定位置的1中最大的連續(xù)個數(shù)薯嗤。如果可以解決這個問題顽爹,那么上述的所有業(yè)務(wù)問題都可以輕松的計算出答案。當(dāng)然骆姐,要求是在PowerBI中實現(xiàn)镜粤。
問題解決
該問題已于2018.11徹底解決,見此前發(fā)文:《PowerBI DAX處理復(fù)雜業(yè)務(wù)到性能優(yōu)化1000倍》(看完本文再看)玻褪。最后得到一個結(jié)論:
該文給出了兩種計算方法肉渴,快的方法的舒適區(qū)也僅僅在在1000個數(shù)以內(nèi),在計算10000個數(shù)時大致需要48秒带射。而慢的方法根本就無法勝任了同规。
在次日,《從數(shù)據(jù)到Excel自動化報表》作者窟社,黃海劍老師給出一個建議:
黃老師大致的方法是:
這個方法的確是可以的券勺,而且比原來的方法更加簡單,速度也更快灿里。在隨后的工作中关炼,我們也的確處理過類似的不少問題,但并沒有將該方法補充進(jìn)來匣吊。
直到PowerBI戰(zhàn)友聯(lián)盟的伙伴【天行】用類似的方法給出了一個實現(xiàn):
作為一種完整儒拂,以及對事物追求完美的沖動寸潦,有必要給出一個終極的標(biāo)準(zhǔn)實現(xiàn),于是就有了:
MaxContinueItems =
VAR vT1 = ADDCOLUMNS( SampleData , "累計" , VAR vX = [Item] RETURN SUMX( SampleData , ( [Item] <= vX ) * ( 1 - [Flag] ) ) )
VAR vT2 = SUMMARIZE( vT1 , [累計] , "出現(xiàn)次數(shù)" , VAR vX = [累計] RETURN COUNTROWS( FILTER( vT1 , [累計] = vX ) ) )
RETURN MAXX( vT2 , [出現(xiàn)次數(shù)] ) - 1
該方法非常簡單(讀者可自行Ctrl C+V使用)社痛,而且巧妙地處理了多個DAX可優(yōu)化點见转,可以得到的性能是:
處理1000個數(shù)字,結(jié)果是:
不到0.2秒褥影,非常好的結(jié)果池户。
這幾乎是無懈可擊的方法。
無巧不成書
就在同一天凡怎,一個用PowerBI展示系統(tǒng)監(jiān)測異常的問題來了校焦,問:最近1個月,主機系統(tǒng)的最大連續(xù)異常分鐘數(shù)统倒。哈哈寨典,又是同樣的問題,不過不同的是房匆,它需要的數(shù)據(jù)點是 60分鐘×24小時×30天= 43200耸成,我們看看目前為止,最好的計算方法對此會是怎樣浴鸿,先來看看10000個數(shù)字的情況:
需要將近12秒的時間處理10000個數(shù)字井氢。似乎,已經(jīng)不能讓人滿意呢岳链。接著模擬了大致60000個點的情況花竞,等了1分鐘,結(jié)果根本無法得到結(jié)果掸哑。等啊等啊等啊约急,終于出來結(jié)果了:
用時372秒,超過了10分鐘苗分。
提出問題的伙伴重金懸賞說可不可以優(yōu)化到30秒以內(nèi)給出結(jié)果厌蔽,在重大的利益誘惑下,還是欣然接受了挑戰(zhàn)摔癣,然后翻出了神書:
然而奴饮,并沒有什么卵用。此處純屬娛樂供填,大家完全沒有必要去看這些拐云。但是,很多好的思想框架是需要的近她。
因為PowerBI根本不是C語言,也無法用通用的方式去實現(xiàn)膳帕,想著想著粘捎,就這樣睡著了薇缅。在半夢半醒之間,突然夢見了一種莫名其妙的方法攒磨。然后早上起來泳桦,就這樣輸入到了PowerBI中,于是得到了這樣的結(jié)果:
看得我驚呆了娩缰,太兇殘了灸撰,3秒。
我又試了試拼坎,還是3秒浮毯,性能提升了100倍,不敢相信泰鸡。
我又試了試债蓝,還是3秒,性能提升了100倍盛龄,不太敢相信饰迹。
我又試了試,還是3秒余舶,性能提升了100倍啊鸭,才信了。
到底夢到了什么呢匿值,夢到了一個公式赠制,如下:
不是無巧不成書,而成了天書千扔,請原諒我也不知道這是什么意思了憎妙,想直接Ctrl C+V的如下:
MaxContinueItems.Premium =
VAR b = CALCULATETABLE( VALUES( Data[Item] ) , Data[Flag] = 0 ) VAR d = SUBSTITUTEWITHINDEX( ADDCOLUMNS( b , "xx" , [Item] ) , "iii" , b, [Item] , ASC ) VAR e = COUNTROWS( d ) VAR f = e / 53 VAR g = IF( f = INT( f ) , f - 1 , INT( f ) ) VAR h = ADDCOLUMNS( SELECTCOLUMNS( GENERATESERIES( 0 , g ) , "zz" , [Value] ) , "yy" , SELECTCOLUMNS( FILTER( d , [iii] = ( [zz] * 53 ) ) , "nb" , [xx] ) ) RETURN MAXX( ADDCOLUMNS( ADDCOLUMNS( h , "mm" , IF( [zz] = g , 1/0 , SELECTCOLUMNS( FILTER( h , [zz] = ( EARLIER( [zz] ) + 1 ) ) , "sb" , [yy] - 1 ) ) ) , "q" , VAR i = FILTER( Data , Data[Item] >= [yy] && Data[Item] <= [mm] ) VAR j = ADDCOLUMNS( i , "kk" , VAR c = [Item] RETURN SUMX( i , ( [Item] <= c ) * ( 1 - [Flag] ) ) ) VAR k = ADDCOLUMNS( SUMMARIZE( j , [kk] ) , "uu" , VAR c = [kk] RETURN COUNTROWS( FILTER( j , [kk] = c ) ) ) RETURN MAXX( k , [uu] ) - 1 ) , [q] )
不要問我為什么,你也不需要理解曲楚,總之厘唾,復(fù)制粘貼拿去用就是了。如果您真的特別好奇龙誊,請為您的好奇買單吧抚垃,成為PowerBI戰(zhàn)友聯(lián)盟的訂閱會員,聽佐羅老師視頻講解透徹背后的奧秘趟大。沒錯鹤树,此處就是明目張膽的廣告插入,你花的錢買懂這一個公式都是值得的逊朽。
善于觀察的伙伴或問罕伯,里面為什么有個53?53是干嘛的叽讳?如果我回答因為一副撲克牌有53張追他,所以這里是53坟募,你信嗎?不管你信不信邑狸,確實如此懈糯。
完美的啟示
令人吃驚的是,這個超級終極方法看上去無比復(fù)雜单雾,卻可以實現(xiàn)比堪稱完美的方法提升100倍的性能赚哗,還有什么不可以呢?
我依稀清楚的記得硅堆,那些年在論壇里討論奇葩的Excel公式的時光屿储,而現(xiàn)在從Excel轉(zhuǎn)型PowerBI的伙伴,你終于有更好玩的公式了硬萍,這個DAX公式扩所,相信具有一定的里程碑意義,它啟示了我們兩點:
- 從復(fù)雜(原有公式)到簡單(完美公式)朴乖,再到復(fù)雜(超級完美公式)祖屏,而本質(zhì)卻是更加簡單(最高性能)。
- 沒有最好买羞,只有更好袁勺,優(yōu)化是永無止境的,甚至是恐怖的畜普。
從而期丰,現(xiàn)實的業(yè)務(wù)問題得到了終極的解決。小姐姐的懸賞是30秒計算50000個點吃挑,而她得到了3秒計算60000個點的結(jié)果钝荡,超預(yù)期10倍,一天的心情都好了舶衬。
至此埠通,我們得到了一個可以計算非常多類似業(yè)務(wù)模式的終極PowerBI方法,所有的人只要復(fù)制粘貼就可以了逛犹。
感謝過程中第一次提出業(yè)務(wù)場景的信克老師端辱,黃老師,天行虽画,神秘姐姐等舞蔽,你們的鼓勵,我們的動力码撰。
總結(jié)
從給出的第一個方法到優(yōu)化了1000倍的性能渗柿,發(fā)現(xiàn)仍然不行;進(jìn)一步優(yōu)化脖岛,得到了近乎完美的化簡做祝;由于應(yīng)對更復(fù)雜情況砾省,仍然不行鸡岗;進(jìn)一步優(yōu)化了100倍的性能混槐,得到了總計10萬倍的性能優(yōu)化,毫不夸張轩性。
PowerBI戰(zhàn)友聯(lián)盟声登,定位于PowerBI世界最前沿,本文就是注解揣苏。將一堆現(xiàn)實問題找出共同模式悯嗓,然后歸納出無懈可擊的完美解決方案就是我們要做的。我們結(jié)合眾多實力戰(zhàn)友卸察,用打造奢侈品的態(tài)度調(diào)教和打磨每個細(xì)節(jié)脯厨。
如果你非常愿意學(xué)習(xí)PowerBI,又不差錢坑质,可以學(xué)習(xí)我們的VIP課程合武,就像公式一樣,可以急速減少優(yōu)化您成為高手的時間涡扼,也許比普通人快100倍哦稼跳。尊敬的會員同志們,視頻已經(jīng)在你們的文件夾吃沪,下載后觀看BI佐羅為您講解天書級完美公式汤善。