Κατανόηση των συναρτήσεων ORDERBY, PARTITIONBYκαι MATCHBY

Οι συναρτήσεις ORDERBY, PARTITIONBYκαι MATCHBY στο DAX είναι ειδικές συναρτήσεις που μπορούν να χρησιμοποιηθούν μόνο μαζί με συναρτήσεις DAX Window: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Η κατανόηση των ORDERBY, PARTITIONBYκαι MATCHBY είναι σημαντική για την επιτυχή χρήση των συναρτήσεων Window. Τα παραδείγματα που παρέχονται εδώ χρησιμοποιούν OFFSET, αλλά ισχύουν παρόμοια με τις άλλες συναρτήσεις Window.

Σενάριο

Ας ξεκινήσουμε με ένα παράδειγμα που δεν χρησιμοποιεί καθόλου συναρτήσεις window. Παρακάτω εμφανίζεται ένας πίνακας που επιστρέφει τις συνολικές πωλήσεις, ανά χρώμα, ανά ημερολογιακό έτος. Υπάρχουν πολλοί τρόποι για να ορίσετε αυτόν τον πίνακα, αλλά δεδομένου ότι μας ενδιαφέρει να κατανοήσουμε τι συμβαίνει σε DAX, θα χρησιμοποιήσουμε έναν υπολογιζόμενο πίνακα. Αυτή είναι η παράσταση πίνακα:

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

Θα δείτε ότι αυτή η παράσταση υπολογιζόμενου πίνακα χρησιμοποιεί SUMMARIZECOLUMNS για τον υπολογισμό της SUM της στήλης SalesAmount στον πίνακα FactInternetSales, από τη στήλη Χρώμα από τον πίνακα DimProduct και τη στήλη CalendarYear από τον πίνακα DimDate. Δείτε το αποτέλεσμα:

Τώρα, ας φανταστούμε ότι προσπαθούμε να λύσουμε το επιχειρηματικό ζήτημα του υπολογισμού της διαφοράς στις πωλήσεις από έτος σε έτος για κάθε χρώμα. Ουσιαστικά, χρειαζόμαστε έναν τρόπο να βρούμε τις πωλήσεις για το ίδιο χρώμα το προηγούμενο έτος και να τις αφαιρέσουμε από τις πωλήσεις κατά το τρέχον έτος, στο περιβάλλον. Για παράδειγμα, για τον συνδυασμό [Red, 2019] αναζητούμε πωλήσεις για [Red, 2018]. Μόλις αποκτήσουμε αυτό το στοιχείο, μπορούμε να το αφαιρέσουμε από τις τρέχουσες πωλήσεις και να επιστρέψουμε την απαιτούμενη τιμή.

Χρήση OFFSET

OFFSET είναι ιδανική για τις τυπικές συγκρίνετε με προηγούμενες τους τύπους υπολογισμών που απαιτούνται για την απάντηση στην επιχειρηματική ερώτηση που περιγράφεται παραπάνω, καθώς μας επιτρέπει να κάνουμε μια σχετική κίνηση. Η πρώτη μας προσπάθεια μπορεί να είναι:

1stAttempt = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation
            ),
            [CurrentYearSales]
        )
    )

Πολλά συμβαίνουν με αυτή την παράσταση. Χρησιμοποιήσαμε ADDCOLUMNS για να αναπτύξουμε τον πίνακα από πριν με μια στήλη που ονομάζεται PreviousColorSales. Τα περιεχόμενα αυτής της στήλης ορίζονται στο CurrentYearSales, το οποίο είναι SUM(FactInternetSales[SalesAmount]), για το προηγούμενο Χρώμα (που ανακτήθηκε χρησιμοποιώντας OFFSET).

Το αποτέλεσμα είναι:

Αυτό είναι ένα βήμα πιο κοντά στον στόχο μας, αλλά αν κοιτάξουμε προσεκτικά δεν ταιριάζει ακριβώς με αυτό που κυνηγάμε. Για παράδειγμα, για το [Silver, 2017] η PreviousColorSales έχει οριστεί σε [Red, 2020].

Προσθήκη ORDERBY

Αυτός ο ορισμός παραπάνω ισοδυναμεί με:

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

Στην περίπτωση αυτή, η κλήση σε OFFSET χρησιμοποιεί ORDERBY για να διατάξει τον πίνακα κατά Color και CalendarYear σε αύξουσα σειρά, το οποίο καθορίζει τι θεωρείται η προηγούμενη γραμμή που επιστρέφεται.

Ο λόγος που αυτά τα δύο αποτελέσματα είναι ισοδύναμα είναι επειδή ORDERBY περιέχει αυτόματα όλες τις στήλες από τη σχέση που δεν βρίσκονται σε PARTITIONBY. Δεδομένου ότι δεν καθορίστηκε PARTITIONBY, ORDERBY ορίζεται σε Χρώμα, CalendarYear και CurrentYearSales. Ωστόσο, δεδομένου ότι τα ζεύγη Χρώμα και CalendarYear στη σχέση είναι μοναδικά, η προσθήκη του CurrentYearSales δεν αλλάζει το αποτέλεσμα. Στην πραγματικότητα, ακόμα και αν καθορίσουμε το Χρώμα μόνο σε ORDERBY, τα αποτελέσματα είναι τα ίδια, δεδομένου ότι το CalendarYear θα προστεθεί αυτόματα. Αυτό συμβαίνει επειδή η συνάρτηση θα προσθέσει όσες στήλες χρειάζεται για να ORDERBY προκειμένου να εξασφαλιστεί ότι κάθε γραμμή μπορεί να αναγνωριστεί μοναδικά από τις ORDERBY και 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]
        )
    )

Προσθήκη PARTITIONBY

Τώρα, για να σχεδόν λάβουμε το αποτέλεσμα, μπορούμε να χρησιμοποιήσουμε PARTITIONBY, όπως φαίνεται στην παρακάτω παράσταση υπολογιζόμενου πίνακα:

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

Παρατηρήστε ότι ο καθορισμός ORDERBY είναι προαιρετικός εδώ, επειδή η ORDERBY περιέχει αυτόματα όλες τις στήλες από τη σχέση που δεν καθορίζονται στο PARTITIONBY. Επομένως, η ακόλουθη παράσταση επιστρέφει τα ίδια αποτελέσματα καθώς ORDERBY ορίζεται αυτόματα σε CalendarYear και 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]
        )
    )

Και οι δύο παραστάσεις επιστρέφουν το αποτέλεσμα που κυνηγάμε:

Όπως βλέπετε σε αυτόν τον πίνακα, η στήλη PreviousYearSalesForSameColor εμφανίζει τις πωλήσεις για το προηγούμενο έτος για το ίδιο χρώμα. Για το [Red, 2020], επιστρέφει τις πωλήσεις για [Red, 2019] και ούτω καθεξής. Εάν δεν υπάρχει προηγούμενο έτος, για παράδειγμα, στην περίπτωση του [Red, 2017], δεν επιστρέφεται καμία τιμή.

Μπορείτε να θεωρήσετε PARTITIONBY ως έναν τρόπο διαίρεσης του πίνακα σε τμήματα στα οποία θα εκτελεστεί ο OFFSET υπολογισμός. Στο παραπάνω παράδειγμα, ο πίνακας διαιρείται σε όσα μέρη υπάρχουν χρώματα, ένα για κάθε χρώμα. Στη συνέχεια, μέσα σε κάθε τμήμα, η OFFSET υπολογίζεται, ταξινομημένη κατά CalendarYear.

Οπτικά, αυτό που συμβαίνει είναι το εξής:

ημερολογιακού έτους

Πρώτα, η κλήση για PARTITIONBY έχει ως αποτέλεσμα ο πίνακας να διαιρεθεί σε τμήματα, ένα για κάθε Χρώμα. Αυτό αντιπροσωπεύεται από τα ανοιχτό μπλε πλαίσια στην εικόνα πίνακα. Στη συνέχεια, ORDERBY εξασφαλίζει ότι κάθε τμήμα ταξινομείται κατά CalendarYear (αντιπροσωπεύεται από τα πορτοκαλί βέλη). Τέλος, μέσα σε κάθε ταξινομημένο τμήμα, για κάθε γραμμή, OFFSET εντοπίζει τη γραμμή επάνω από αυτή και επιστρέφει αυτή την τιμή στη στήλη PreviousYearSalesForSameColor. Δεδομένου ότι για κάθε πρώτη γραμμή σε κάθε τμήμα δεν υπάρχει προηγούμενη γραμμή στο ίδιο τμήμα, το αποτέλεσμα σε αυτή τη γραμμή για τη στήλη PreviousYearSalesForSameColor είναι κενό.

Για να επιτύχουμε το τελικό αποτέλεσμα, πρέπει απλώς να αφαιρέσουμε το CurrentYearSales από τις πωλήσεις του προηγούμενου έτους για το ίδιο χρώμα που επιστρέφεται από την κλήση στο OFFSET. Δεδομένου ότι δεν μας ενδιαφέρει να εμφανίσουμε τις πωλήσεις του προηγούμενου έτους για το ίδιο χρώμα, αλλά μόνο στις πωλήσεις του τρέχοντος έτους και τη διαφορά έτους κατά έτος. Αυτή είναι η τελική παράσταση υπολογιζόμενου πίνακα:

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

Και αυτό είναι το αποτέλεσμα αυτής της παράστασης:

Χρήση MATCHBY

Ίσως παρατηρήσατε ότι δεν καθορίσαμε καθόλου MATCHBY. Σε αυτή την περίπτωση, δεν είναι απαραίτητο. Οι στήλες σε ORDERBY και PARTITIONBY (στο βαθμό που έχουν καθοριστεί στα παραπάνω παραδείγματα) επαρκούν για τη μοναδική αναγνώριση κάθε γραμμής. Δεδομένου ότι δεν καθορίσαμε MATCHBY, οι στήλες που καθορίζονται σε ORDERBY και PARTITIONBY χρησιμοποιούνται για τη μοναδική αναγνώριση κάθε γραμμής, ώστε να μπορούν να συγκριθούν για να επιτρέψουν OFFSET να δώσουν ένα ουσιαστικό αποτέλεσμα. Εάν οι στήλες σε ORDERBY και PARTITIONBY δεν μπορούν να αναγνωρίσουν μοναδικά κάθε γραμμή, μπορούν να προστεθούν επιπλέον στήλες στον όρο ORDERBY εάν αυτές οι επιπλέον στήλες επιτρέπουν τη μοναδική αναγνώριση κάθε γραμμής. Εάν αυτό δεν είναι δυνατό, επιστρέφεται ένα σφάλμα. Στην τελευταία περίπτωση, ο καθορισμός MATCHBY μπορεί να βοηθήσει στην επίλυση του σφάλματος.

Εάν καθοριστεί MATCHBY, οι στήλες σε MATCHBY και PARTITIONBY χρησιμοποιούνται για τη μοναδική αναγνώριση κάθε γραμμής. Εάν αυτό δεν είναι δυνατό, επιστρέφεται ένα σφάλμα. Ακόμα και αν δεν απαιτούνται MATCHBY, εξετάστε το ενδεχόμενο να καθορίσετε ρητά MATCHBY για να αποφύγετε οποιαδήποτε σύγχυση.

Συνεχίζοντας από τα παραπάνω παραδείγματα, αυτή είναι η τελευταία παράσταση:

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

Εάν θέλουμε να είμαστε σαφείς σχετικά με τον τρόπο με τον οποίο οι γραμμές πρέπει να προσδιορίζονται μοναδικά, μπορούμε να καθορίσουμε MATCHBY, όπως φαίνεται στην παρακάτω ισοδύναμη παράσταση:

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

Δεδομένου ότι καθορίζεται MATCHBY, αμφότερες οι στήλες που καθορίζονται στο MATCHBY καθώς και σε PARTITIONBY χρησιμοποιούνται για τη μοναδική αναγνώριση των γραμμών. Δεδομένου ότι το Color καθορίζεται τόσο στο MATCHBY όσο και στο PARTITIONBY, η ακόλουθη παράσταση ισοδυναμεί με την προηγούμενη παράσταση:

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

Δεδομένου ότι ο καθορισμός MATCHBY δεν είναι απαραίτητος στα παραδείγματα που εξετάσαμε μέχρι στιγμής, ας δούμε ένα ελαφρώς διαφορετικό παράδειγμα που απαιτεί MATCHBY. Σε αυτή την περίπτωση, έχουμε μια λίστα γραμμών παραγγελιών. Κάθε γραμμή αντιπροσωπεύει μια γραμμή παραγγελίας για μια παραγγελία. Μια παραγγελία μπορεί να έχει πολλές γραμμές παραγγελιών και η γραμμή παραγγελίας 1 εμφανίζεται σε πολλές παραγγελίες. Επιπλέον, για κάθε γραμμή παραγγελίας έχουμε ένα ProductKey και ένα SalesAmount. Ένα δείγμα των σχετικών στηλών στον πίνακα μοιάζει κάπως έτσι:

Παρατηρήστε ότι οι SalesOrderNumber και SalesOrderLineNumber απαιτούνται και οι δύο για τη μοναδική αναγνώριση των γραμμών.

Για κάθε παραγγελία, θέλουμε να επιστρέψουμε το προηγούμενο ποσό πωλήσεων του ίδιου προϊόντος (αντιπροσωπεύεται από το ProductKey) που έχει παραγγελθεί από το SalesAmount σε φθίνουσα σειρά. Η ακόλουθη παράσταση δεν θα λειτουργήσει, επειδή πιθανώς υπάρχουν πολλές γραμμές στη σχέση vRelation καθώς μεταβιβάζονται στο OFFSET:

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

Αυτή η παράσταση επιστρέφει ένα σφάλμα: "OFFSETπαράμετρος Relation του μπορεί να έχει διπλότυπες γραμμές, το οποίο δεν επιτρέπεται."

Προκειμένου αυτή η παράσταση να λειτουργήσει, MATCHBY πρέπει να καθοριστεί και πρέπει να περιλαμβάνει όλες τις στήλες που ορίζουν με μοναδικό τρόπο μια γραμμή. MATCHBY απαιτείται εδώ καθώς η σχέση, FactInternetSales, δεν περιέχει ρητά κλειδιά ή μοναδικές στήλες. Ωστόσο, οι στήλες SalesOrderNumber και SalesOrderLineNumber μαζί σχηματίζουν ένα σύνθετο κλειδί, όπου η ύπαρξή τους είναι μοναδική στη σχέση και, επομένως, μπορούν να αναγνωρίσουν μοναδικά κάθε γραμμή. Η απλή καθορισμός των SalesOrderNumber ή SalesOrderLineNumber δεν είναι αρκετή, καθώς και οι δύο στήλες περιέχουν επαναλαμβανόμενες τιμές. Η ακόλουθη παράσταση επιλύει το πρόβλημα:

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

Και αυτή η παράσταση όντως επιστρέφει τα αποτελέσματα που κυνηγάμε:

Σχετικό περιεχόμενο

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER