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

Функции ORDERBY, PARTITIONBY и MATCHBY в DAX — это специальные функции, которые можно использовать только вместе с функциями ОКНА DAX: INDEX, OFFSET, WINDOW, RANK, ROWNU МБ ER.

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

Сценарий

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

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

Вы увидите, что это вычисляемое выражение таблицы использует СУММECOLUMNS для вычисления суммы столбца SalesAmount в таблице FactInternetSales, по столбцу Color из таблицы DimProduct и столбцу CalendarYear из таблицы DimDate. Вот результат:

Color	CalendarYear	CurrentYearSales
"Черный"	2017	393885
"Черный"	2018	1818835
"Черный"	2019	3981638
"Черный"	2020	2644054
"Blue"	2019	994448
"Blue"	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Красный"	2017	2961198
"Красный"	2018	3686935
"Красный"	2019	900175
"Красный"	2020	176022
"Silver"	2017	326399
"Silver"	2018	750026
"Silver"	2019	2165176
"Silver"	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).

Результат:

Color	CalendarYear	CurrentYearSales	ПредыдущиеColorSales
"Черный"	2017	393885
"Черный"	2018	1818835	393885
"Черный"	2019	3981638	1818835
"Черный"	2020	2644054	3981638
"Blue"	2019	994448	2644054
"Blue"	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Красный"	2017	2961198	227295
"Красный"	2018	3686935	2961198
"Красный"	2019	900175	3686935
"Красный"	2020	176022	900175
"Silver"	2017	326399	176022
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	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 для упорядочивания таблицы по цвету и calendarYear в порядке возрастания, который определяет, что считается возвращенной предыдущей строкой.

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

Оба выражения возвращают результат, который мы после:

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Черный"	2017	393885
"Черный"	2018	1818835	393885
"Черный"	2019	3981638	1818835
"Черный"	2020	2644054	3981638
"Blue"	2019	994448
"Blue"	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Красный"	2017	2961198
"Красный"	2018	3686935	2961198
"Красный"	2019	900175	3686935
"Красный"	2020	176022	900175
"Silver"	2017	326399
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	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. В приведенном выше примере таблица делится на столько частей, сколько есть цвета, по одному для каждого цвета. Затем, в каждой части, смещение вычисляется по CalendarYear.

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

Во-первых, вызов 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]
        )
    )

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

Color	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Черный"	2017	393885	393885
"Черный"	2018	1818835	1424950
"Черный"	2019	3981638	2162803
"Черный"	2020	2644054	-1337584
"Blue"	2019	994448	994448
"Blue"	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Красный"	2017	2961198	2961198
"Красный"	2018	3686935	725737
"Красный"	2019	900175	-2786760
"Красный"	2020	176022	-724153
"Silver"	2017	326399	326399
"Silver"	2018	750026	423627
"Silver"	2019	2165176	1415150
"Silver"	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. Пример соответствующих столбцов в таблице выглядит следующим образом:

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

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

SalesOrderNumber	SalesOrderLineNumber	ProductKey	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

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

ORDERBY
PARTITIONBY
MATCHBY
INDEX
OFFSET
WINDOW
RANK
ROWNU МБ ER