了解 ORDERBY、PARTITIONBY和 MATCHBY 函数

ORDERBY、PARTITIONBY 和 MATCHBY 函数在 DAX 中是特殊函数，只能与 DAX 窗口函数一起使用，包括：INDEX、OFFSET、WINDOW、RANK、ROWNUMBER。

了解 ORDERBY， PARTITIONBY并且 MATCHBY 对于成功使用 Window 函数至关重要。 此处提供的示例使用 OFFSET，但同样适用于其他 Window 函数。

情景

让我们从一个根本不使用 Window 函数的示例开始。 如下所示的表返回每个日历年的总销售额（每色）。 有多种方法可以定义此表，但由于我们想了解 DAX 中发生的情况，我们将使用计算表格。 下面是表格表达式：

BasicTable = 
    SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
    )

你将看到，此计算表表达式使用 SUMMARIZECOLUMNS 根据 DimProduct 表中的 Color 列和 DimDate 表中的 CalendarYear 列计算 FactInternetSales 表中 SalesAmount 列的 SUM。 下面是结果：

现在，让我们想象一下，我们正在努力解决计算每种颜色销售额差异、逐年变化的业务问题。 实际上，我们需要一种方法来查找上一年相同颜色的销售额，并在上下文中减去当年销售额。 例如，对于组合 [Red, 2019] ，我们要查找 [Red, 2018] 的销售额。 有了它后，我们可以从当前销售额中减去它，并返回所需的值。

使用 OFFSET

OFFSET 非常适合与回答上述业务问题所需的先前计算进行典型比较，因为它允许我们执行相对移动。 我们的第一次尝试可能是：

1stAttempt = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation
            ),
            [CurrentYearSales]
        )
    )

此表达式正在发生很多事情。 我们用ADDCOLUMNS扩展了之前的表格，添加了一个名为PreviousColorSales的列。 对于使用 OFFSET 检索到的上一个 Color，该列的内容设置为 CurrentYearSales，即 SUM (FactInternetSales[SalesAmount])。

结果为：

这离我们的目标更近一步，但如果我们仔细观察，它与我们追求的完全不匹配。 例如，对于 [Silver， 2017] ，PreviousColorSales 设置为 [Red， 2020]。

添加 ORDERBY

上述定义等效于：

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color], ASC, [CalendarYear], ASC, [CurrentYearSales], ASC)
            ),
            [CurrentYearSales]
        )
    )

在本例中，对 OFFSET 的调用使用 ORDERBY 按 Color 和 CalendarYear 升序对表进行排序，从而确定被视为返回的上一行。

之所以这两个结果是等效的，是因为ORDERBY自动包含了关系中所有不在PARTITIONBY中的列。 由于 PARTITIONBY 未指定， ORDERBY 因此设置为 Color、CalendarYear 和 CurrentYearSales。 但是，由于关系中的 Color 和 CalendarYear 对是唯一的，因此添加 CurrentYearSales 不会更改结果。 事实上，即使我们只指定 Color in ORDERBY，结果也是相同的，因为 CalendarYear 将自动添加。 这是因为该函数将根据需要向 ORDERBY 添加任意数量的列，以确保每一行都可以由 ORDERBY 和 PARTITIONBY 列唯一标识。

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS(
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

添加 PARTITIONBY

现在，为了几乎获得我们想要的结果，可以使用 PARTITIONBY，如以下计算表表达式所示：

UsingPARTITIONBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]), 
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

请注意，此处指定 ORDERBY 是可选的，因为 ORDERBY 自动包含关系中 PARTITIONBY未指定的所有列。 因此，以下表达式返回相同的结果，因为 ORDERBY 将自动设置为 CalendarYear 和 CurrentYearSales：

UsingPARTITIONBYWithoutORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

这两个表达式都返回了我们想要的结果。

如此表所示，PreviousYearSalesForSameColor 列显示上一年相同颜色的销售额。 对于 [Red，2020]，它返回的是 [Red，2019] 的销售数据，依此类推。 如果没有前一年，例如[Red， 2017]，则不返回任何值。

你可以将 PARTITIONBY 视为一种方法，用于将表划分为执行 OFFSET 计算的各个部分。 在上面的示例中，表格分为任意多个部分，每个颜色各有一种。 然后，在每个部分内计算 OFFSET，按 CalendarYear 排序。

从视觉上看，发生的情况是：

首先，对PARTITIONBY的调用导致表格被分为多个部分，每种颜色各自占有一部分。 这由表图像中的浅蓝色框表示。 接下来， ORDERBY 确保每个部分都按 CalendarYear 排序（由橙色箭头表示）。 最后，在每个已排序部分中，对于每一行，OFFSET 查找其上方的行，并在 PreviousYearSalesForSameColor 列中返回该值。 由于对于每个部分的第一行，同一部分中没有上一行，因此 PreviousYearSalesForSameColor 列的该行的结果为空。

要获得最终结果，只需从调用 OFFSET 返回的相同颜色的上一年销售额中减去 CurrentYearSales。 由于我们对显示同一颜色的上一年销售额不感兴趣，而只关注当前年度的销售额和同比差异。 下面是最终计算表表达式：

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

下面是该表达式的结果：

使用 MATCHBY

你可能已经注意到我们根本没有指定 MATCHBY 。 在这种情况下，不需要这样做。 ORDERBY 和 PARTITIONBY 中的列（就上面示例中指定的而言）足以唯一识别每一行。 由于我们没有指定MATCHBY，所以列ORDERBY和PARTITIONBY被用于唯一标识每一行，从而可以进行比较，以便OFFSET能够提供有意义的结果。 如果列中ORDERBYPARTITIONBY的列无法唯一标识每行，则如果这些额外列允许唯一标识每行，则可以将其他列添加到ORDERBY子句中。 如果不可能，则返回错误。 在此最后一种情况下，指定 MATCHBY 可能有助于解决错误。

如果指定了MATCHBY，那么MATCHBY和PARTITIONBY中的列将用于唯一标识每一行。 如果不可能，则返回错误。 即使 MATCHBY 不需要，也应考虑显式指定 MATCHBY 以避免任何混淆。

继续上述示例，下面是最后一个表达式：

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

如果想要显式说明应如何唯一标识行，可以按以下等效表达式所示指定 MATCHBY ：

FinalResultWithExplicitMATCHBYOnColorAndCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([Color], [CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

由于MATCHBY已指定，因此在MATCHBY和PARTITIONBY中指定的列都用于唯一标识行。 由于在 MATCHBY 和 PARTITIONBY 中都指定了 Color，因此以下表达式等效于上一个表达式：

FinalResultWithExplicitMATCHBYOnCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

由于到目前为止，在我们所看的示例中不需要指定MATCHBY，因此我们来看看一个确实需要MATCHBY的略有不同的示例。 在本例中，我们有一个订单行列表。 每一行都表示订单的订单行。 订单可以有多个订单行，订单行 1 显示在多个订单上。 此外，对于每个订单行，我们都有 ProductKey 和 SalesAmount。 表中相关列的示例如下所示：

请注意，需有 SalesOrderNumber 和 SalesOrderLineNumber 才能唯一识别行。

对于每个订单，我们希望返回同一产品（由 ProductKey 表示）之前的销售金额，并按销售金额降序排列。 以下表达式不起作用，因为 vRelation 中可能存在多个行，因为它被 OFFSET传入：

ThisExpressionFailsBecauseMATCHBYIsMissing = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

此表达式返回错误：“OFFSET''s Relation 参数可能有重复行，这是不允许的。

若要使此表达式正常工作， MATCHBY 必须指定并必须包含唯一定义行的所有列。 MATCHBY 此处是必需的，因为关系 FactInternetSales 不包含任何显式键或唯一列。 但是，列 SalesOrderNumber 和 SalesOrderLineNumber 共同构成 一个组合键，其中它们一起存在于关系中是唯一的，因此可以唯一标识每一行。 仅指定 SalesOrderNumber 或 SalesOrderLineNumber 是不够的，因为两列都包含重复值。 以下表达式可解决问题：

ThisExpressionWorksBecauseOfMATCHBY = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] ),
                    MATCHBY ( FactInternetSales[SalesOrderNumber], 
                                FactInternetSales[SalesOrderLineNumber] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

此表达式确实返回了我们想要的结果：

相关内容

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER