A ORDERBY, PARTITIONBY, andMATCHBY függvények ismertetése

A DAXORDERBY, PARTITIONBY, andMATCHBY függvényei olyan speciális függvények, amelyek csak DAXWindow függvényekkel együtt használhatók: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

A ORDERBY, PARTITIONBY, andMATCHBY megértése kritikus fontosságú a Window függvények sikeres használatához. Az itt szereplő példák OFFSEThasználnak, de a többi Window függvényre is érvényesek.

Forgatókönyv

Kezdjük egy példával, amely nem Window függvényeket használ a all. Az alábbiakban egy táblázat látható, amely a teljes értékesítést adja vissza színenként, calendaryear. Ezt a táblázatot többféleképpen is define, de mivel szeretnénk megérteni, hogy mi történik DAX, egy számított táblát használunk. A táblakifejezés a következő:

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

Ez a számított táblakifejezés a FactInternetSales tábla SalesAmount oszlopának SUM végrehajtásához használja az SUMMARIZECOLUMNS és calculate kifejezéseket, a DimProduct tábla Szín oszlopát figyelembe véve, valamint a DimDate tábla CalendarYear oszlopát is and. Az eredmény a következő:

Now, tegyük fel, hogy megpróbáljuk megoldani az értékesítési különbség kiszámításának üzleti kérdését, year-over-year minden színhez. Gyakorlatilag módot kell keresnünk arra, hogy find az azonos színű értékesítéseket a previousyearand kivonjuk az aktuális yearértékesítéséből a kontextusban. Például a [Red, 2019] kombináció esetén a [Red, 2018] értékesítéseit keressük. Ha ez megvan, kivonhatjuk az aktuális értékesítésből, és and visszaadjuk a szükséges value.

A OFFSET használata

OFFSET tökéletes a tipikus összehasonlításhoz szükséges previous típusú számításokra, amelyek szükségesek a fent leírt üzleti kérdés megválaszolásához, mivel lehetővé teszi számunkra, hogy relatív mozgást hajtsunk végre. A mi first kísérletünk a következő lehet:

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

Sok minden történik ezzel a kifejezéssel. Korábban használtuk a táblázat ADDCOLUMNS és expand módosítására egy PreviousColorSales nevű oszloppal. Az oszlop tartalma a CurrentYearSales értékre van állítva, amely SUM(FactInternetSales[SalesAmount]) a previous színhez (OFFSEThasználatával kérhető le).

Az eredmény a következő:

Ez egy lépéssel közelebb áll a célunkhoz, de if alaposan megnézzük, nem egyezik pontosan azzal, amit keresünk. A [Silver, 2017] esetében például a PreviousColorSales értéke [Piros, 2020].

ORDERBY hozzáadása

Ez a fenti definíció a következőnek felel meg:

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

Ebben az esetben a OFFSET hívása ORDERBY használatával rendezi a táblázatot a Color and CalendarYear sorrendben növekvő sorrendben, amely meghatározza, hogy mi tekinthető a visszaadott previous sornak.

Ennek a két eredménynek az az oka, hogy ORDERBY automatikusan containsall oszlopokat a PARTITIONBYnem szereplő relációból. Mivel PARTITIONBY nincs megadva, a ORDERBY a Color (Naptárév) értékre van állítva, and CurrentYearSales értékre. Mivel azonban a relációban a Color and CalendarYear párok egyediek, a CurrentYearSales hozzáadása nem változtatja meg az eredményt. A fact, evenif-ben az volt a feladat, hogy csak a színt adjuk meg ORDERBY-ban, az eredmények megegyeznek, mivel a CalendarYear automatikusan hozzáadódik. Ennek az az oka, hogy a függvény annyi oszlopot ad hozzá a ORDERBY-hoz, amennyi szükséges annak érdekében, hogy az egyes sorokat egyedileg azonosíthassa a ORDERBYandPARTITIONBY oszlopok alapján.

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 hozzáadása

, hogy majdnem megkapjuk a után kapott eredményt, ahogyan az alábbi számított táblakifejezésben látható:

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

Vegye észre, hogy a ORDERBY megadása itt nem kötelező, mert a ORDERBY automatikusan containsall a reláció azon oszlopait, amelyek nincsenek megadva a PARTITIONBY-ben. A következő kifejezés tehát ugyanazokat az eredményeket adja vissza, mert a ORDERBY a CalendarYear and CurrentYearSales értékre van állítva automatikusan:

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

Mindkét kifejezés a következő eredményt adja vissza:

Ahogy ebben a táblázatban látható, a PreviousYearSalesForSameColor oszlop az azonos színű previousyear értékesítéseit jeleníti meg. A [Red, 2020] esetében a [Red, 2019] értékesítéseit adja vissza, and és így tovább. If nincs previousyear, például a [Red, 2017] esetében, nincs value visszaadva.

Úgy tekinthet a(z) PARTITIONBY-ra, mint a táblázat olyan részekre divide-ére, amelyekben végrehajthatja a(z) OFFSET számítást. A fenti példában a táblázat annyi részre van osztva, amennyi szín van, egy-egy színre. Ezután minden egyes részben a OFFSET kiszámításra kerül, majd a naptári év szerint rendezve lesz.

