Základné informácie o funkciách ORDERBY, PARTITIONBYa MATCHBY

Funkcie ORDERBY, PARTITIONBYa MATCHBY v DAX sú špeciálne funkcie, ktoré možno použiť len spolu s funkciami DAX Window: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Pre úspešné používanie funkcií okna je nevyhnutné porozumieť ORDERBY, PARTITIONBYa MATCHBY. Príklady uvedené v tomto článku používajú OFFSET, ale sú podobné ako v prípade ostatných funkcií okna.

Scenár

Začnime s príkladom, ktorý vôbec nepoužíva funkcie okna. Zobrazená nižšie je tabuľka, ktorá vráti celkový predaj na farbu pre kalendárny rok. Tabuľku je možné definovať viacerými spôsobmi, ale keďže máme záujem pochopiť, čo sa stane v DAX, použijeme vypočítavanú tabuľku. Tu je výraz tabuľky:

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

Výraz vypočítanej tabuľky používa SUMMARIZECOLUMNS na výpočet SUM stĺpca ObjemPredaja v tabuľke FactInternetSales podľa stĺpca Color z tabuľky DimProduct a stĺpca CalendarYear z tabuľky DimDate. Tu je výsledok:

Teraz si predstavme, že sa snažíme vyriešiť obchodnú otázku výpočtu rozdielu predaja pre každú farbu medziročne. Potrebujeme teda nájsť spôsob, ako vyhľadať predaj pre rovnakú farbu v predchádzajúcom roku a odpočítať ho od predaja v aktuálnom roku v kontexte. Napríklad pre kombináciu [Červená, 2019] hľadáme predaj pre [Červená, 2018]. Keď to bude mať, môžeme ju odčítať od aktuálneho predaja a vrátiť požadovanú hodnotu.

Používanie OFFSET

OFFSET ideálny pre typické v porovnaní s predchádzajúcimi typmi výpočtov potrebných na zodpovedanie obchodnej otázky popísanej vyššie, pretože nám umožňuje vykonať relatívny pohyb. Náš prvý pokus môže byť:

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ýrazom sa toho veľa deje. Použili sme ADDCOLUMNS na rozbalenie tabuľky spred a stĺpcom s názvom PreviousColorSales. Obsah tohto stĺpca je nastavený na hodnotu CurrentYearSales, ktorá je SUM(FactInternetSales[SalesAmount]) pre predchádzajúcu farbu (načítaná pomocou OFFSET).

Výsledok je:

Toto je o krok bližšie k nášmu cieľu, ale ak sa pozrieme pozorne, nezhoduje sa presne to, po čom ideme. Napríklad pre hodnotu [Silver, 2017] sa hodnota PreviousColorSales nastaví na hodnotu [Red, 2020].

Pridanie ORDERBY

Vyššie uvedená definícia je ekvivalentná:

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 prípade volanie funkcie OFFSET používa ORDERBY na zoradenie tabuľky podľa hodnôt Color a CalendarYear vo vzostupnom poradí, ktoré určuje, čo sa považuje za predchádzajúci vrátený riadok.

Dôvodom, prečo sú tieto dva výsledky rovnocenné, je, že ORDERBY automaticky obsahuje všetky stĺpce zo vzťahu, ktoré nie sú v PARTITIONBY. Keďže PARTITIONBY nebol zadaný, ORDERBY sa nastaví na položky Color (Farba), CalendarYear (KalendárnyRok) a CurrentYearSales (AktuálnyPredajRoku). Keďže však páry Color a CalendarYear sú vo vzťahu jedinečné, pridanie stĺpca CurrentYearSales výsledok nezmení. Dokonca aj keby sme zadali farbu iba v ORDERBY, výsledky sú rovnaké, pretože calendarYear by sa pridali automaticky. Dôvodom je, že funkcia pridá toľko stĺpcov, koľko je potrebné na ORDERBY, aby sa zabezpečilo, že každý riadok bude môcť byť jednoznačne identifikovaný ORDERBY a PARTITIONBY stĺpcoch:

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

Pridanie PARTITIONBY

Ak chcete takmer získame výsledok, ktorý sme získali po použití PARTITIONBY, ako je to znázornené v nasledujúcom výraze vypočítanej tabuľky:

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šimnite si, že zadanie ORDERBY je tu voliteľné, pretože ORDERBY automaticky obsahuje všetky stĺpce zo vzťahu, ktoré nie sú zadané v PARTITIONBY. Takže nasledujúci výraz vráti rovnaké výsledky, pretože ORDERBY sa automaticky nastaví na stĺpce CalendarYear a 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]
        )
    )

Oba výrazy vrátia výsledok, ktorý nasleduje:

Ako môžete vidieť v tejto tabuľke, stĺpec PreviousYearSalesForSameColor zobrazuje predaj za predchádzajúci rok pre rovnakú farbu. Pre [Červená, 2020] vráti predaj pre [Červená, 2019] a tak ďalej. Ak neexistuje žiadny predchádzajúci rok, napríklad v prípade [Červená, 2017], nevráti sa žiadna hodnota.

PARTITIONBY si môžete predstaviť ako spôsob, ako rozdeliť tabuľku na časti, v ktorých sa má vykonať výpočet OFFSET. V príklade uvedenom vyššie je tabuľka rozdelená do počtu častí, v ktorých sú farby, pričom jedna je pre každú farbu. Potom sa v každej časti vypočíta OFFSET zoradí podľa stĺpca KalendárnyRok.

Z vizuálneho prehľadu sa deje toto:

kalendárneho roka

Po prvé, výsledkom volania funkcie PARTITIONBY je rozdelenie tabuľky na časti, po jednom pre každú farbu. Toto je znázornené svetlomodrými poliami na obrázku tabuľky. Potom ORDERBY uistite sa, že každá časť je zoradená podľa stĺpca KalendárnyRok (znázornená oranžovými šípkami). Nakoniec v každej zoradenej časti nájde OFFSET nad ním riadok a vráti túto hodnotu v stĺpci PreviousYearSalesForSameColor. Keďže pre každý prvý riadok v každej časti neexistuje v tej istej časti žiadny predchádzajúci riadok, výsledok v tomto riadku pre stĺpec PreviousYearSalesForSameColor je prázdny.

Ak chceme dosiahnuť konečný výsledok, stačí odčítať hodnotu CurrentYearSales od predaja za predchádzajúci rok pre rovnakú farbu, ktorú vrátila výzva na OFFSET. Keďže nechceme zobrazovať predaj za predchádzajúci rok pre rovnakú farbu, ale len v aktuálnom roku predaja a v roku oproti predošlému rozdielu. Tu je finálny výraz vypočítanej tabuľky:

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

Tu je výsledok tohto výrazu:

Používanie MATCHBY

Možno ste si všimli, že sme vôbec neurčili MATCHBY. V tomto prípade to nie je potrebné. Stĺpce v ORDERBY a PARTITIONBY (pokiaľ boli zadané v príkladoch vyššie) stačia na jedinečnú identifikáciu každého riadka. Keďže sme neurčili MATCHBY, stĺpce zadané v ORDERBY a PARTITIONBY sa používajú na jedinečnú identifikáciu každého riadka, aby ich bolo možné porovnať a povoliť OFFSET dávať zmysluplný výsledok. Ak stĺpce v ORDERBY a PARTITIONBY nemôžu jednoznačne identifikovať každý riadok, do klauzuly ORDERBY môžu byť pridané ďalšie stĺpce, ak tieto dodatočné stĺpce umožňujú jedinečnú identifikáciu každého riadka. Ak to nie je možné, vráti sa chyba. V poslednom prípade môže špecifikácia MATCHBY pomôcť vyriešiť chybu.

Ak je zadaná hodnota MATCHBY, stĺpce v MATCHBY a PARTITIONBY sa použijú na jedinečnú identifikáciu každého riadka. Ak to nie je možné, vráti sa chyba. Dokonca aj keď MATCHBY nie je povinné, zvážte explicitné zadanie MATCHBY, aby ste sa vyhli nejasnostiam.

Pokračujeme v príkladoch uvedených vyššie a tu 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]
        )
    )

Ak chceme explicitne určiť, ako majú byť riadky jednoznačne identifikované, môžeme zadať MATCHBY tak, ako je to znázornené v nasledujúcom ekvivalentnom výraze:

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

Keďže je zadaná MATCHBY, na jedinečnú identifikáciu riadkov sa používajú stĺpce zadané v MATCHBY, ako aj v PARTITIONBY. Keďže hodnota Color je zadaná v MATCHBY aj PARTITIONBY, nasledujúci výraz je ekvivalentný predchádzajúcemu 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]
        )
    )

Keďže špecifikovanie MATCHBY nie je v doteraz zobrazených príkladoch potrebné, pozrime sa na trochu iný príklad, ktorý si vyžaduje MATCHBY. V tomto prípade máme k dispozícii zoznam riadkov objednávok. Každý riadok predstavuje riadok objednávky pre objednávku. Objednávka môže mať viacero riadkov objednávky a riadok objednávky 1 sa zobrazí v mnohých objednávkach. Navyše pre každý riadok objednávky máme kód ProductKey a SalesAmount (ObjemPredaja). Ukážka relevantných stĺpcov v tabuľke vyzerá takto:

Všimnite si, že na jedinečnú identifikáciu riadkov sa vyžadujú obe možnosti ČísloPredajnejObjednávky a ČísloRiadkaPredajnejObjednávky.

Pre každú objednávku chceme vrátiť predchádzajúcu čiastku predaja toho istého produktu (reprezentovanú kódom ProductKey) zoradeného podľa objem predaja v zostupnom poradí. Nasledujúci výraz nebude fungovať, pretože vo funkcii vRelation môže existovať potenciálne viacero riadkov, pretože sa odovzdajú 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áti chybu: "OFFSETrelačný parameter môže mať duplicitné riadky, čo nie je povolené."

Aby tento výraz fungoval, MATCHBY musí byť zadaný a musí obsahovať všetky stĺpce, ktoré jednoznačne definujú riadok. MATCHBY je tu potrebné, pretože vzťah FactInternetSales neobsahuje žiadne explicitné kľúče ani jedinečné stĺpce. Stĺpce SalesOrderNumber (ČísloPredajnejObjednávky) a SalesOrderLineNumber (ČísloRiadkaPredajnejObjednávky) však spoločne tvoria zložený kľúč, kde je ich existencia spolu jedinečná vo vzťahu, a preto dokážu jednoznačne identifikovať každý riadok. Stačí zadať iba zadanie hodnôt ČísloPredajnejObjednávky alebo ČísloRiadkaPredajnejObjednávky, pretože oba stĺpce obsahujú opakujúce sa hodnoty. Tento problém vyrieši nasledujúci 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]
            )
    )

Tento výraz totiž vráti výsledky, ktoré nasleduje:

Súvisiaci obsah

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER