部落格:www。jiaopengzi。com
一、背景
最大連續次數或者是最大連續子序列問題,在DAX中如何快速計算呢?
思路
1、N-1:按照INDEX錯位
2、START:連續第一齣現INDEX
3、END:最後一次出現的INDEX下一位
4、END-START剛好等於連續出現的次數
5、T8為結果
10萬條資料只需要不到0。2秒,速度相當可觀了,已能解決大部分實際工作場景。
二、資料來源
為了方便展示,設定了7到28,1出現最大次數28-7=21;拿到檔案可以自行修改再驗證。
三、上DAX
分步DAX
DEFINEVAR T0 =ADDCOLUMNS ( DATA,“N-1”,VAR I0 = DATA[INDEX]VAR V0 =CALCULATE ( SUM ( DATA[VALUE] ), FILTER ( DATA, DATA[INDEX] = I0- 1 ) )RETURN V0,“START”,VAR I1 = DATA[INDEX]VAR V1 =CALCULATE ( SUM ( DATA[VALUE] ), FILTER ( DATA, DATA[INDEX] = I1 - 1 ) )RETURNIF ( V1 <> [VALUE]&& V1 = 0, [INDEX] ),“END”,VAR I2 = DATA[INDEX]VAR V2 = CALCULATE ( SUM ( DATA[VALUE] ), FILTER ( DATA, DATA[INDEX] = I2 - 1 ) )RETURNIF ( V2 <> [VALUE]&& V2 = 1, [INDEX] ) )VAR T1 =ADDCOLUMNS (DATA,“START”,VAR I1 = DATA[INDEX]VAR V1 =CALCULATE ( SUM ( DATA[VALUE] ), FILTER ( DATA, DATA[INDEX] = I1 - 1 ) )RETURNIF ( V1 <> [VALUE]&& V1 = 0, [INDEX] ),“END”,VAR I2 = DATA[INDEX]VAR V2 = CALCULATE ( SUM ( DATA[VALUE] ), FILTER ( DATA, DATA[INDEX] = I2 - 1 ) )RETURNIF ( V2 <> [VALUE]&& V2 = 1, [INDEX] ))VAR T2 =SELECTCOLUMNS ( FILTER ( T1, [START] > 0 ), “START”, [INDEX] )VAR T3 = SELECTCOLUMNS ( FILTER ( T1, [END] > 0 ), “END”, [INDEX] )VAR T4 =SUBSTITUTEWITHINDEX (ADDCOLUMNS ( T2, “S”, [START] ), “INDEX”, T2,[START], ASC)VAR T5 =SUBSTITUTEWITHINDEX (ADDCOLUMNS ( T3, “E”, [END] ), “INDEX”, T3, [END], ASC )VAR T6 =ADDCOLUMNS (T4,“E”,VAR I = [INDEX]RETURNCALCULATE ( SUMX ( FILTER ( T5, [INDEX] = I ), [E] ) ))VAR T7 = ADDCOLUMNS ( T6, “MAX”, [E] - [S] )VAR T8 =ROW ( “最大連續次數”, MAXX ( T6, [E] - [S] ) )EVALUATET8
分步說明
1、T1錯位找出1開始的index和結束的index
2、T4找到開始index,使用SUBSTITUTEWITHINDEX建立新index
3、T5找到結束index,使用SUBSTITUTEWITHINDEX建立新index
5、T7透過
新index
把1出現次數首尾
老INDEX
放到一起,實現“END-START剛好等於連續出現的次數”。
這裡注意新老INDEX,本來可以給新的欄位名,難得想名字了,使用的時候要注意有點繞。
6、最終度量:最大連續次數
最大連續次數:=VAR T1 = ADDCOLUMNS ( DATA, “START”, VAR I1 = DATA[INDEX] VAR V1 =CALCULATE ( SUM ( DATA[VALUE] ), FILTER ( DATA, DATA[INDEX] = I1 - 1 ) ) RETURN IF ( V1 <> [VALUE] && V1 = 0, [INDEX] ), “END”, VAR I2 = DATA[INDEX] VAR V2 = CALCULATE ( SUM ( DATA[VALUE] ), FILTER ( DATA, DATA[INDEX] = I2 - 1 ) ) RETURN IF ( V2 <> [VALUE] && V2 = 1, [INDEX] ) ) VAR T2 =SELECTCOLUMNS ( FILTER ( T1, [START] > 0 ), “START”, [INDEX] ) VAR T3 = SELECTCOLUMNS ( FILTER ( T1, [END] > 0 ), “END”, [INDEX] ) VAR T4 =SUBSTITUTEWITHINDEX ( ADDCOLUMNS ( T2, “S”, [START] ) , “INDEX” , T2 ,[START] , ASC) VAR T5 =SUBSTITUTEWITHINDEX ( ADDCOLUMNS ( T3, “E”, [END] ) , “INDEX” , T3 , [END] , ASC ) VAR T6 =ADDCOLUMNS ( T4, “E”, VAR I = [INDEX] RETURN CALCULATE ( SUMX ( FILTER ( T5, [INDEX] = I ), [E] ) ) )RETURN MAXX ( T6, [E] - [S] )
四、總結
1、本案例中使用INDEX錯位,從結構上去最佳化計算效率;
2、對SUBSTITUTEWITHINDEX的熟悉;
3、類似案例要多思考計算的本質。
by 焦棚子