Principy funkcí ORDERBY, PARTITIONBYa MATCHBY

Funkce ORDERBY, PARTITIONBYa MATCHBY v DAX jsou speciální funkce, které lze používat pouze společně s funkcemi okna DAX: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Pochopení ORDERBY, PARTITIONBYa MATCHBY je důležité pro úspěšné použití funkcí Okna. Zde uvedené příklady používají OFFSET, ale podobně platí i pro ostatní funkce Okna.

Scénář

Začněme příkladem, který nepoužívá funkce Okna vůbec. Níže je uvedena tabulka, která vrací celkové prodeje podle barvy za kalendářní rok. Existuje několik způsobů, jak tuto tabulku definovat, ale protože nás zajímá, co se stane v DAX, použijeme počítanou tabulku. Tady je výraz tabulky:

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

Tento výraz počítané tabulky používá SUMMARIZECOLUMNS k výpočtu SUM sloupce SalesAmount v tabulce FactInternetSales podle sloupce Color z tabulky DimProduct a sloupce CalendarYear z tabulky DimDate. Tady je výsledek:

Barva	Kalendářní rok	Prodeje za současný rok
"Černá"	2017	393885
"Černá"	2018	1818835
"Černá"	2019	3981638
"Černá"	2020	2644054
"Modrá"	2019	994448
"Modrá"	2020	1284648
Více	2019	48622
Více	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Červená"	2017	2961198
"Červená"	2018	3686935
"Červená"	2019	900175
"Červená"	2020	176022
"Silver"	2017	326399
"Silver"	2018	750026
"Silver"	2019	2165176
"Silver"	2020	1871788
Bílá	2019	2517
Bílá	2020	2589
"Žlutá"	2018	163071
"Žlutá"	2019	2072083
"Žlutá"	2020	2621602

Teď si představme, že se snažíme vyřešit obchodní otázku výpočtu rozdílu v prodejích v jednotlivých barvách za rok. V podstatě potřebujeme způsob, jak najít prodeje pro stejnou barvu v předchozím roce a odečíst je od prodeje v aktuálním roce. Například pro kombinaci [Red, 2019] hledáme prodej pro [Red, 2018]. Jakmile to máme, můžeme ho odečíst od aktuálního prodeje a vrátit požadovanou hodnotu.

Použití OFFSET

OFFSET je ideální pro typické porovnání s předchozími typy výpočtů potřebnými k zodpovězení výše popsané obchodní otázky, protože nám umožňuje provádět relativní pohyb. Náš první pokus může být:

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

Tento výraz se hodně používá. Pro rozšíření předchozí tabulky o sloupec s názvem PreviousColorSales jsme použili ADDCOLUMNS. Obsah tohoto sloupce je nastavený na CurrentYearSales, což je SUM(FactInternetSales[SalesAmount]) pro předchozí barvu (načteno pomocí OFFSET).

Výsledkem je:

Barva	Kalendářní rok	Prodeje za současný rok	Prodej předchozích barev
"Černá"	2017	393885
"Černá"	2018	1818835	393885
"Černá"	2019	3981638	1818835
"Černá"	2020	2644054	3981638
"Modrá"	2019	994448	2644054
"Modrá"	2020	1284648	994448
Více	2019	48622	1284648
Více	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Červená"	2017	2961198	227295
"Červená"	2018	3686935	2961198
"Červená"	2019	900175	3686935
"Červená"	2020	176022	900175
"Silver"	2017	326399	176022
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
Bílá	2019	2517	1871788
Bílá	2020	2589	2517
"Žlutá"	2018	163071	2589
"Žlutá"	2019	2072083	163071
"Žlutá"	2020	2621602	2072083

Toto je jeden krok blíž k našemu cíli, ale pokud se podíváme pozorně, neshoduje se přesně s tím, o co usilujeme. Například pro [Silver, 2017] PreviousColorSales je nastaven na [Red, 2020].

Přidání ORDERBY

Výše uvedená definice odpovídá:

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

V tomto případě volání OFFSET používá ORDERBY k seřazení tabulky podle barvy a kalendáře ve vzestupném pořadí, které určuje, co se považuje za předchozí vrácený řádek.

Důvodem, proč jsou tyto dva výsledky ekvivalentní, je to, že ORDERBY automaticky obsahuje všechny sloupce z relace, které nejsou v PARTITIONBY. Vzhledem k tomu, že PARTITIONBY nebylo zadáno, ORDERBY je nastaveno na Barva, Kalendářní rok a Prodej za aktuální rok. Vzhledem k tomu, že páry Color a CalendarYear v relaci jsou jedinečné, přidání CurrentYearSales nezmění výsledek. Ve skutečnosti, i když bychom v ORDERBYzadali pouze barvu , výsledky jsou stejné, protože CalendarYear by se automaticky přidal. Je to proto, že funkce přidá tolik sloupců, kolik je potřeba k ORDERBY, aby každý řádek mohl být jednoznačně identifikován ORDERBY a PARTITIONBY sloupci:

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

Přidání PARTITIONBY

Teď téměř dosáhneme výsledku, po kterém jdeme, můžeme použít PARTITIONBY, jak je znázorněno v následujícím počítaném výrazu tabulky:

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

Všimněte si, že zadání ORDERBY je zde volitelné, protože ORDERBY automaticky obsahuje všechny sloupce z relace, které nejsou zadány v PARTITIONBY. Následující výraz tedy vrátí stejné výsledky, protože ORDERBY je nastavena na CalendarYear a CurrentYearSales automaticky:

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

Poznámka

I když je ORDERBY nastavena na CalendarYear a CurrentYearSales automaticky, neposkytuje se žádná záruka, v jakém pořadí budou přidány. Pokud se před CalendarYear přidá CurrentYearSales, výsledné pořadí není v souladu s očekáváním. Být explicitní při zadávání ORDERBY a PARTITIONBY, aby nedocházelo k nejasnostem a neočekávaným výsledkům.

Oba výrazy vrátí výsledek, který hledáme.

Barva	Kalendářní rok	Prodeje za současný rok	ProdejZaStejnouBarvuZMinuléhoRoku
"Černá"	2017	393885
"Černá"	2018	1818835	393885
"Černá"	2019	3981638	1818835
"Černá"	2020	2644054	3981638
"Modrá"	2019	994448
"Modrá"	2020	1284648	994448
Více	2019	48622
Více	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Červená"	2017	2961198
"Červená"	2018	3686935	2961198
"Červená"	2019	900175	3686935
"Červená"	2020	176022	900175
"Silver"	2017	326399
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
Bílá	2019	2517
Bílá	2020	2589	2517
"Žlutá"	2018	163071
"Žlutá"	2019	2072083	163071
"Žlutá"	2020	2621602	2072083

Jak vidíte v této tabulce, sloupec PreviousYearSalesForSameColor zobrazuje prodeje za předchozí rok pro stejnou barvu. Pro [Red, 2020] vrátí prodeje z roku [Red, 2019] a tak dále. Pokud neexistuje žádný předchozí rok, například v případě [Červená, 2017], nebude vrácena žádná hodnota.

PARTITIONBY si můžete představit jako způsob rozdělení tabulky na části, ve kterých se má provést výpočet OFFSET. V předchozím příkladu je tabulka rozdělena na tolik částí, kolik je barev, jedna pro každou barvu. Pak se v rámci každé části vypočítá OFFSET a seřadí podle kalendářního roku.

Pohledem na situaci je vidět toto:

tabulka

Nejprve volání PARTITIONBY vede k rozdělení tabulky do částí, jedna pro každou barvu. Toto je reprezentováno světle modrými poli na obrázku tabulky. Následně ORDERBY zajistí, aby každá část byla seřazena podle kalendářního roku, jak ukazují oranžové šipky. Nakonec v každé seřazené části pro každý řádek OFFSET najde řádek nad ním a vrátí danou hodnotu ve sloupci PreviousYearSalesForSameColor. Vzhledem k tomu, že pro každý první řádek v každé části není ve stejné části žádný předchozí řádek, výsledek tohoto řádku pro sloupec PreviousYearSalesForSameColor je prázdný.

Abychom dosáhli konečného výsledku, jednoduše musíme odečíst CurrentYearSales z prodeje za předchozí rok pro stejnou barvu vrácenou voláním na OFFSET. Vzhledem k tomu, že nás nezajímá zobrazení prodejů za předchozí rok pro stejnou barvu, ale pouze prodeje za aktuální rok a meziroční rozdíl. Tady je konečný počítaný výraz tabulky:

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

A tady je výsledek tohoto výrazu:

Barva	Kalendářní rok	Prodeje za současný rok	YoYSalesForSameColor
"Černá"	2017	393885	393885
"Černá"	2018	1818835	1424950
"Černá"	2019	3981638	2162803
"Černá"	2020	2644054	-1337584
"Modrá"	2019	994448	994448
"Modrá"	2020	1284648	290200
Více	2019	48622	48622
Více	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Červená"	2017	2961198	2961198
"Červená"	2018	3686935	725737
"Červená"	2019	900175	-2786760
"Červená"	2020	176022	-724153
"Silver"	2017	326399	326399
"Silver"	2018	750026	423627
"Silver"	2019	2165176	1415150
"Silver"	2020	1871788	-293388
Bílá	2019	2517	2517
Bílá	2020	2589	72
"Žlutá"	2018	163071	163071
"Žlutá"	2019	2072083	1909012
"Žlutá"	2020	2621602	549519

Použití MATCHBY

Možná jste si všimli, že jsme vůbec neurčili MATCHBY. V tomto případě není nutné. Sloupce v ORDERBY a PARTITIONBY (pokud byly uvedeny v příkladech výše) jsou dostatečné k jednoznačné identifikaci jednotlivých řádků. Vzhledem k tomu, že jsme nezadali MATCHBY, sloupce zadané v ORDERBY a PARTITIONBY slouží k jedinečné identifikaci jednotlivých řádků, aby bylo možné je porovnat a umožnit tak OFFSET smysluplný výsledek. Pokud sloupce v ORDERBY a PARTITIONBY nemohou jednoznačně identifikovat každý řádek, je možné do klauzule ORDERBY přidat další sloupce, pokud tyto nadbytečné sloupce umožňují jedinečně identifikovat každý řádek. Pokud to není možné, vrátí se chyba. V tomto posledním případě může zadání MATCHBY pomoct s řešením chyby.

Pokud je zadán MATCHBY, sloupce v MATCHBY a PARTITIONBY slouží k jedinečné identifikaci jednotlivých řádků. Pokud to není možné, vrátí se chyba. I když MATCHBY není potřeba, zvažte explicitní zadání MATCHBY, aby nedocházelo k nejasnostem.

Pokračování z příkladů uvedených výše, zde je poslední výraz:

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

Pokud chceme explicitně určit, jak mají být řádky jednoznačně identifikovány, můžeme zadat MATCHBY, jak je znázorněno v následujícím ekvivalentním výrazu:

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

Vzhledem k tomu, že je zadán MATCHBY, oba sloupce zadané v MATCHBY i v PARTITIONBY slouží k jedinečné identifikaci řádků. Vzhledem k tomu, že v MATCHBY i PARTITIONBYje zadaná funkce Color, je následující výraz ekvivalentní předchozímu výrazu:

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

Vzhledem k tomu, že určení MATCHBY není nutné v příkladech, na které jsme se zatím podívali, podívejme se na mírně jiný příklad, který vyžaduje MATCHBY. V tomto případě máme seznam řádků objednávek. Každý řádek představuje položku objednávky. Objednávka může mít více řádků objednávky a řádek objednávky 1 se zobrazí v mnoha objednávkách. Kromě toho pro každý řádek objednávky máme ProductKey a SalesAmount. Ukázka relevantních sloupců v tabulce vypadá takto:

ČísloObjednávkyProdeje	ČísloŘádkuObjednávky	Produktový klíč	Částka prodeje
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

Všimněte si, že SalesOrderNumber i SalesOrderLineNumber jsou potřeba k jednoznačné identifikaci řádků.

Pro každou objednávku chceme vrátit předchozí objem prodeje stejného produktu (reprezentovaný kódem ProductKey) seřazený hodnotou SalesAmount v sestupném pořadí. Následující výraz nebude fungovat, protože ve vRelation jsou potenciálně více řádků, což se předává do OFFSET:

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

Tento výraz vrátí chybu: "OFFSETrelační parametr může mít duplicitní řádky, které nejsou povoleny."

Aby tento výraz fungoval, musí být zadán MATCHBY a musí obsahovat všechny sloupce, které jednoznačně definují řádek. MATCHBY se tady vyžaduje, protože relace FactInternetSales neobsahuje žádné explicitní klíče ani jedinečné sloupce. Sloupce SalesOrderNumber a SalesOrderLineNumber však společně tvoří složený klíč, kde jejich existence je jedinečná ve vztahu a může tedy jednoznačně identifikovat každý řádek. Není dostatečné uvést pouze SalesOrderNumber nebo SalesOrderLineNumber, protože oba sloupce obsahují opakující se hodnoty. Tento problém řeší následující výraz:

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

A tento výraz skutečně vrací výsledky, které hledáme.

ČísloObjednávkyProdeje	ČísloŘádkuObjednávky	Produktový klíč	Částka prodeje	Předchozí částka prodeje
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

Související obsah

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER