Opis funkcji ORDERBY, PARTITIONBY i MATCHBY

Funkcje ORDERBY, PARTITIONBY i MATCHBY w języku DAX to specjalne funkcje, których można używać tylko wraz z funkcjami okna języka DAX: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Zrozumienie funkcji ORDERBY, PARTITIONBY i MATCHBY ma kluczowe znaczenie dla pomyślnego korzystania z funkcji Okna. W podanych tutaj przykładach użyto funkcji 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ż, kolor, rok kalendarzowy. Istnieje wiele sposobów definiowania tej tabeli, ale ponieważ interesuje nas zrozumienie tego, co się dzieje w języku DAX, 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 funkcji SUMMARIZECOLUMNS do obliczania sumy kolumny SalesAmount w tabeli FactInternetSales według kolumny Color z tabeli DimProduct oraz kolumny CalendarYear z tabeli DimDate. Oto wynik:

Color	CalendarYear	CurrentYearSales
"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
"Silver"	2017	326399
"Silver"	2018	750026
"Silver"	2019	2165176
"Silver"	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 w przypadku kombinacji [Red, 2019] szukamy sprzedaży [Red, 2018]. Gdy to zrobimy, możemy odjąć ją od bieżącej sprzedaży i zwrócić wymaganą wartość.

Korzystanie z funkcji OFFSET

OFFSET jest idealny do typowego porównania z poprzednimi typami obliczeń wymaganych do odpowiedzi na pytanie biznesowe opisane powyżej, ponieważ pozwala nam to zrobić względny ruch. 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 dzieje się z tym wyrażeniem. Użyliśmy funkcji ADDCOLUMNS , aby rozwinąć tabelę z wcześniejszej kolumny o nazwie PreviousColorSales. Zawartość tej kolumny jest ustawiona na wartość CurrentYearSales( SUM(FactInternetSales[SalesAmount]) dla poprzedniego koloru (pobranego przy użyciu przesunięcia).

Wynik to:

Color	CalendarYear	CurrentYearSales	PreviousColorSales
"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
"Silver"	2017	326399	176022
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	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ę bliżej, nie pasuje dokładnie do tego, co jesteśmy po. Na przykład dla elementu [Silver, 2017] właściwość PreviousColorSales jest ustawiona na [Red, 2020].

Dodawanie elementu 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 funkcji OFFSET używa funkcji ORDERBY w celu uporządkowania tabeli według wartości Color i CalendarYear w kolejności rosnącej, co określa, co jest uznawane za zwrócony poprzedni wiersz.

Powodem, dla którego te dwa wyniki są równoważne, jest to, że funkcja ORDERBY automatycznie zawiera wszystkie kolumny z relacji, które nie znajdują się w kolumnie PARTITIONBY. Ponieważ parametr PARTITIONBY nie został określony, właściwość ORDERBY jest ustawiona na Wartość Color(Kolor), CalendarYear (Rok kalendarzowy) i CurrentYearSales (Sprzedaż bieżąca). 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 dowolną liczbę kolumn do kolumn 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 elementu PARTITIONBY

Teraz, aby uzyskać prawie wynik, po jakim możemy użyć funkcji 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 parametru ORDERBY jest tutaj opcjonalne, ponieważ funkcja ORDERBY automatycznie zawiera wszystkie kolumny z relacji, które nie są określone w parametrze PARTITIONBY. Dlatego następujące wyrażenie zwraca te same wyniki, ponieważ właściwość ORDERBY jest ustawiona na Wartość 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

Podczas gdy właściwość ORDERBY jest ustawiona na Wartość CalendarYear i CurrentYearSales automatycznie, żadna gwarancja nie jest udzielana co do kolejności, w jakiej zostaną dodane. Jeśli wartość CurrentYearSales zostanie dodana przed kolumną CalendarYear, wynikowa kolejność nie jest wbudowana w oczekiwaną wartość. Należy jawnie określać kolumny ORDERBY i PARTITIONBY, aby uniknąć nieporozumień i nieoczekiwanych wyników.

Oba wyrażenia zwracają wynik, po których jesteśmy:

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"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
"Silver"	2017	326399
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	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 sprzedaż dla [Red, 2019] itd. Jeśli nie ma poprzedniego roku, na przykład w przypadku [Red, 2017], żadna wartość nie jest zwracana.

Partycje PARTITIONBY można traktować jako sposób dzielenia tabeli na części, w których ma zostać wykonane obliczenie PRZESUNIĘCIA. 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 obliczane jest przesunięcie posortowane według kolumny CalendarYear.

Wizualnie, co się dzieje, to:

Najpierw wywołanie metody PARTITIONBY powoduje podzielenie tabeli na części, po jednym dla każdego koloru. Jest to reprezentowane przez jasnoniebieskie pola na obrazie tabeli. Następnie funkcja ORDERBY upewnia się, że każda część jest sortowana według kolumny CalendarYear (reprezentowana przez pomarańczowe strzałki). Na koniec w każdej posortowanej części dla każdego wiersza funkcja OFFSET znajduje nad nim wiersz 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ąć końcowy wynik, musimy po prostu odjąć sprzedaż CurrentYearSales z poprzedniego roku sprzedaży dla tego samego koloru zwróconego przez wywołanie funkcji OFFSET. Ponieważ nie jesteśmy zainteresowani pokazanie sprzedaży w poprzednim roku dla tego samego koloru, ale tylko w bieżącym roku sprzedaży i roku w porównaniu z rokiem. 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:

Color	CalendarYear	CurrentYearSales	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
"Silver"	2017	326399	326399
"Silver"	2018	750026	423627
"Silver"	2019	2165176	1415150
"Silver"	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 funkcji MATCHBY

Być może zauważyliśmy, że w ogóle nie określiliśmy ELEMENTU MATCHBY. W takim przypadku nie jest to konieczne. Kolumny w kolumnach 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 funkcji MATCHBY, kolumny określone w kolumnach ORDERBY i PARTITIONBY są używane do unikatowego identyfikowania każdego wiersza, dzięki czemu można je porównać w celu włączenia funkcji OFFSET w celu uzyskania znaczącego wyniku. Jeśli kolumny w kolumnach ORDERBY i PARTITIONBY nie mogą jednoznacznie zidentyfikować każdego wiersza, dodatkowe kolumny można dodać do klauzuli ORDERBY, jeśli te dodatkowe kolumny zezwalają na unikatowe zidentyfikowanie każdego wiersza. Jeśli to nie jest możliwe, zostanie zwrócony błąd. W tym ostatnim przypadku określenie funkcji MATCHBY może pomóc w rozwiązaniu błędu.

Jeśli parametr MATCHBY zostanie określony, kolumny w kolumnach 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 funkcja MATCHBY nie jest wymagana, rozważ jawne określenie FUNKCJI 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ć funkcję MATCHBY, jak pokazano w poniższym wyrażeniu równoważnym:

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ż funkcja MATCHBY jest określona, zarówno kolumny określone w funkcji MATCHBY, jak i w kolumnie PARTITIONBY są używane do unikatowego identyfikowania wierszy. Ponieważ kolor jest określony zarówno w FUNKCJI MATCHBY, jak i PARTITIONBY, następujące wyrażenie jest równoważne poprzedniemu wyrażeniu:

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 FUNKCJI MATCHBY nie jest konieczne w przykładach, które omówiliśmy do tej pory, przyjrzyjmy się nieco innego przykładowi, który wymaga funkcji MATCHBY. W tym przypadku mamy listę wierszy kolejności. Każdy wiersz reprezentuje wiersz zamówienia dla zamówienia. Zamówienie może zawierać wiele wierszy zamówienia i wiersz zamówienia 1 wyświetlanych w wielu zamówieniach. Ponadto dla każdego wiersza zamówienia mamy wartość ProductKey i SalesAmount. Przykład odpowiednich kolumn w tabeli wygląda następująco:

SalesOrderNumber (Numer zamówienia sprzedaży)	SalesOrderLineNumber	ProductKey (Klucz produktu)	SalesAmount (Kwota sprzedaż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

Zwróć uwagę, że kolumny SalesOrderNumber i SalesOrderLineNumber są wymagane do unikatowego identyfikowania wierszy.

Dla każdego zamówienia chcemy zwrócić poprzednią kwotę sprzedaży tego samego produktu (reprezentowanego przez ProductKey) uporządkowanego przez salesAmount w kolejności malejącej. Następujące wyrażenie nie będzie działać, ponieważ istnieje potencjalnie wiele wierszy w relacji wirtualnej, ponieważ jest ono przekazywane do funkcji 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: "Parametr relacyjny przesunięcia może mieć zduplikowane wiersze, które nie są dozwolone".

Aby to wyrażenie działało, należy określić element MATCHBY i musi zawierać wszystkie kolumny, które jednoznacznie definiują wiersz. Funkcja MATCHBY jest wymagana w tym miejscu, ponieważ relacja FactInternetSales nie zawiera żadnych jawnych kluczy ani unikatowych kolumn. Kolumny SalesOrderNumber i SalesOrderLineNumber tworzą jednak klucz złożony, w którym ich istnienie jest unikatowe w relacji i w związku z tym może jednoznacznie identyfikować każdy wiersz. Wystarczy określić kolumny SalesOrderNumber lub SalesOrderLineNumber, ponieważ 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, po których jesteśmy:

SalesOrderNumber (Numer zamówienia sprzedaży)	SalesOrderLineNumber	ProductKey (Klucz produktu)	SalesAmount (Kwota sprzedaż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 zawartość

ORDERBY
PARTYCJONOWANIE WG
MATCHBY
INDEKS
PRZESUNIĘCIE
OKNO
RANK
ROWNUMBER