Principy funkcí ORDERBY, PARTITIONBY, andMATCHBY

ORDERBY, PARTITIONBY, andMATCHBY funkce v DAX jsou speciální funkce, které lze použít pouze společně s funkcemi DAXWindow: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

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

Scénář

Začněme příkladem, který nepoužívá funkce Window na all. Níže je uvedena tabulka, která zobrazuje celkové prodeje pro každou barvu pro calendaryear. Existuje několik způsobů, jak tuto tabulku define vytvořit, ale protože nás zajímá, co se stane v DAX, použijeme tabulku se vzorcem. Tady je výraz tabulky:

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

Uvidíte, že tento výraz počítané tabulky používá SUMMARIZECOLUMNS k calculateSUM sloupce SalesAmount v tabulce FactInternetSales podle sloupce Color z tabulky DimProduct and sloupec 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
Multi	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
stříbro	2019	2165176
Stříbrný	2020	1871788
Bílá	2019	2517
"Bílá"	2020	2589
"Žlutá"	2018	163071
"Žlutá"	2019	2072083
"Žlutá"	2020	2621602

Now, představme si, že se snažíme vyřešit obchodní otázku výpočtu rozdílu v prodeji, year-over-year pro každou barvu. V podstatě potřebujeme způsob, jak find prodej pro stejnou barvu v previousyearand odečíst od prodeje v aktuálním yearv kontextu. Například pro kombinaci [Red, 2019] hledáme prodej pro [Red, 2018]. Jakmile to máme, můžeme to odečíst od aktuálního prodeje and a vrátit požadované value.

Použití OFFSET

OFFSET je ideální pro typické porovnání s previous 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. Naším first pokusem 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á. Použili jsme ADDCOLUMNS k expand tabulku z dřívějška s názvem PreviousColorSales. Obsah tohoto sloupce je nastaven na CurrentYearSales, což odpovídá SUM(FactInternetSales[SalesAmount]), pro barvu previous (načtenou pomocí OFFSET).

Výsledkem je:

Barva	Kalendářní rok	Prodeje tohoto roku	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
Mult	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 if podíváme se pozorně, že neodpovídá přesně tomu, 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í OFFSET používá ORDERBY k seřazení tabulky podle barvy and CalendarYear vzestupně, což určuje, co se považuje za vrácený řádek previous.

Důvod, proč jsou tyto dva výsledky ekvivalentní, je, že ORDERBY automaticky containsall sloupce z relace, které nejsou v PARTITIONBY. Vzhledem k tomu, že PARTITIONBY nebylo specifikováno, je ORDERBY nastavena na barvu, kalendářní rok, and aktuální prodej za rok. Vzhledem k tomu, že dvojice s barvou and v rámci CalendarYear v relaci jsou jedinečné, přidání CurrentYearSales nemění výsledek. V fact, evenif jsme zadali pouze barvu v ORDERBY, výsledky jsou stejné, protože CalendarYear by se automaticky přidal. Důvodem je to, že funkce přidá tolik sloupců, kolik je potřeba k ORDERBY, aby bylo zajištěno, že každý řádek může být jednoznačně identifikován ORDERBYandPARTITIONBY 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

Now téměř získat výsledek, který můžeme použít PARTITIONBY, jak je znázorněno v následujícím výrazu počítané 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 containsall 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 and 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 and CurrentYearSales automaticky, není žádná záruka, pokud jde o pořadí, ve kterém budou přidány. If CurrentYearSales se přidá před CalendarYear, výsledné pořadí není v souladu s očekáváním. Být explicitní při zadávání ORDERBYandPARTITIONBY, aby nedocházelo k nejasnostem and neočekávané výsledky.

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

Barva	kalendářní rok	Prodej za aktuální 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
Multi	2019	48622
Multi	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 pro previousyear pro stejnou barvu. Pro [Red, 2020] vrátí prodej za [Red, 2019], and a podobně. If neexistuje žádná previousyear, například v případě [Red, 2017], nevrátí se žádná value.