Vizuálisan a következő történik:

táblázat, amely CalendarYear szerint mutatja a -t

First, a PARTITIONBY hívása azt eredményezi, hogy a táblázat részekre van osztva, egy-egy színre. Ezt a táblázat képének világoskék mezői jelölik. Next, ORDERBY gondoskodik arról, hogy az egyes részeket naptárév szerint rendezze (a narancssárga nyilak jelölik). Végül a rendezett részek mindegyikében, minden sor esetén, OFFSET megkeresi a fölötte lévő sort, majd and ezt visszaadja a PreviousYearSalesForSameColor oszlopban value. Mivel minden first sornál nincs previous sor ugyanabban a részben, a PreviousYearSalesForSameColor oszlopban lévő eredmény üres.

A végső eredmény eléréséhez egyszerűen ki kell vonnunk a CurrentYearSales-t a previousyear azon színre vonatkozó értékesítéséből, amelyet a OFFSEThívás adott vissza. Mivel not szeretnénk az previousyear értékesítéseket ugyanahhoz a színhez, de csak az aktuális year értékesítésekben and a year különbséget year. Íme az utolsó számított táblakifejezés:

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 itt van ennek a kifejezésnek az eredménye:

A MATCHBY használata

Észrevehette, hogy nem adtuk meg MATCHBY a all-nél. Ebben az esetben nem szükséges. A ORDERBYandPARTITIONBY oszlopai (amennyiben a fenti példákban meg vannak adva) elegendőek az egyes sorok egyedi azonosításához. Mivel nem specifikáltunk MATCHBY-t, a ORDERBYandPARTITIONBY megadott oszlopok a sorok egyedi azonosítására szolgálnak, így azok összehasonlíthatók, hogy OFFSET jelentős eredményt adjon. If a ORDERBYandPARTITIONBY oszlopai nem azonosítják egyedileg az egyes sorokat, további oszlopok is hozzáadhatók a ORDERBY záradékhoz, if ezek a további oszlopok lehetővé teszik az egyes sorok egyedi azonosítását. If lehetséges not egy error ad vissza. Ebben a last esetben a MATCHBY megadása segíthet a errormegoldásában.

If MATCHBY meg van adva, a MATCHBYandPARTITIONBY oszlopai az egyes sorok egyedi azonosítására szolgálnak. If ha lehetséges, not egyet error ad vissza. Even if MATCHBY nem szükséges, fontolja meg a MATCHBY explicit megadását a félreértések elkerülése érdekében.

A fenti példákat folytatva az alábbi last kifejezés látható:

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 szeretnénk explicit módon meghatározni a sorok egyedi azonosítását, MATCHBY az alábbi egyenértékű kifejezésben látható módon adhatjuk meg:

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

Mivel MATCHBY van megadva, a MATCHBY és a PARTITIONBY megadott oszlopok egyaránt a sorok egyedi azonosítására szolgálnak. Mivel a szín a MATCHBYandPARTITIONBYmindegyikében meg van adva, a következő kifejezés egyenértékű a previous kifejezéssel:

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

Mivel a MATCHBY megadása nem szükséges az eddig megvizsgált példákban, nézzünk meg egy kicsit más példát, amely MATCHBYigényel. Ebben az esetben a rendelési sorok listája van. Minden sor egy rendeléssort jelöl. A rendelések több rendeléssort is tartalmazhatnak, and 1. rendeléssor számos rendelésen megjelenik. Emellett minden rendeléssorhoz tartozik egy ProductKey and és egy SalesAmount. A táblázat megfelelő oszlopainak sample a következőképpen néz ki:

Figyelje meg, hogy a SalesOrderNumber and SalesOrderLineNumber mind a sorok egyedi azonosításához szükséges.

Minden rendeléshez a SalesAmount által csökkenő sorrendben megrendelt ugyanazon product (a ProductKey által képviselt) previous értékesítési összeget szeretnénk visszaadni. A következő kifejezés nem fog működni, mert a vRelationban több sor is lehet, mivel az át lett adva OFFSET:

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

Ez a kifejezés a error-át adja vissza: "AOFFSETkapcsolat paramétere ismétlődő sorokat tartalmazhat, ami nem megengedett."

A kifejezés működéséhez meg kell adni MATCHBY, andall oszlopokat kell tartalmaznia, amelyek egyedileg define egy sort. itt MATCHBY szükséges, mert a FactInternetSales reláció nem tartalmaz explicit kulcsokat or egyedi oszlopokhoz. A SalesOrderNumber and SalesOrderLineNumber oszlopok azonban egy összetett kulcsot alkotnak, ahol a relációban egyedi a létezésük, and ezért egyedien azonosíthatják az egyes sorokat. A SalesOrderNumber or SalesOrderLineNumber megadása nem elegendő, mivel mindkét oszlop ismétlődő valuestartalmaz. A következő kifejezés megoldja a problémát:

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 ez a kifejezés valóban a következő eredményeket adja vissza:

Related tartalom

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER