原创: 2023-08-24 17:31
DAX 提供了丰富的函数集,其中一些函数的功能是重叠的。在众多函数中,有两个函数可以进行分组:SUMMARIZE 和 GROUPBY。但并非只有这两个:SUMMARIZECOLUMNS 和 GROUPCROSSAPPLY 也执行类似的操作。不过,本文讨论的是 SUMMARIZE 和 GROUPBY,因为其他函数还有更多的功能,因此进行比较并不公平。
简而言之:GROUPBY应用于按局部列进行分组,即由DAX函数动态创建的列。SUMMARIZE应用于按模型和查询列进行分组。要注意的是,这两个函数都支持这两种情况:两个函数都可以按模型和局部列进行分组。然而,使用错误的函数会导致性能严重下降。
现在让我们详细说明这些函数是如何工作的。
SUMMARIZE介绍
SUMMARIZE执行两个操作:按本地列分组和添加新的本地列。我们已经在一篇很长很有技术含量的文章中介绍过SUMMARIZE: SUMMARIZE的所有秘密(https://www.sqlbi.com/articles/all-the-secrets-of-summarize/)。在那篇文章中,我们描述了SUMMARIZE的行为,以及为什么不应该使用它来计算新的本地列。具体来说,SUMMARIZE实现了聚类,这是一种分组技术,尽管非常强大,但可能导致意想不到的结果和较差的性能。
但是,为了进行比较,我们将使用SUMMARIZE来计算新列,以描述其特殊行为。
当与简单示例一起使用时,SUMMARIZE表现良好,将分组操作下推到存储引擎。例如,下面的代码工作得很好,产生预期的存储引擎查询:
EVALUATE
SUMMARIZE (
Sales,
'Product'[Brand],
"Sales Amount", [Sales Amount]
)
SUMMARIZE 扫描销售额,按产品[品牌]分组,并按品牌生成销售额。存储引擎查询如下:
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];
然而,一旦执行的度量代码变得复杂一些,这种简单的行为就很容易丢失。事实上,正如我们所提到的,SUMMARIZE 是通过一种名为聚类的特殊技术来进行计算的。请看下面的代码:
EVALUATE
SUMMARIZE (
Sales,
'Product'[Brand],
"Sales Amount", [Sales Amount],
"Sales All Brands",
CALCULATE (
[Sales Amount],
REMOVEFILTERS ( Product[Brand] )
)
)
由于 CALCULATE 删除了筛选上下文中唯一的筛选项,因此可以合理地认为 "所有品牌销售额 "会产生销售总额。但是,这种推测没有考虑到聚类。由于存在聚类,SUMMARIZE 设置的筛选器会影响扩展销售表的所有列,从而导致这种奇怪的结果。
如您所见,"所有品牌销售额 "重复了与 "销售金额 "相同的值。不同的数据分布或存在重复行可能会导致不同的值。此外,由于聚类的原因,一旦要聚合的数据是非三维数据,SUMMARIZE 就需要将整个表具体化。为了计算所有品牌的销售额,这是正在执行的 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 并非查询的一部分,因此数据缓存的粒度并不完全是 Sales 的粒度,GROUPBY 也是如此。不过,由于表中的所有列都用作分组列,因此其大小通常非常重要。
同样的查询,使用 SUMMARIZE 和 ADDCOLUMNS 会产生预期结果:
EVALUATE
ADDCOLUMNS (
SUMMARIZE (
Sales,
'Product'[Brand]
),
"Sales Amount", [Sales Amount],
"Sales All Brands",
CALCULATE (
[Sales Amount],
ALL ( Product[Brand] )
)
)
这里是结果:
得益于聚类功能,SUMMARIZE 也可以按本地列进行分组。尽管按本地列分组,但下面的查询仍能正常运行:
EVALUATE
SUMMARIZE (
ADDCOLUMNS (
Sales,
"Transaction Size",
IF (
Sales[Quantity] > 3,
"Large",
"Small"
)
),
[Transaction Size],
"Sales Amount", [Sales Amount]
)
结果显示了按交易规模分组的销售额。
不过,请记住,尽管从语法和语义的角度来看查询是有效的,但其结果却是通过聚类计算出来的。聚类在多种情况下都会产生令人惊讶的结果,而且这种功能带来的问题要多于解决方法。此外,在这种情况下,计算需要将整个销售表具体化。
GROUPBY函数介绍
GROUPBY 按列对表进行分组。列可以是模型列或本地列。不过,它的行为与 SUMMARIZE 非常不同。GROUPBY 甚至不会将计算推送到存储引擎:整个计算都是在将表具体化后在公式引擎中进行的。GROUPBY 还可以在结果中添加新列。不过,由于 GROUPBY 的行为方式,新列需要使用 CURRENTGROUP 特殊函数作为被分组表中列的简单聚合来计算。
举例来说,让我们看看下面的代码:
EVALUATE
GROUPBY (
Sales,
'Product'[Brand],
"Sales Amount",
SUMX (
CURRENTGROUP (),
Sales[Quantity] * Sales[Net Price]
)
)
GROUPBY 扫描销售表并按产品[品牌]分组。为了进行分组,DAX 在数据缓存中将 Sales 所需的列具体化,然后由公式引擎进行处理。实际上,查询执行的就是这段代码:
SELECT
'Product'[Brand],
'Sales'[RowNumber],
'Sales'[Quantity],
'Sales'[Net Price]
FROM 'Sales'
LEFT OUTER JOIN 'Product'
ON 'Sales'[ProductKey]='Product'[ProductKey];
在销售中,DAX 会检索销售[数量]、销售[净价]和产品[品牌]。Sales[RowNumber] 的存在保证了检索到所有行,否则,VertiPaq 本身将执行分组操作。
结果是一个行数与销售额相同的表格,因此可能非常大。公式引擎会扫描该表,根据产品[品牌]将其分成若干组,然后计算每个组的销售额[数量]乘以销售额[净价]的总和。
GROUPBY 有一个很大的局限性,那就是在迭代 CURRENTGROUP 时使用的表达式不能涉及上下文转换。这一限制使得我们无法使用现有的度量值作为迭代的一部分。您可能已经注意到,我们不得不重写示例中销售金额的代码。
尽管 GROUPBY 看起来很慢,但它是唯一一个可以对无行列的表执行分组和计算的 DAX 函数。例如,下面的查询按本地表的一列对表进行分组,而 GROUPBY 是唯一能执行该操作的函数:
EVALUATE
VAR TableToGroup =
SELECTCOLUMNS (
{
( "A", 1 ),
( "A", 2 ),
( "B", 3 ),
( "B", 4 )
},
"Group", [Value1],
"Value", [Value2]
)
RETURN
GROUPBY (
TableToGroup,
[Group],
"Result",
SUMX (
CURRENTGROUP (),
[Value]
)
)
GROUPBY 是一个合适的函数,适用于使用其他 DAX 函数生成一个小表格,然后需要按其中一列执行分组,逐行进行简单的聚合。
选择正确的函数
正如您所看到的,当需要按模型中的列分组时,SUMMARIZE 可以很好地发挥作用。尽管它也可以按本地列分组,但它使用了聚类,其结果大多出人意料。GROUPBY 不使用聚类。不过,它也有一个很大的局限性:它总是将需要分组的表具体化。因此,GROUPBY 并不是按模型列分组的最佳选择,而 ADDCOLUMNS/SUMMARIZE 则能产生更高效的代码。
不过,当需要按本地列对小型临时表进行分组时,GROUPBY 是最好的函数,因为它可以在不依赖聚类的情况下完成工作。
明智的 DAX 开发人员会选择合适的函数,通常会将 SUMMARIZE、ADDCOLUMNS 和 GROUPBY 混合使用,以获得最佳性能和正确结果。让我们通过一个例子来详细说明这一点。之前,我们向您展示了这段代码:
EVALUATE
SUMMARIZE (
ADDCOLUMNS (
Sales,
"Transaction Size",
IF (
Sales[Quantity] > 3,
"Large",
"Small"
)
),
[Transaction Size],
"Sales Amount", [Sales Amount]
)
该查询使用 SUMMARIZE,因此进行了聚类。它执行两个 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';
第二个存储引擎查询使用第一个查询的结果,对销售数据进行大规模筛选:
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]};
尽管这两个查询在我们的示例模型上运行速度非常快,但在有数千万行数据的实际示例中可能会非常繁重和缓慢。
使用 GROUPBY 表示的相同查询可能会更高效:
EVALUATE
GROUPBY (
ADDCOLUMNS (
Sales,
"Transaction Size",
IF (
Sales[Quantity] > 3,
"Large",
"Small"
)
),
[Transaction Size],
"Sales Amount",
SUMX (
CURRENTGROUP (),
Sales[Quantity] * Sales[Net Price]
)
)
尽管我们不能使用基本衡量标准 "销售金额",但具体化级别却变小了。正在执行的唯一 VertiPaq 查询如下:
SELECT
'Sales'[RowNumber],
'Sales'[Quantity],
'Sales'[Net Price]
FROM 'Sales';
但是,这种数据缓存的粒度与 Sales 相同,对于大型模型来说,这将是一个严重的问题。
要想获得更好的性能,就必须将这两个功能结合起来,并改变我们的视角。我们首先按销售额[数量]分组,使用 ADDCOLUMNS 和 SUMMARIZE 生成一个很小的表。该表仅包含 10 行。然后,我们添加 "交易量 "列,最后,我们使用GROUPBY将表格中的10行数据分组到两个“交易规模”集群中:
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 查询只执行两个存储引擎查询。第一个查询按数量对销售额进行分组:
WITH
$Expr0 := ( PFCAST ( 'Sales'[Quantity] AS INT ) * PFCAST ( 'Sales'[Net Price] AS INT ) )
SELECT
'Sales'[Quantity],
SUM ( @$Expr0 )
FROM 'Sales';
第二个 VertiPaq 查询只是检索销售[数量]的不同值:
SELECT
'Sales'[Quantity]
FROM 'Sales';
大部分计算都已下放到存储引擎中;具体化程度可以忽略不计,即使在大型数据库中,最后这个 DAX 查询也会非常快。
结论
对于任何认真学习 DAX 的人来说,了解函数的细节、实现方式和预期用法都是一项重要技能。在本文中,我们介绍了 GROUPBY 和 SUMMARIZE 之间的区别。然而,DAX 还有许多隐藏的细节值得了解。
使用错误的函数可能会产生意想不到的结果或查询效率低下。对 DAX 了解得越多,你的代码就会变得越好。
如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”。
长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯,欢迎小伙伴儿们转发分享~