Udostępnij za pośrednictwem


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:

Table showing OFFSET by Calendar Year

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

ORDERBY
PARTYCJONOWANIE WG
MATCHBY
INDEKS
PRZESUNIĘCIE
OKNO
RANK
ROWNUMBER