Поделиться через

Общие сведения о функциях ORDERBY, PARTITIONBYи MATCHBY

Функции ORDERBY, PARTITIONBY и MATCHBY в DAX являются специальными функциями, которые можно использовать только вместе с функциями Window: DAX, INDEX, OFFSET, WINDOW, RANK.

Понимание ORDERBY, PARTITIONBY и MATCHBY имеет критически важное значение для успешного использования функций Window. Примеры, приведенные здесь, используют OFFSET, но также применимы к другим оконным функциям.

Сценарий

Начнем с примера, который не использует функции Окна вообще. Ниже показана таблица, которая показывает общий объем продаж по цветам за календарный год. Существует несколько способов определения этой таблицы, но так как мы заинтересованы в понимании того, что происходит в DAX, мы будем использовать вычисляемую таблицу. Вот табличное выражение:

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

Вычисляемое табличное выражение использует SUMMARIZECOLUMNS, чтобы вычислить SUM столбца SalesAmount в таблице FactInternetSales, по столбцам Color из таблицы DimProduct и CalendarYear из таблицы DimDate. Вот результат:

Цвет Календарный год ПродажиТекущегоГода
"Черный" 2017 393885
"Черный" 2018 1818835
"Черный" 2019 3981638
"Черный" 2020 2644054
"Синий" 2019 994448
"Синий" 2020 1284648
Мульти 2019 48622
Мульти 2020 57849
"NA" 2019 207822
"NA" 2020 227295
"Красный" 2017 2961198
"Красный" 2018 3686935
"Красный" 2019 900175
"Красный" 2020 176022
Серебро 2017 326399
Серебро 2018 750026
Серебро 2019 2165176
Серебро 2020 1871788
Белый 2019 2517
Белый 2020 2589
"Желтый" 2018 163071
"Желтый" 2019 2072083
"Желтый" 2020 2621602

Теперь давайте предположим, что мы пытаемся решить бизнес-вопрос вычисления разницы в продажах, год за год для каждого цвета. На самом деле, нам нужен способ найти продажи для того же цвета в предыдущем году и вычитать их из продаж в текущем году, в контексте. Например, для сочетания [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. Содержимое этого столбца имеет значение CurrentYearSales, то есть SUM(FactInternetSales[SalesAmount]), для предыдущего цвета (извлекаемого с помощью OFFSET).

Результатом является:

Цвет Календарный год ПродажиТекущегоГода ПредыдущиеColorSales
"Черный" 2017 393885
"Черный" 2018 1818835 393885
"Черный" 2019 3981638 1818835
"Черный" 2020 2644054 3981638
"Синий" 2019 994448 2644054
"Синий" 2020 1284648 994448
Мульти 2019 48622 1284648
Мульти 2020 57849 48622
"NA" 2019 207822 57849
"NA" 2020 227295 207822
"Красный" 2017 2961198 227295
"Красный" 2018 3686935 2961198
"Красный" 2019 900175 3686935
"Красный" 2020 176022 900175
Серебро 2017 326399 176022
Серебро 2018 750026 326399
Серебро 2019 2165176 750026
Серебро 2020 1871788 2165176
Белый 2019 2517 1871788
Белый 2020 2589 2517
"Желтый" 2018 163071 2589
"Желтый" 2019 2072083 163071
"Желтый" 2020 2621602 2072083

Это один шаг ближе к нашей цели, но если мы посмотрим внимательно, это не совсем соответствует тому, чего мы добиваемся. Например, для [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 для упорядочивания таблицы по Цвету и Календарному году в порядке возрастания, что определяет, какая строка считается предыдущей, возвращенной.

Причина, по которой эти два результата эквивалентны, заключается в том, что ORDERBY автоматически содержит все столбцы из связи, которые не находятся PARTITIONBY. Поскольку PARTITIONBY не указано, для параметра ORDERBY установлены значения Color, CalendarYear и CurrentYearSales. Однако, так как пары Color и CalendarYear в связи уникальны, добавление CurrentYearSales не изменяет результат. На самом деле, даже если бы мы только указали цвет в 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]
        )
    )

Заметка

Хотя ORDERBY автоматически устанавливается как CalendarYear и CurrentYearSales, нет гарантии относительно порядка их добавления. Если CurrentYearSales добавляется перед CalendarYear, результирующий заказ не соответствует ожидаемому. Указывайте явно ORDERBY и PARTITIONBY, чтобы избежать путаницы и неожиданных результатов.

Оба выражения возвращают результат, которого мы добиваемся.

Цвет Календарный год ПродажиТекущегоГода ПродажиЗаПрошлыйГодПоТомуЖеЦвету
"Черный" 2017 393885
"Черный" 2018 1818835 393885
"Черный" 2019 3981638 1818835
"Черный" 2020 2644054 3981638
"Синий" 2019 994448
"Синий" 2020 1284648 994448
Мульти 2019 48622
Мульти 2020 57849 48622
"NA" 2019 207822
"NA" 2020 227295 207822
"Красный" 2017 2961198
"Красный" 2018 3686935 2961198
"Красный" 2019 900175 3686935
"Красный" 2020 176022 900175
Серебро 2017 326399
Серебро 2018 750026 326399
Серебро 2019 2165176 750026
Серебро 2020 1871788 2165176
Белый 2019 2517
Белый 2020 2589 2517
"Желтый" 2018 163071
"Желтый" 2019 2072083 163071
"Желтый" 2020 2621602 2072083

Как видно в этой таблице, столбец PreviousYearSalesForSameColor показывает продажи за предыдущий год для того же цвета. Для [Red, 2020] это возвращает продажи для [Red, 2019], и так далее. Если предыдущий год отсутствует, например в случае [Red, 2017], значение не возвращается.

Можно рассматривать PARTITIONBY как способ разделить таблицу на части для выполнения вычисления OFFSET. В приведенном выше примере таблица делится на столько частей, сколько есть цвета, по одному для каждого цвета. Затем в каждой части вычисляется OFFSET и сортируется по CalendarYear.

Визуально это происходит:

Таблица, показывающая OFFSET по календарным годам

Во-первых, вызов PARTITIONBY приводит к тому, что таблица делится на части, каждая из которых соответствует определённому цвету. Это представлено светло-голубыми полями на изображении таблицы. Затем ORDERBY следит за тем, чтобы каждая часть была отсортирована по CalendarYear (что обозначено оранжевыми стрелками). Наконец, в каждой отсортированной части, для каждой строки OFFSET находит строку, расположенную выше, и возвращает это значение в столбце с названием PreviousYearSalesForSameColor. Так как для каждой первой строки в каждой части данных нет предыдущей строки в той же части, результат в этой строке для столбца PreviousYearSalesForSameColor будет пустым.

Чтобы добиться окончательного результата, мы просто должны вычесть CurrentYearSales из продаж предыдущего года за тот же цвет, возвращенный вызовом OFFSET. Поскольку нас интересуют не продажи за предыдущий год для того же цвета, а продажи за текущий год и их изменения по сравнению с прошлым годом. Ниже приведено окончательное вычисляемое табличное выражение:

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]
        )
    )

И вот результат этого выражения:

Цвет Календарный год ПродажиТекущегоГода Годовой объем продаж для одинакового цвета
"Черный" 2017 393885 393885
"Черный" 2018 1818835 1424950
"Черный" 2019 3981638 2162803
"Черный" 2020 2644054 -1337584
"Синий" 2019 994448 994448
"Синий" 2020 1284648 290200
Мульти 2019 48622 48622
Мульти 2020 57849 9227
"NA" 2019 207822 207822
"NA" 2020 227295 19473
"Красный" 2017 2961198 2961198
"Красный" 2018 3686935 725737
"Красный" 2019 900175 -2786760
"Красный" 2020 176022 -724153
Серебро 2017 326399 326399
Серебро 2018 750026 423627
Серебро 2019 2165176 1415150
Серебро 2020 1871788 -293388
Белый 2019 2517 2517
Белый 2020 2589 72
"Желтый" 2018 163071 163071
"Желтый" 2019 2072083 1909012
"Желтый" 2020 2621602 549519

Использование MATCHBY

Возможно, вы заметили, что мы вообще не указали MATCHBY . В этом случае это не обязательно. Столбцы в ORDERBY и PARTITIONBY (насколько они были указаны в приведенных выше примерах) достаточны для уникальной идентификации каждой строки. Так как мы не указали MATCHBY, столбцы, указанные в ORDERBY и PARTITIONBY, используются для уникальной идентификации каждой строки, чтобы их можно было сравнить и OFFSET дало значимый результат. Если столбцы в ORDERBY и PARTITIONBY не могут однозначно идентифицировать каждую строку, дополнительные столбцы можно добавить в 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, следующее выражение эквивалентно предыдущему выражению:

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. Пример соответствующих столбцов в таблице выглядит следующим образом:

НомерЗаказа Номер строки заказа Ключ продукта СуммаПродаж
SO51900 1 528 4.99
SO51948 1 528 5.99
SO52043 1 528 4.99
SO52045 1 528 4.99
SO52094 1 528 4.99
SO52175 1 528 4.99
SO52190 1 528 4.99
SO52232 1 528 4.99
SO52234 1 528 4.99
SO52234 2 529 3.99

Обратите внимание, что SalesOrderNumber и SalesOrderLineNumber требуются для уникальной идентификации строк.

Для каждого заказа мы хотим вернуть предыдущий объем продаж одного и того же продукта (представленного ProductKey), отсортировав заказы по SalesAmount в порядке убывания. Следующее выражение не будет работать, поскольку в vRelation может быть несколько строк, которые передаются в OFFSET.

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

Это выражение возвращает ошибку: "OFFSETПараметр отношения может иметь повторяющиеся строки, которые не разрешены".

Чтобы это выражение работало, необходимо, чтобы 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]
            )
    )

И это выражение действительно возвращает те результаты, которые мы ищем.

НомерЗаказа Номер строки заказа Ключ продукта СуммаПродаж Предыдущая сумма продаж
SO51900 1 528 5.99
SO51948 1 528 4.99 5.99
SO52043 1 528 4.99 4.99
SO52045 1 528 4.99 4.99
SO52094 1 528 4.99 4.99
SO52175 1 528 4.99 4.99
SO52190 1 528 4.99 4.99
SO52232 1 528 4.99 4.99
SO52234 1 528 4.99 4.99
SO52234 2 529 3.99

Связанный контент

  • Last updated on