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

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

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

Сценарий

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

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

Это вычисляемое выражение таблицы использует SUMMARIZECOLUMNS для calculateSUM столбца SalesAmount в таблице FactInternetSales в столбце Color из таблицы DimProduct, and столбце 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

Now, предположим, что мы пытаемся решить бизнес-вопрос вычисления разницы в продажах, year-over-year для каждого цвета. Нам действительно нужен способ find продаж для того же цвета в previousyearand, чтобы вычесть это из продаж в текущем year. Например, для сочетания [Red, 2019] мы ищем данные о продажах за [Red, 2018]. После того как у нас будет это, мы можем вычесть его из текущего and продаж, вернуть необходимые value.

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

OFFSET идеально подходит для типичных вычислений типа по сравнению с типами previousи, необходимых для ответа на описанный выше бизнес-вопрос, так как это позволяет нам делать относительные изменения. Наша попытка first может быть:

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

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

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

Причина, по которой эти два результата эквивалентны, заключается в том, что ORDERBY автоматически containsall столбцов из связи, которые не находятся в PARTITIONBY. Так как PARTITIONBY не задано, ORDERBY имеет значение Color, CalendarYear, and CurrentYearSales. Тем не менее, так как пары Color and и CalendarYear в отношении уникальны, добавление CurrentYearSales не изменяет результат. В factevenif мы должны были указать только цвет в ORDERBY, результаты одинаковы, так как CalendarYear будет добавлен автоматически. Это связано с тем, что функция добавит столько столбцов, сколько необходимо для ORDERBY, чтобы убедиться, что каждая строка может быть однозначно идентифицирована столбцами ORDERBYandPARTITIONBY:

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

Now, чтобы почти достичь результата, который мы стремимся получить, мы можем использовать 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 автоматически containsall столбцы из отношения, которые не указаны в PARTITIONBY. Поэтому следующее выражение возвращает те же результаты, так как ORDERBY автоматически задано значением CalendarYear and 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 and CurrentYearSales устанавливается автоматически, нет гарантии порядка их добавления. If CurrentYearSales добавляется до CalendarYear, результирующий порядок не соответствует ожидаемому. Быть явным при указании ORDERBYandPARTITIONBY, чтобы избежать путаницы and непредвиденных результатов.

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

Цвет	Календарный год	ПродажиТекущегоГода	ПродажиЗаПрошлыйГодПоТомуЖеЦвету
"Черный"	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 показывает продажи для previousyear для того же цвета. Для [Red, 2020], он возвращает продажи для [Red, 2019], and т. д. If отсутствует previousyear, например, в случае [Red, 2017] не возвращается value.

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

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

Таблица

Firstвызов PARTITIONBY приводит к тому, что таблица делится на части, по одному для каждого цвета. Это представлено светло-голубыми полями на изображении таблицы. Next, ORDERBY гарантирует, что каждая часть отсортирована по CalendarYear (представлена оранжевыми стрелками). Наконец, в каждой отсортированной части для каждой строки OFFSET находит строку над ней и and возвращает value в столбце PreviousYearSalesForSameColor. Так как для каждой строки first в каждой части нет строки previous в той же части, результат в этой строке для столбца PreviousYearSalesForSameColor является пустым.

Чтобы добиться окончательного результата, мы просто должны вычитать CurrentYearSales из previousyear продаж для того же цвета, возвращаемого вызовом OFFSET. Так как мы not заинтересованы в отображении previousyear продаж для того же цвета, но только в текущих year продажах, andyear более year разницы. Ниже приведено окончательное вычисляемое табличное выражение:

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

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

Цвет	Календарный год	ПродажиТекущегоГода	Годовой объем продаж для одинакового цвета
"Черный"	2017	393885	393885
"Черный"	2018	1818835	1424950
"Черный"	2019	3981638	2162803
"Черный"	2020	2644054	-1337584
"Синий"	2019	994448	994448
"Синий"	2020	1284648	290200
Мульти	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
Серебро	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 на all. В этом случае это не обязательно. Столбцы в ORDERBYandPARTITIONBY (насколько они были указаны в приведённых выше примерах) достаточны для уникальной идентификации каждой строки. Так как мы не указали MATCHBY, столбцы, указанные в ORDERBYandPARTITIONBY, используются для уникальной идентификации каждой строки, чтобы их можно было сравнить, чтобы позволить OFFSET дать значимый результат. If столбцы в ORDERBYandPARTITIONBY не могут однозначно идентифицировать каждую строку, в предложение ORDERBY можно добавить дополнительные столбцы, if эти дополнительные столбцы позволяют определить каждую строку уникальным образом. If если not возможно, возвращается error. В этом last случае указание MATCHBY может помочь устранить error.

If MATCHBY указывается, столбцы в MATCHBYandPARTITIONBY используются для уникальной идентификации каждой строки. If это возможно для not, возвращается error. Even if MATCHBY не требуется, рекомендуется явно указывать MATCHBY, чтобы избежать путаницы.

Продолжая приведенные выше примеры, ниже приведено выражение last:

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

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

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. В этом случае у нас есть список строк заказа. Каждая строка представляет собой позицию в заказе. Заказ может содержать несколько строк заказа; строка заказа and-1 встречается во многих заказах. Кроме того, для каждой строки заказа у нас есть ProductKey and и сумма продаж (SalesAmount). Значение sample в соответствующих столбцах таблицы выглядит следующим образом:

НомерЗаказа	Номер строки заказа	Ключ продукта	СуммаПродаж
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 and и SalesOrderLineNumber необходимы для уникальной идентификации строк.

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

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

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

Чтобы сделать это выражение работой, MATCHBY необходимо указать and должны содержать all столбцы, которые однозначно define строке. MATCHBY здесь необходим, потому что отношение FactInternetSales не содержит ни явных ключей, ни уникальных столбцов or. Однако столбцы SalesOrderNumber and и SalesOrderLineNumber вместе образуют составной ключ, что обеспечивает их уникальность в связи and и таким образом позволяет однозначно идентифицировать каждую строку. Просто указать SalesOrderNumber or SalesOrderLineNumber недостаточно, так как оба столбца содержат повторяющиеся values. Следующее выражение решает проблему:

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

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

Номер заказа на продажу	НомерСтрокиЗаказа	Ключ продукта	Сумма продаж	Previous объем продаж
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

содержимое Related

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER