DAX高級：GROUPBY 和 SUMMARIZE 之間的區(qū)別

本文翻譯自 Marco Russo & Alberto Ferrari 的文章《Differences between GROUPBY and SUMMARIZE》。

GROUPBY 和 SUMMARIZE 都是用于按列進行分組的函數(shù)。然而，它們在性能和功能上存在差異。了解詳細信息可以幫助開發(fā)人員在特定的場景下選擇正確函數(shù)。

DAX 提供了豐富的函數(shù)集，其中一些函數(shù)的功能是重疊的。在眾多函數(shù)中，有兩個函數(shù)執(zhí)行分組操作：SUMMARIZE 和 GROUPBY。這并不是僅有的兩個執(zhí)行分組操作的函數(shù)：SUMMARIZECOLUMNS 和GROUPCROSSAPPLY 也執(zhí)行類似的操作。然而，本文主要關注 SUMMARIZE 和 GROUPBY，因為其他函數(shù)擁有更多功能，所以進行比較可能不公平。

現(xiàn)在讓我們詳細介紹這些函數(shù)的工作方式，以提供關于前面陳述的更多技術信息。

SUMMARIZE 函數(shù)執(zhí)行兩個操作：按列進行分組和添加新的本地列。我們之前已經(jīng)在一篇詳細且技術性較強的文章中介紹過 SUMMARIZE 函數(shù)：《SUMMARIZE 的所有秘密》。在那篇文章中，我們描述了 SUMMARIZE 的行為以及為什么不應該使用它來計算新的本地列。具體而言，SUMMARIZE 實現(xiàn)了聚類，這是一種雖然非常強大但可能導致錯誤結果和較差性能的分組技術。

然而，為了進行比較，我們將使用 SUMMARIZE 函數(shù)來計算新的列，以描述其獨特的行為。

在簡單示例中使用 SUMMARIZE 函數(shù)時，它表現(xiàn)得很好，將分組操作下推到存儲引擎。例如，以下代碼可以正常工作，并產(chǎn)生預期的存儲引擎查詢：

EVALUATE
SUMMARIZE (
    Sales,
    'Product'[Brand],
    "Sales Amount", [Sales Amount]
)

SUMMARIZE 函數(shù)會掃描 Sales，按 Product[Brand] 進行分組，并按 brand 生成銷售額。存儲引擎查詢?nèi)缦滤荆?/span>

WITH
    $Expr0 := ( PFCAST ( 'Sales'[Quantity] AS INT ) * PFCAST ( 'Sales'[Net Price] AS INT ) ) 
SELECT
    'Product'[Brand],
    SUM ( @$Expr0 )
FROM 'Sales'
    LEFT OUTER JOIN 'Product'
        ON 'Sales'[ProductKey]='Product'[ProductKey];錯誤去粘貼吧

然而，一旦執(zhí)行的度量值稍微復雜起來，這種簡單的行為很容易喪失。實際上，SUMMARIZE 函數(shù)使用一種名為“聚類”的特殊技術來進行計算，請看以下代碼：

EVALUATE
SUMMARIZE (
    Sales,
    'Product'[Brand],
    "Sales Amount", [Sales Amount],
    "Sales All Brands",
        CALCULATE (
            [Sales Amount],
            REMOVEFILTERS ( Product[Brand] )
        )
)錯誤去粘貼吧

可以預計到 Sales All Brands 會計算出銷售總額，因為 CALCULATE 函數(shù)會移除過濾器上下文中的唯一過濾器。然而，這種推測并未考慮聚類。由于聚類的存在，SUMMARIZE 函數(shù)所放置的過濾器會影響擴展的 Sales 表的所有列，導致了這種奇怪的結果。

正如你所看到的，Sales All Brands 與 Sales Amount 的值相同。不同的數(shù)據(jù)分布或重復的行可能會導致不同的值。此外，由于聚類的存在，一旦要進行聚合的度量值是很重要的，SUMMARIZE 需要實例化整個表。計算 Sales All Brands，以下是正在執(zhí)行的 VertiPaq 查詢之一：

WITH
    $Expr0 := ( PFCAST ( 'Sales'[Quantity] AS INT ) * PFCAST ( 'Sales'[Net Price] AS INT ) ) 
SELECT
    'Sales'[Order Number],
    'Sales'[Line Number],
    'Sales'[Order Date],
    'Sales'[Delivery Date],
    'Sales'[CustomerKey],
    'Sales'[StoreKey],
    'Sales'[ProductKey],
    'Sales'[Quantity],
    'Sales'[Unit Price],
    'Sales'[Net Price],
    'Sales'[Unit Cost],
    'Sales'[Currency Code],
    'Sales'[Exchange Rate],
    SUM ( @$Expr0 )
FROM 'Sales';錯誤去粘貼吧

請注意，RowNumber 不是查詢的一部分，因此數(shù)據(jù)緩存的粒度與 Sales 的粒度不完全相同，就像使用 GROUPBY 一樣。然而，由于表的所有列都被用作分組依據(jù)列，因此大小通常非常相關。

使用 SUMMARIZE 和 ADDCOLUMNS 的相同查詢會產(chǎn)生預期的結果：

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        Sales,
        'Product'[Brand]
    ),
    "Sales Amount", [Sales Amount],
    "Sales All Brands",
        CALCULATE (
            [Sales Amount],
            ALL ( Product[Brand] )
        )
)錯誤去粘貼吧

結果如下。

得益于聚類，SUMMARIZE 也可以對本地列進行分組。盡管按照本地列進行分組，以下查詢?nèi)匀豢梢哉９ぷ鳎?/span>

EVALUATE
SUMMARIZE (
    ADDCOLUMNS (
        Sales,
        "Transaction Size",
            IF (
                Sales[Quantity] > 3,
                "Large",
                "Small"
            )
    ),
    [Transaction Size],
    "Sales Amount", [Sales Amount]
)錯誤去粘貼吧

結果顯示了按交易規(guī)模分組的銷售金額。

然而，請記住，盡管從句法和語義的角度來看，這個查詢是有效的，但其結果是通過使用聚類來計算的。聚類在多種情況下會產(chǎn)生令人驚訝的結果，并且它帶來的問題多于解決方案。此外，在這種情況下，計算也需要實例化整個 Sales 表。

GROUPBY 按表的一列進行分組。該列可以是模型列或本地列。然而，它的行為與 SUMMARIZE 的行為非常不同。GROUPBY 甚至不會將計算下推到存儲引擎：整個計算在表被實例化后在公式引擎中進行。GROUPBY 還可以為其結果添加新列。但是，由于其特性，新列需要被計算為使用CURRENTGROUP 特殊函數(shù)對正在進行分組的表中的列的簡單聚合。

舉個例子，讓我們看一下以下代碼：

EVALUATE
GROUPBY (
    Sales,
    'Product'[Brand],
    "Sales Amount",
        SUMX (
            CURRENTGROUP (),
            Sales[Quantity] * Sales[Net Price]
        )
)錯誤去粘貼吧

GROUPBY 掃描 Sales 并按 Product[Brand] 進行分組。為了執(zhí)行分組，DAX會將 Sales 中所需的列實例化到一個數(shù)據(jù)緩存中，然后由公式引擎進行處理。實際上，查詢執(zhí)行了以下代碼：

SELECT
    'Product'[Brand],
    'Sales'[RowNumber],
    'Sales'[Quantity],
    'Sales'[Net Price]
FROM 'Sales'
    LEFT OUTER JOIN 'Product'
        ON 'Sales'[ProductKey]='Product'[ProductKey];錯誤去粘貼吧

在 Sales 表中，DAX 檢索了 Sales[Quantity]、Sales[Net Price] 和 Product[Brand]。Sales[RowNumber] 的存在保證了所有行都被檢索出來，否則 VertiPaq 本身會執(zhí)行分組操作。

結果是一個與 Sales 具有相同行數(shù)的表，因此可能非常大。公式引擎會掃描該表，根據(jù) Product[Brand] 將其分成不同的群組，然后針對每個群組計算 Sales[Quantity] 與 Sales[Net Price]的乘積之和。

GROUPBY 的一個明顯限制是，在迭代 CURRENTGROUP 時所使用的表達式不能涉及上下文轉(zhuǎn)換。這個限制使得在迭代中使用現(xiàn)有的度量是不可能的。正如你可能注意到的，我們在示例中不得不重新編寫了 Sales Amount 的代碼。

盡管看起來較慢，GROUPBY 是唯一能夠?qū)]有繼承關系的表執(zhí)行分組和計算的 DAX 函數(shù)。例如，以下查詢通過表的一列對本地表進行分組，而 GROUPBY 是唯一能夠執(zhí)行此操作的函數(shù)：

EVALUATE
VAR TableToGroup =
    SELECTCOLUMNS (
        {
            ( "A", 1 ),
            ( "A", 2 ),
            ( "B", 3 ),
            ( "B", 4 )
        },
        "Group", [Value1],
        "Value", [Value2]
    )
RETURN
    GROUPBY (
        TableToGroup,
        [Group],
        "Result",
            SUMX (
                CURRENTGROUP (),
                [Value]
            )
    )錯誤去粘貼吧

當使用其他 DAX 函數(shù)生成一個小表，然后需要對其中的一列進行分組，并對每一行進行簡單的聚合時，GROUPBY 是正確的函數(shù)選擇。

正如你所見，當需要按模型進行列分組時，SUMMARIZE 表現(xiàn)良好。盡管它具有按本地列分組的能力，但它使用了聚類，其結果通常是意想不到的。GROUPBY 不使用聚類。然而，它具有一個非常強大的限制：它總是需要將需要分組的表實例化。因此，在執(zhí)行按模型列分組時，它可能不是最佳選擇，而 ADDCOLUMNS/SUMMARIZE 效率通常更高。

然而，當需要對一個小型臨時表按本地列進行分組時，GROUPBY 是最佳的函數(shù)，因為它可以在不依賴聚類的情況下完成工作。

明智的 DAX 開發(fā)人員會根據(jù)工作的需求選擇合適的函數(shù)，通常會混合使用 SUMMARIZE、ADDCOLUMNS 和 GROUPBY，以獲得最佳的性能和正確的結果。讓我們通過一個例子來詳細說明。

EVALUATE
SUMMARIZE (
    ADDCOLUMNS (
        Sales,
        "Transaction Size",
            IF (
                Sales[Quantity] > 3,
                "Large",
                "Small"
            )
    ),
    [Transaction Size],
    "Sales Amount", [Sales Amount]
)錯誤去粘貼吧

這個查詢使用了 SUMMARIZE，因此涉及到聚類。它執(zhí)行了兩個 VertiPaq 查詢。第一個查詢基本上將 Sales 進行了實例化：

SELECT
    'Sales'[Order Number],
    'Sales'[Line Number],
    'Sales'[Order Date],
    'Sales'[Delivery Date],
    'Sales'[CustomerKey],
    'Sales'[StoreKey],
    'Sales'[ProductKey],
    'Sales'[Quantity],
    'Sales'[Unit Price],
    'Sales'[Net Price],
    'Sales'[Unit Cost],
    'Sales'[Currency Code],
    'Sales'[Exchange Rate]
FROM 'Sales';誤去粘貼吧

第二個存儲引擎查詢使用了第一個查詢的結果來構建對 Sales 的大規(guī)模過濾器：

WITH
    $Expr0 := ( PFCAST ( 'Sales'[Quantity] AS INT ) * PFCAST ( 'Sales'[Net Price] AS INT ) ) 
SELECT
    'Sales'[Order Number],
    'Sales'[Line Number],
    'Sales'[Order Date],
    'Sales'[Delivery Date],
    'Sales'[CustomerKey],
    'Sales'[StoreKey],
    'Sales'[ProductKey],
    'Sales'[Quantity],
    'Sales'[Unit Price],
    'Sales'[Net Price],
    'Sales'[Unit Cost],
    'Sales'[Currency Code],
    'Sales'[Exchange Rate],
    SUM ( @$Expr0 )
FROM 'Sales'
WHERE
    ( 'Sales'[Exchange Rate], 'Sales'[Currency Code], 'Sales'[Unit Cost], 'Sales'[Net Price], 'Sales'[Unit Price], 'Sales'[Quantity], 
      'Sales'[ProductKey], 'Sales'[StoreKey], 'Sales'[CustomerKey], 'Sales'[Delivery Date], 'Sales'[Order Date], 
      'Sales'[Line Number], 'Sales'[Order Number] ) 
    IN { ( 1.000000, 'USD', 1227800, 2536500, 2670000, 1, 1507, 999999, 1573592, 43818.000000, 43816.000000, 1, 363800 ) , 
         ( 0.914500, 'EUR', 1677300, 2928100, 3290000, 2, 241, 999999, 587554, 43739.000000, 43736.000000, 2, 355804 ) , 
         ( 0.902900, 'EUR', 676000, 1470000, 1470000, 1, 668, 340, 884269, 43693.000000, 43693.000000, 1, 351503 ) , 
         ( 1.335200, 'CAD', 322500, 701300, 701300, 3, 1707, 999999, 278457, 43473.000000, 43472.000000, 1, 329404 ) , 
         ( 1.000000, 'USD', 1480780, 3220000, 3220000, 3, 1410, 999999, 1582937, 43095.000000, 43090.000000, 0, 291214 ) , 
         ( 1.297600, 'CAD', 3214400, 6990000, 6990000, 1, 405, 80, 326829, 43836.000000, 43836.000000, 2, 365800 ) ,  
         ( 1.000000, 'USD', 300800, 513300, 590000, 2, 501, 999999, 1540547, 43818.000000, 43813.000000, 1, 363503 ) , 
         ( 1.000000, 'USD', 186500, 364950, 405500, 6, 79, 450, 1665181, 43239.000000, 43239.000000, 0, 306110 ) , 
         ( 1.310000, 'CAD', 1520800, 4590000, 4590000, 4, 569, 100, 384389, 43407.000000, 43407.000000, 0, 322905 ) , 
         ( 0.875900, 'EUR', 1379600, 3000000, 3000000, 1, 1449, 999999, 590077, 43410.000000, 43406.000000, 0, 322800 ) 
         ..[13,915 total tuples, not all displayed]};錯誤去粘貼吧

盡管在我們的示例模型上工作速度很快，但在實際情況下，如果 Sales 表中有數(shù)千萬行數(shù)據(jù)，這兩個查詢可能會非常繁重和緩慢。

用 GROUPBY 表達的相同查詢可能會更高效：

EVALUATE
GROUPBY (
    ADDCOLUMNS (
        Sales,
        "Transaction Size",
            IF (
                Sales[Quantity] > 3,
                "Large",
                "Small"
            )
    ),
    [Transaction Size],
    "Sales Amount",
        SUMX (
            CURRENTGROUP (),
            Sales[Quantity] * Sales[Net Price]
        )
)錯誤去粘貼吧

盡管我們無法使用基本度量“Sales Amount”，但實例化級別較小。僅執(zhí)行了以下 VertiPaq 查詢：

SELECT
    'Sales'[RowNumber],
    'Sales'[Quantity],
    'Sales'[Net Price]
FROM 'Sales';錯誤去粘貼吧

然而，這個數(shù)據(jù)緩存的粒度與 Sales 相同，在大型模型中可能會成為一個嚴重的問題。

要獲得更好的性能，需要結合這兩個函數(shù)并改變我們的觀點。我們首先按 Sales[Quantity] 進行分組，使用 ADDCOLUMNS 和 SUMMARIZE 生成一個非常小的表。該表只包含 10 行。然后我們添加 Transaction Size 列，最后使用 GROUPBY 將這個只有 10 行的表按照 Transaction Size 分組成兩個類別：

EVALUATE
GROUPBY (
    ADDCOLUMNS (
        SUMMARIZE (
            Sales,
            Sales[Quantity]
        ),
        "@Sales", [Sales Amount],
        "Transaction Size",
            IF (
                Sales[Quantity] > 3,
                "Large",
                "Small"
            )
    ),
    [Transaction Size],
    "Sales Amount",
        SUMX (
            CURRENTGROUP (),
            [@Sales]
        )
)誤去粘貼吧

這個DAX 查詢只執(zhí)行了兩個存儲引擎查詢。第一個查詢按 quantity 對 sales amount 進行分組：

WITH
    $Expr0 := ( PFCAST ( 'Sales'[Quantity] AS INT ) * PFCAST ( 'Sales'[Net Price] AS INT ) ) 
SELECT
    'Sales'[Quantity],
    SUM ( @$Expr0 )
FROM 'Sales';錯誤去粘貼吧

第二個 VertiPaq 查詢只檢索了 Sales[Quantity] 的不同值：

SELECT
    'Sales'[Quantity]
FROM 'Sales';錯誤去粘貼吧

大部分計算已經(jīng)下推到了存儲引擎，實例化級別可以忽略不計，即使在大型數(shù)據(jù)庫上，這最后一個 DAX 查詢也非?？焖佟?/span>

了解函數(shù)的細節(jié)、它們的實現(xiàn)方式以及用法是任何研究 DAX 的人都應具備的重要技能。在本文中，我們介紹了 GROUPBY 和 SUMMARIZE 之間的區(qū)別。然而，DAX 還有許多其他值得學習的隱藏細節(jié)。

使用錯誤的函數(shù)可能會產(chǎn)生錯誤的結果或低效的查詢。你對 DAX 的了解越多，你的代碼就會變得更好。

我就此問題與BI佐羅討論，并詢問：作為業(yè)務人員，應該怎樣使用這些函數(shù)的最佳實踐是什么？此前的這篇文章幾乎完美地回答了我的問題。

強烈推薦

PowerBI DAX 表連續(xù)運算及上下文轉(zhuǎn)換失效

通過學習 SQLBI 的專業(yè)技術文章再加上BI佐羅老師的最佳實踐，這已經(jīng)可以解決好實際的各種問題了。

希望大家也可以從這些技術文章和實踐總結中獲益，并直接套用。

Power BI 終極系列課程《BI真經(jīng)》

BI真經(jīng) - 讓數(shù)據(jù)真正成為你的力量

掃碼與精英一起討論 Power BI，驗證碼：data2023

點擊“閱讀原文”進入學習中心

↙