Omówienie funkcji ORDERBY, PARTITIONBYi MATCHBY

Funkcje ORDERBY, PARTITIONBYi MATCHBY w systemie DAX to specjalne funkcje, których można używać tylko wraz z funkcjami DAX okna: INDEX, , OFFSETWINDOW, RANK, . ROWNUMBER

Zrozumienie ORDERBY, PARTITIONBYi MATCHBY ma kluczowe znaczenie dla pomyślnego korzystania z funkcji Window. W przykładach podanych tutaj użyto metody OFFSET, ale są one podobnie stosowane do innych funkcji okna.

Scenariusz

Zacznijmy od przykładu, który w ogóle nie używa funkcji Okna. Poniżej przedstawiono tabelę, która zwraca łączną sprzedaż według koloru i roku kalendarzowego. Istnieje wiele sposobów definiowania tej tabeli, ale ponieważ interesuje nas zrozumienie tego, co się dzieje w DAXtabeli , użyjemy tabeli obliczeniowej. Oto wyrażenie tabeli:

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

Zobaczysz, że to wyrażenie tabeli obliczeniowej używa SUMMARIZECOLUMNS do obliczania SUM kolumny SalesAmount w tabeli FactInternetSales, względem kolumny Color z tabeli DimProduct oraz kolumny CalendarYear z tabeli DimDate. Oto wynik:

Kolor	Rok kalendarzowy	SprzedażZaBieżącyRok
Czarny	2017	393885
Czarny	2018	1818835
Czarny	2019	3981638
Czarny	2020	2644054
"Niebieski"	2019	994448
"Niebieski"	2020	1284648
Multi	2019	48622
Multi	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Czerwony"	2017	2961198
"Czerwony"	2018	3686935
"Czerwony"	2019	900175
"Czerwony"	2020	176022
Srebro	2017	326399
Srebro	2018	750026
Srebro	2019	2165176
Srebro	2020	1871788
"Biały"	2019	2517
"Biały"	2020	2589
"Żółty"	2018	163071
"Żółty"	2019	2072083
"Żółty"	2020	2621602

Teraz wyobraźmy sobie, że staramy się rozwiązać pytanie biznesowe dotyczące obliczania różnicy w sprzedaży, rok do roku dla każdego koloru. W rzeczywistości potrzebujemy sposobu znalezienia sprzedaży dla tego samego koloru w poprzednim roku i odejmowania tej sprzedaży w bieżącym roku w kontekście. Na przykład, przy zestawieniu [Red, 2019] szukamy danych sprzedaży z roku [Red, 2018]. Gdy to zrobimy, możemy odjąć ją od bieżącej sprzedaży i zwrócić wymaganą wartość.

Korzystanie z OFFSET

OFFSET jest idealny do typowego porównania z wcześniejszymi obliczeniami wymaganymi do odpowiedzi na pytanie biznesowe opisane powyżej, ponieważ pozwala nam to dokonać względnej analizy. Naszą pierwszą próbą moż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]
        )
    )

Wiele się dzieje wokół tego wyrażenia. Użyliśmy ADDCOLUMNS do rozszerzenia wcześniejszej tabeli o kolumnę o nazwie PreviousColorSales. Zawartość tej kolumny jest ustawiona na wartość CurrentYearSales ( SUMFactInternetSales[SalesAmount]) dla poprzedniego koloru (pobranego przy użyciu polecenia OFFSET).

Wynik to:

Kolor	Rok kalendarzowy	SprzedażBieżącegoRoku	PoprzedniaSprzedażKoloru
Czarny	2017	393885
Czarny	2018	1818835	393885
Czarny	2019	3981638	1818835
Czarny	2020	2644054	3981638
"Niebieski"	2019	994448	2644054
"Niebieski"	2020	1284648	994448
Multi	2019	48622	1284648
Multi	2020	57849	48622
NA	2019	207822	57849
"NA"	2020	227295	207822
"Czerwony"	2017	2961198	227295
"Czerwony"	2018	3686935	2961198
"Czerwony"	2019	900175	3686935
"Czerwony"	2020	176022	900175
Srebro	2017	326399	176022
Srebro	2018	750026	326399
srebro	2019	2165176	750026
Srebro	2020	1871788	2165176
"Biały"	2019	2517	1871788
"Biały"	2020	2589	2517
"Żółty"	2018	163071	2589
"Żółty"	2019	2072083	163071
"Żółty"	2020	2621602	2072083

Jest to krok bliżej naszego celu, ale jeśli przyjrzymy się bardziej, nie pasuje dokładnie do tego, czego szukamy. Na przykład dla elementu [Silver, 2017] właściwość PreviousColorSales jest ustawiona na [Red, 2020].

Dodawanie ORDERBY

Powyższa definicja jest równoważna:

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

W tym przypadku wywołanie OFFSET korzysta z ORDERBY do uporządkowania tabeli według wartości Color i CalendarYear w kolejności rosnącej, co określa, jaki jest uznawany za poprzedni zwrócony wiersz.

Powodem, dla którego te dwa wyniki są równoważne, jest to, że ORDERBY automatycznie zawiera wszystkie kolumny z relacji, które nie znajdują się w PARTITIONBY. Ponieważ PARTITIONBY nie określono wartości, ORDERBY jest ustawiony na Color, CalendarYear i CurrentYearSales. Jednak ponieważ pary Color i CalendarYear w relacji są unikatowe, dodanie wartości CurrentYearSales nie zmienia wyniku. W rzeczywistości, nawet jeśli mieliśmy określić kolor tylko w ORDERBY, wyniki są takie same, ponieważ CalendarYear zostanie automatycznie dodany. Wynika to z faktu, że funkcja doda tyle kolumn, ile będzie potrzebne do ORDERBY, aby upewnić się, że każdy wiersz może być jednoznacznie identyfikowany przez kolumny ORDERBY i 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]
        )
    )

Dodawanie PARTITIONBY

Aby niemal uzyskać wynik, którego oczekujemy, możemy użyć PARTITIONBY, jak pokazano w poniższym wyrażeniu tabeli obliczeniowej.

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

Zwróć uwagę, że określenie ORDERBY jest tutaj opcjonalne, ponieważ ORDERBY automatycznie zawiera wszystkie kolumny z relacji, które nie są określone w PARTITIONBY. Dlatego następujące wyrażenie zwraca te same wyniki, ponieważ ORDERBY jest ustawione na CalendarYear i CurrentYearSales automatycznie:

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

Uwaga / Notatka

Wartość CalendarYear i CurrentYearSales jest ustawiana automatycznie, ale ORDERBY nie ma żadnej gwarancji co do kolejności, w jakiej zostaną dodane. Jeśli CurrentYearSales zostanie dodany przed CalendarYear, wynikowa kolejność nie będzie zgodna z oczekiwaniami. Należy jawnie określać ORDERBY i PARTITIONBY unikać pomyłek i nieoczekiwanych wyników.

Oba wyrażenia zwracają wynik, którego szukamy.

Kolor	Rok kalendarzowy	SprzedażBieżącegoRoku	Sprzedaż za Poprzedni Rok dla Tego Samego Koloru
Czarny	2017	393885
Czarny	2018	1818835	393885
Czarny	2019	3981638	1818835
Czarny	2020	2644054	3981638
"Niebieski"	2019	994448
"Niebieski"	2020	1284648	994448
"Multi"	2019	48622
Multi	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Czerwony"	2017	2961198
"Czerwony"	2018	3686935	2961198
"Czerwony"	2019	900175	3686935
"Czerwony"	2020	176022	900175
Srebro	2017	326399
Srebro	2018	750026	326399
Srebro	2019	2165176	750026
Srebro	2020	1871788	2165176
"Biały"	2019	2517
"Biały"	2020	2589	2517
"Żółty"	2018	163071
"Żółty"	2019	2072083	163071
"Żółty"	2020	2621602	2072083

Jak widać w tej tabeli, kolumna PreviousYearSalesForSameColor pokazuje sprzedaż w poprzednim roku dla tego samego koloru. Dla [Red, 2020] zwraca dane sprzedaży z [Red, 2019] i analogicznie dla kolejnych lat. Jeśli nie ma poprzedniego roku, na przykład w przypadku [Red, 2017], żadna wartość nie jest zwracana.

Możesz traktować PARTITIONBY jako sposób dzielenia tabeli na części, w których należy wykonać OFFSET obliczenia. W powyższym przykładzie tabela jest podzielona na tyle części, ile są kolory, po jednym dla każdego koloru. Następnie, w każdej części, OFFSET jest obliczany i posortowany według Roku Kalendarzowego.

Oto, co dzieje się wizualnie:

Najpierw wywołanie PARTITIONBY powoduje podzielenie tabeli na części, po jednym dla każdego koloru. Jest to reprezentowane przez jasnoniebieskie pola na obrazie tabeli. Następnie ORDERBY się upewnia, że każda część jest sortowana według CalendarYear (reprezentowanego przez pomarańczowe strzałki). Na koniec w każdej posortowanej części dla każdego wiersza OFFSET znajduje wiersz nad nim i zwraca taką wartość w kolumnie PreviousYearSalesForSameColor. Ponieważ dla każdego pierwszego wiersza w każdej części nie ma poprzedniego wiersza w tej samej części, wynik w tym wierszu dla kolumny PreviousYearSalesForSameColor jest pusty.

Aby osiągnąć ostateczny wynik, wystarczy odjąć sprzedaż z bieżącego roku od sprzedaży z poprzedniego roku dla tego samego koloru, zwróconego przez wywołanie OFFSET. Ponieważ nie jesteśmy zainteresowani pokazywaniem sprzedaży w poprzednim roku dla tego samego koloru, a jedynie sprzedażą bieżącego roku oraz różnicą rok do roku. Oto końcowe wyrażenie tabeli obliczeniowej:

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

Oto wynik tego wyrażenia:

Kolor	Rok kalendarzowy	SprzedażBieżącegoRoku	YoYSalesForSameColor
Czarny	2017	393885	393885
Czarny	2018	1818835	1424950
Czarny	2019	3981638	2162803
Czarny	2020	2644054	-1337584
"Niebieski"	2019	994448	994448
"Niebieski"	2020	1284648	290200
"Multi"	2019	48622	48622
Multi	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Czerwony"	2017	2961198	2961198
"Czerwony"	2018	3686935	725737
"Czerwony"	2019	900175	-2786760
"Czerwony"	2020	176022	-724153
srebro	2017	326399	326399
Srebro	2018	750026	423627
Srebro	2019	2165176	1415150
Srebro	2020	1871788	-293388
"Biały"	2019	2517	2517
"Biały"	2020	2589	72
"Żółty"	2018	163071	163071
"Żółty"	2019	2072083	1909012
"Żółty"	2020	2621602	549519

Korzystanie z MATCHBY

Być może zauważyliśmy, że w ogóle nie określiliśmy MATCHBY . W takim przypadku nie jest to konieczne. Kolumny w ORDERBY i PARTITIONBY (o ile zostały określone w powyższych przykładach) są wystarczające do unikatowego identyfikowania każdego wiersza. Ponieważ nie określiliśmy MATCHBY, kolumny określone w ORDERBY i PARTITIONBY są używane do unikatowego identyfikowania każdego wiersza, co umożliwia ich porównanie i pozwala OFFSET uzyskać znaczący wynik. Jeśli kolumny w ORDERBY i PARTITIONBY nie mogą jednoznacznie zidentyfikować każdego wiersza, można dodać dodatkowe kolumny do klauzuli ORDERBY, jeśli te dodatkowe kolumny pozwalają na jednoznaczne zidentyfikowanie każdego wiersza. Jeśli to nie jest możliwe, zostanie zwrócony błąd. W tym ostatnim przypadku określenie MATCHBY może pomóc w rozwiązaniu błędu.

Jeśli MATCHBY zostanie określony, kolumny w MATCHBY i PARTITIONBY są używane do unikatowego identyfikowania każdego wiersza. Jeśli to nie jest możliwe, zostanie zwrócony błąd. Nawet jeśli MATCHBY nie jest wymagane, rozważ jawne określenie MATCHBY, aby uniknąć pomyłek.

Kontynuując powyższe przykłady, oto ostatnie wyrażenie:

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

Jeśli chcemy jawnie określić sposób identyfikowania wierszy, możemy określić MATCHBY , jak pokazano w poniższym równoważnym wyrażeniu:

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

Ponieważ określono MATCHBY, zarówno kolumny określone w MATCHBY, jak i te w PARTITIONBY są używane do unikatowego identyfikowania wierszy. Ponieważ kolor jest określony zarówno w MATCHBY, jak i w PARTITIONBY, następujące wyrażenie jest równoważne poprzedniego wyrażenia.

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

Ponieważ określenie MATCHBY nie jest konieczne w przykładach, które omówiliśmy do tej pory, przyjrzyjmy się nieco innego przykładowi, który wymaga MATCHBY. W tym przypadku mamy listę wierszy zamówień. Każdy wiersz reprezentuje pozycję zamówienia. Zamówienie może zawierać wiele pozycji, a pozycja 1 pojawia się w wielu zamówieniach. Ponadto dla każdej pozycji zamówienia mamy klucz produktu i kwotę sprzedaży. Przykład odpowiednich kolumn w tabeli wygląda następująco:

numer zamówienia sprzedaży	NumerLiniiZamówieniaSprzedaży	ProductKey (Klucz produktu)	KwotaSprzedaży
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

Należy zauważyć, że kolumny SalesOrderNumber i SalesOrderLineNumber są wymagane, aby unikatowo identyfikować wiersze.

Dla każdego zamówienia chcemy zwrócić poprzednią kwotę sprzedaży dla tego samego produktu (reprezentowanego przez klucz produktu), uporządkowaną według kwoty sprzedaży w kolejności malejącej. Następujące wyrażenie nie będzie działać, ponieważ w vRelation potencjalnie znajduje się wiele wierszy, gdy jest ono przekazywane do elementu OFFSET.

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

To wyrażenie zwraca błąd: "OFFSETParametr Relation może mieć zduplikowane wiersze, które nie są dozwolone".

Aby to wyrażenie działało, MATCHBY należy określić i musi zawierać wszystkie kolumny, które jednoznacznie definiują wiersz. MATCHBY jest wymagany w tym miejscu, ponieważ relacja FactInternetSales nie zawiera żadnych jawnych kluczy ani unikatowych kolumn. Kolumny SalesOrderNumber i SalesOrderLineNumber tworzą klucz złożony, gdzie ich istnienie razem jest unikatowe w relacji i w związku z tym może jednoznacznie identyfikować każdy wiersz. Samo określenie SalesOrderNumber lub SalesOrderLineNumber nie jest wystarczające, gdyż obie kolumny zawierają powtarzające się wartości. Następujące wyrażenie rozwiązuje problem:

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

To wyrażenie rzeczywiście zwraca wyniki, których szukamy.

NumerZamówieniaSprzedaży	NumerWierszaZamówieniaSprzedaży	ProductKey (Klucz produktu)	KwotaSprzedaży	Poprzednia kwota sprzedaży
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

Powiązana treść

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER