Principy funkcí ORDERBY, PARTITIONBY a POZVYHLEDAT

Funkce ORDERBY, PARTITIONBY a POZVYHLEDAT v jazyce DAX jsou speciální funkce, které lze použít pouze spolu s funkcemi okna DAX: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Pochopení FUNKCE ORDERBY, PARTITIONBY a MATCHBY je důležité pro úspěšné použití funkcí Window. Zde uvedené příklady používají posun, 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 jazyce 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á funkce SUMMARIZECOLUMNS k výpočtu součtu sloupce SalesAmount v tabulce FactInternetSales podle sloupce Color z tabulky DimProduct a sloupce CalendarYear z tabulky DimDate. Tady je výsledek:

Color	CalendarYear	CurrentYearSales
"Č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 od prodeje v aktuálním roce v kontextu. 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í posunu

POSUN je ideální pro typické porovnání s předchozími typy výpočtů potřebných 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]
        )
    )

S tímto výrazem se hodně děje. Pomocí addCOLUMNS jsme rozbalili tabulku z předchozího sloupce s názvem PreviousColorSales. Obsah tohoto sloupce je nastavený na CurrentYearSales, což je SUMA(FactInternetSales[SalesAmount]) pro předchozí barvu (načteno pomocí OFFSET).

Výsledkem je:

Color	CalendarYear	CurrentYearSales	PreviousColorSales
"Č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, co jsme poté. 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í posunu pomocí funkce ORDERBY seřadí tabulku podle barvy a kalendáře ve vzestupném pořadí, což určuje, co se považuje za předchozí vrácený řádek.

Důvodem, proč jsou tyto dva výsledky ekvivalentní, je to, že FUNKCE ORDERBY automaticky obsahuje všechny sloupce z relace, které nejsou v PARTITIONBY. Vzhledem k tomu, že funkce PARTITIONBY nebyla zadána, je FUNKCE ORDERBY nastavena na Color (Barva), CalendarYear (Kalendář) a CurrentYearSales (Aktuální_YearSales). 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 ORDERBY zadali pouze barvu, výsledky jsou stejné, protože CalendarYear by se automaticky přidal. Důvodem je to, že funkce přidá do ORDERBY tolik sloupců, kolik je potřeba, aby každý řádek mohl být jednoznačně identifikován sloupci ORDERBY a 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]
        )
    )

Přidání PARTITIONBY

Teď, abychom téměř získali výsledek, který jsme poté, co 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 FUNKCE 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 FUNKCE 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:

Zatímco ORDERBY je nastavena na CalendarYear a CurrentYearSales automaticky, žádná záruka není udělena, pokud jde o pořadí, ve kterém budou přidány. Pokud se před CalendarYearSales přidá CurrentYearSales, výsledné pořadí není vložené s očekávaným způsobem. Při zadávání ORDERBY a PARTITIONBY buďte explicitní, abyste se vyhnuli nejasnostem a neočekávaným výsledkům.

Oba výrazy vrátí výsledek, který následuje:

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Č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í prodej za [Red, 2019] atd. 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 POSUN. V předchozím příkladu je tabulka rozdělena na tolik částí, kolik je barev, jedna pro každou barvu. Potom se v každé části vypočítá posun seřazený podle kalendáře.

Vizuální, co se děje, je toto:

Nejprve volání funkce 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. V dalším kroku orderBY zajistí, aby každá část byla seřazena podle CalendarYear (reprezentované oranžovými šipkami). Nakonec v rámci každé seřazené části pro každý řádek najde POSUN řá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, musíme jednoduše odečíst currentYearSales z prodeje za předchozí rok pro stejnou barvu vrácenou voláním funkce OFFSET. Vzhledem k tomu, že nás nezajímá zobrazení prodeje za předchozí rok pro stejnou barvu, ale pouze v prodeji za aktuální rok a rozdíl v roce v průběhu roku. 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:

Color	CalendarYear	CurrentYearSales	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í funkce POZVYHLEDAT

Možná jste si všimli, že jsme vůbec nezadali MATCHBY. V tomto případě není nutné. Sloupce v ORDERBY a PARTITIONBY (pokud jsou uvedené v příkladech výše) jsou dostatečné k jednoznačné identifikaci jednotlivých řádků. Vzhledem k tomu, že jsme nezadali FUNKCI POZVYHLEDAT, používají se sloupce zadané v ORDERBY a PARTITIONBY k jedinečné identifikaci jednotlivých řádků, aby bylo možné je porovnat tak, aby funkce POSUN poskytovala 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í funkce POZVYHLEDAT pomoct s řešením chyby.

Pokud je zadán matchBY, sloupce v MATCHBY a PARTITIONBY se používají k jedinečné identifikaci jednotlivých řádků. Pokud to není možné, vrátí se chyba. I když funkce POZVYHLEDAT není povinná, zvažte explicitní zadání funkce POZVYHLEDAT, abyste se vyhnuli nejasnostem.

Když budete pokračovat z výše uvedených příkladů, tady 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 jedinečně identifikovány, můžeme zadat FUNKCI POZVYHLEDAT, 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ána funkce POZVYHLEDAT, používají se k jedinečné identifikaci řádků jak sloupce zadané v FUNKCI POZVYHLEDAT, tak i v PARTITIONBY. Vzhledem k tomu, že funkce Color je zadána v matchBY i PARTITIONBY, 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 v příkladech, na které jsme se zatím podívali, není určení FUNKCE POZVYHLEDAT POTŘEBA, podívejme se na mírně odlišný příklad, který vyžaduje MATCHBY. V tomto případě máme seznam řádků objednávek. Každý řádek představuje řádek objednávky pro objednávku. 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:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount
SO51900	0	528	4.99
SO51948	0	528	5.99
SO52043	0	528	4.99
SO52045	0	528	4.99
SO52094	0	528	4.99
SO52175	0	528	4.99
SO52190	0	528	4.99
SO52232	0	528	4.99
SO52234	0	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 existuje potenciálně více řádků vRelation, protože se předává do posunu:

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: Parametr RELATION posunu může obsahovat duplicitní řádky, které nejsou povolené.

Aby tento výraz fungoval, musí být zadán funkce POZVYHLEDAT a musí obsahovat všechny sloupce, které jednoznačně definují řádek. FUNKCE POZVYHLEDAT je zde nutná, 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á v relaci a může tedy jednoznačně identifikovat každý řádek. Stačí zadat 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é jsme po:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Předchozí částka prodeje
SO51900	0	528	5.99
SO51948	0	528	4.99	5.99
SO52043	0	528	4.99	4.99
SO52045	0	528	4.99	4.99
SO52094	0	528	4.99	4.99
SO52175	0	528	4.99	4.99
SO52190	0	528	4.99	4.99
SO52232	0	528	4.99	4.99
SO52234	0	528	4.99	4.99
SO52234	2	529	3.99

Související obsah

ORDERBY
PARTITIONBY
MATCHBY
INDEX
POSUN
OKNO
HODNOST
ROWNUMBER