PARTITIONBY si můžete představit jako způsob, jak divide tabulku do částí, 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

First, volání PARTITIONBY vede k tomu, že se tabulka rozdělí do částí, jedna pro každou barvu. Toto je reprezentováno světle modrými poli na obrázku tabulky. Next, ORDERBY zajistí, aby každá část byla seřazena podle CalendarYear (reprezentované oranžovými šipkami). Nakonec v každé seřazené části OFFSET najde řádek nad ním and vrátí value ve sloupci PreviousYearSalesForSameColor. Vzhledem k tomu, že pro každý first řádek v každé části není ve stejné části žádný řádek previous, je výsledek v tomto řádku pro sloupec PreviousYearSalesForSameColor prázdný.

K dosažení konečného výsledku musíme jednoduše odečíst prodeje v aktuálním roce od prodeje previousyear pro stejnou barvu, kterou vrací volání OFFSET. Vzhledem k tomu, že not zájem o zobrazení prodeje previousyear pro stejnou barvu, ale pouze v aktuálním year prodeji andyear oproti rozdílu year. 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]
        )
    )

And zde je výsledek toho výpočtu:

Barva	kalendářní rok	Prodeje za tento 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
Multi	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 nezadali MATCHBY v all. V tomto případě není nutné. Sloupce v ORDERBYandPARTITIONBY (pokud jsou uvedené v příkladech výše) stačí k jednoznačné identifikaci jednotlivých řádků. Vzhledem k tomu, že jsme nezadali MATCHBY, sloupce zadané v ORDERBYandPARTITIONBY slouží k jedinečné identifikaci jednotlivých řádků, aby bylo možné je porovnat, aby OFFSET poskytl smysluplný výsledek. If sloupce v ORDERBYandPARTITIONBY nemohou jednoznačně identifikovat jednotlivé řádky, do klauzule ORDERBY je možné přidat další sloupce, if tyto nadbytečné sloupce umožňují jedinečně identifikovat každý řádek. If je možné not, vrátí se error. V tomto last případě může určení MATCHBY pomoct vyřešit error.

If MATCHBY je zadán, sloupce v MATCHBYandPARTITIONBY slouží k jedinečné identifikaci jednotlivých řádků. If, to je not možné, je vráceno error. Even if MATCHBY není nutné, zvažte explicitní zadání MATCHBY, abyste se vyhnuli nejasnostem.

Pokračujeme z výše uvedených příkladů, tady je výraz 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 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 obou MATCHBYandPARTITIONBYje zadaná funkce Color, je následující výraz ekvivalentní výrazu 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]
        )
    )

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ávek and řádek objednávky 1 se zobrazí v mnoha objednávkách. Kromě toho pro každý řádek objednávky máme ProductKey and a SalesAmount. sample 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 Kč
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 čísla SalesOrderNumber and a SalesOrderLineNumber jsou nutná k jednoznačné identifikaci řádků.

Pro každou objednávku chceme vrátit částku prodeje previous pro stejný product (představovaný kódem ProductKey), která je seřazena podle hodnoty prodeje 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í error: "OFFSETrelační parametr může mít duplicitní řádky, které nejsou povoleny."

Aby tento výraz fungoval, musí být MATCHBY zadán a and musí obsahovat all sloupce, které jednoznačně define řádek. MATCHBY se tady vyžaduje, protože relace FactInternetSales neobsahuje žádné explicitní klíče or jedinečných sloupců. Sloupce SalesOrderNumber and a SalesOrderLineNumber společně tvoří složený klíč, kde jejich společná existence je jedinečná ve vztahu and, a proto mohou jednoznačně identifikovat každý řádek. Nestačí pouze zadat SalesOrderNumber or a SalesOrderLineNumber, protože oba sloupce obsahují opakující se hodnoty values. 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]
            )
    )

And tento výraz skutečně vrací výsledky, o které se snažíme:

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

obsah Related

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER