ORDERBY, PARTITIONBY, andMATCHBY funktioiden ymmärtäminen

DAX ORDERBY-ORDERBY, andMATCHBYPARTITIONBY, ovat erikoisfunktioita, joita voidaan käyttää vain DAXWindow funktioiden kanssa: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Window-funktioiden käyttämisen kannalta on tärkeää ymmärtää ORDERBY, PARTITIONBYandMATCHBY. Tässä esitetyissä esimerkeissä käytetään OFFSET, mutta niitä voidaan soveltaa vastaavasti muihin Window funktioihin.

Skenaario

Aloitetaan esimerkistä, jossa ei käytetä all:n Window funktioita. Alla on taulukko, joka palauttaa kokonaismyynnin väriä kohti calendaryearkohti. Tätä taulukkoa voi define monella tavalla, mutta koska olemme kiinnostuneita siitä, mitä DAXtapahtuu, käytämme laskettua taulukkoa. Tässä on taulukkolauseke:

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

Näet tämän lasketun taulukon lausekkeen käyttävän SUMMARIZECOLUMNScalculate FactInternetSales-taulukon SalesAmount-sarakkeen SUM DimProduct-taulukon Color-sarakkeen and DimDate-taulukon CalendarYear-sarakkeen. Tässä on tulos:

Väri	Kalenterivuosi	CurrentYearSales
"Musta"	2017	393885
"Musta"	2018	1818835
"Musta"	2019	3981638
"Musta"	2020	2644054
"Sininen"	2019	994448
"Sininen"	2020	1284648
"Moni"	2019	48622
"Moni"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Punainen"	2017	2961198
"Punainen"	2018	3686935
"Punainen"	2019	900175
"Punainen"	2020	176022
"Hopeinen"	2017	326399
"Hopeinen"	2018	750026
"Hopeinen"	2019	2165176
"Hopeinen"	2020	1871788
"Valkoinen"	2019	2517
"Valkoinen"	2020	2589
"Keltainen"	2018	163071
"Keltainen"	2019	2072083
"Keltainen"	2020	2621602

Now, kuvitellaan, että yritämme ratkaista liiketoimintakysymyksen, joka koskee myyntieron laskemista year–year kullekin värille. Käytännössä tarvitset tavan find myynnin samalle värille previousyearand vähentää sen myynnistä nykyisessä yearkontekstissa. Esimerkiksi yhdistelmälle [Red, 2019] etsitään myyntiä tuotteille [Red, 2018]. Kun se on saatu, voimme vähentää sen nykyisestä myyntituloksista and palauttaa vaaditun value.

OFFSET käyttäminen

OFFSET sopii täydellisesti tyypillisiin vertailuun previous laskutoimitusten tyyppejä, joita tarvitaan edellä kuvattuun liiketoimintakysymykseen vastaamiseksi, koska se mahdollistaa suhteellisen liikkumisen. first yrityksemme voi olla:

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

Tällä lausekkeella tapahtuu paljon. ADDCOLUMNS avulla expand taulukon aiempaan sarakkeeseen nimeltä PreviousColorSales. Kyseisen sarakkeen sisällöksi määritetään CurrentYearSales, joka on SUM(FactInternetSales[SalesAmount]) previous Color -arvolle (noudettu käyttämällä OFFSET).

Tulos on:

Väri	Kalenterivuosi	CurrentYearSales	PreviousColorSales
"Musta"	2017	393885
"Musta"	2018	1818835	393885
"Musta"	2019	3981638	1818835
"Musta"	2020	2644054	3981638
"Sininen"	2019	994448	2644054
"Sininen"	2020	1284648	994448
"Moni"	2019	48622	1284648
"Moni"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Punainen"	2017	2961198	227295
"Punainen"	2018	3686935	2961198
"Punainen"	2019	900175	3686935
"Punainen"	2020	176022	900175
"Hopeinen"	2017	326399	176022
"Hopeinen"	2018	750026	326399
"Hopeinen"	2019	2165176	750026
"Hopeinen"	2020	1871788	2165176
"Valkoinen"	2019	2517	1871788
"Valkoinen"	2020	2589	2517
"Keltainen"	2018	163071	2589
"Keltainen"	2019	2072083	163071
"Keltainen"	2020	2621602	2072083

Tämä on askeleen lähempänä tavoitettamme, mutta if katsomme tarkasti, se ei vastaa tarkalleen mitä haluamme. Esimerkiksi kaavalle [Silver, 2017] PreviousColorSales on määritetty arvoksi [Red, 2020].

ORDERBY lisääminen

Edellä oleva määritelmä vastaa:

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

Tässä tapauksessa OFFSET kutsu käyttää ORDERBY taulukon järjestämiseen Kalenterivuosi-and mukaan nousevassa järjestyksessä, mikä määrittää, mitä pidetään palautetun previous rivinä.

Nämä kaksi tulosta vastaavat toisiaan, koska ORDERBYcontainsall automaattisesti suhteen sarakkeet, jotka eivät ole PARTITIONBY. Koska PARTITIONBY ei määritetty, ORDERBY arvoksi asetetaan Color, CalendarYear and CurrentYearSales. Koska Kalenterivuosi-and Väri-parit ovat yksilöllisiä, CurrentYearSales-sarakkeen lisääminen ei muuta tulosta. fact even if, että väri määritetään vain ORDERBY, tulokset ovat samat, koska Kalenterivuosi lisätään automaattisesti. Tämä johtuu siitä, että funktio lisää niin monta saraketta kuin tarvitaan ORDERBY, jotta ORDERBYandPARTITIONBY-sarakkeet voivat tunnistaa kunkin rivin yksilöllisesti:

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 lisääminen

Now, melkein saamme tulokseksi sen, minkä jälkeen voimme käyttää PARTITIONBY, seuraavan lasketun taulukkolausekkeen mukaisesti:

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

Huomaa, että ORDERBY määrittäminen on tässä valinnaista, koska ORDERBYcontainsall automaattisesti sarakkeet suhteesta, jota ei ole määritetty PARTITIONBY. Seuraava lauseke palauttaa siis samat tulokset, koska ORDERBY on määritetty automaattisesti Kalenterivuosi-and 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]
        )
    )

Muistiinpano

Vaikka ORDERBY on määritetty automaattisesti Kalenterivuosi-and CurrentYearSales, mitään takuuta ei anneta siitä, missä järjestyksessä ne lisätään. If CurrentYearSales lisätään ennen kalenterivuotta, tuloksena oleva tilaus ei vastaa odotettua. Määritä ORDERBYandPARTITIONBY eksplisiittisesti odottamattomien tulosten and.

Molemmat lausekkeet palauttavat etsimämme tuloksen:

Väri	Kalenterivuosi	CurrentYearSales	PreviousYearSalesForSameColor
"Musta"	2017	393885
"Musta"	2018	1818835	393885
"Musta"	2019	3981638	1818835
"Musta"	2020	2644054	3981638
"Sininen"	2019	994448
"Sininen"	2020	1284648	994448
"Moni"	2019	48622
"Moni"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Punainen"	2017	2961198
"Punainen"	2018	3686935	2961198
"Punainen"	2019	900175	3686935
"Punainen"	2020	176022	900175
"Hopeinen"	2017	326399
"Hopeinen"	2018	750026	326399
"Hopeinen"	2019	2165176	750026
"Hopeinen"	2020	1871788	2165176
"Valkoinen"	2019	2517
"Valkoinen"	2020	2589	2517
"Keltainen"	2018	163071
"Keltainen"	2019	2072083	163071
"Keltainen"	2020	2621602	2072083

Kuten tässä taulukossa näkyy, PreviousYearSalesForSameColor-sarake näyttää previousyear myynnin samalla värillä. Funktiolle [Red, 2020] palautetaan myynti parametrille [Red, 2019], and niin edelleen. If ei ole previousyear, esimerkiksi jos kyseessä on [Red, 2017], mitään value ei palauteta.

Voit ajatella PARTITIONBY keinona divide taulukon osiksi, joissa OFFSET laskutoimitus suoritetaan. Yllä olevassa esimerkissä taulukko on jaettu niin moneen osaan kuin väreihin, yksi kuhunkin väriin. Sitten OFFSET lasketaan kunkin osan sisällä kalenterivuoden mukaan lajiteltuna.

Visuaalisesti tämä on seuraava tapahtuma:

näyttävä -taulukko

First, PARTITIONBY kutsu aiheuttaa sen, että taulukko jaetaan osiin, yksi kutakin väriä kohden. Tätä ilmaisevat taulukon kuvan vaaleansiniset ruudut. Next, ORDERBY varmistaa, että jokainen osa lajitellaan Kalenterivuosi-sarakkeen mukaan (oranssit nuolet). Lopuksi kunkin lajitellun osan sisällä kullekin riville OFFSET löytää sen yläpuolella olevan rivin, and palauttaa kyseisen value PreviousYearSalesForSameColor-sarakkeessa. Koska kunkin osan jokaisen first rivin kohdalla ei ole previous riviä samassa osassa, PreviousYearSalesForSameColor-sarakkeen kyseinen rivi on tyhjä.

Jotta saavutetaan lopullinen tulos, meidän tarvitsee vain vähentää CurrentYearSales previousyear -myynnistä samalle värille, jonka OFFSET-kutsu palautti. Koska olemme not kiinnostuneita näyttämään saman värisen previousyear myynnin, mutta vain nykyisessä year myynnissä andyearyear eron yli. Tässä on lopullinen lasketun taulukon lauseke:

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 lausekkeen tulos on seuraava:

Väri	Kalenterivuosi	CurrentYearSales	YoYSalesForSameColor
"Musta"	2017	393885	393885
"Musta"	2018	1818835	1424950
"Musta"	2019	3981638	2162803
"Musta"	2020	2644054	-1337584
"Sininen"	2019	994448	994448
"Sininen"	2020	1284648	290200
"Moni"	2019	48622	48622
"Moni"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Punainen"	2017	2961198	2961198
"Punainen"	2018	3686935	725737
"Punainen"	2019	900175	-2786760
"Punainen"	2020	176022	-724153
"Hopeinen"	2017	326399	326399
"Hopeinen"	2018	750026	423627
"Hopeinen"	2019	2165176	1415150
"Hopeinen"	2020	1871788	-293388
"Valkoinen"	2019	2517	2517
"Valkoinen"	2020	2589	72
"Keltainen"	2018	163071	163071
"Keltainen"	2019	2072083	1909012
"Keltainen"	2020	2621602	549519

MATCHBY käyttäminen

Olet ehkä huomannut, että emme määrittäneet MATCHBY kohdassa all. Tässä tapauksessa se ei ole tarpeen. ORDERBY and PARTITIONBY sarakkeet (edellä esimerkeissä määritetyn mukaisesti) riittävät yksilöimään kunkin rivin. Koska emme määrittäneet MATCHBY, ORDERBYandPARTITIONBY määritettyjä sarakkeita käytetään kunkin rivin yksilöimiseen, jotta niitä voidaan verrata siihen, OFFSET mahdollistavat merkityksellisen tuloksen. If ORDERBY and PARTITIONBY sarakkeet eivät pysty yksilöimään kutakin riviä, ORDERBY-lauseeseen voidaan lisätä lisäsarakkeita, if kyseiset ylimääräiset sarakkeet mahdollistavat kunkin rivin yksilöllisen tunnistamisen. If not mahdollista, palautetaan error. Tässä last tapauksessa MATCHBY määrittäminen voi auttaa errorratkaisemisessa.

If MATCHBY määritetään, MATCHBYandPARTITIONBY sarakkeita käytetään kunkin rivin yksilöimiseen. If not mahdollista, palautetaan error. Even if MATCHBY ei tarvita, harkitse MATCHBY eksplisiittistä määrittämistä sekaannuksien välttämiseksi.

Yllä olevista esimerkeistä alkaen tässä on last-lauseke:

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 haluamme määrittää tarkasti, miten rivit tulee yksilöidä, voimme määrittää MATCHBY seuraavassa vastaavassa lausekkeessa esitetyllä tavalla:

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

Koska määritetään MATCHBY, sekä MATCHBY että PARTITIONBY määritettyjä sarakkeita käytetään rivien yksilöimiseen. Koska Väri määritetään molemmissa MATCHBYandPARTITIONBY, seuraava lauseke vastaa previous lauseketta:

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

Koska MATCHBY määrittäminen ei ole tarpeen esimerkeissä, joita olemme tähän mennessä tarkastelleet, katsotaanpa hieman erilaista esimerkkiä, joka edellyttää MATCHBY. Tässä tapauksessa meillä on luettelo tilausriveistä. Jokainen rivi edustaa tilauksen tilausriviä. Tilaukseen voi olla useita tilausrivejä, and tilausrivi 1 näkyy useissa tilauksissa. Lisäksi jokaisella tilausrivillä on ProductKey-and SalesAmount. sample taulukon olennaisista sarakkeista näyttää tältä:

SalesOrderNumber	SalesOrderLineNumber	Tuoteavain	SalesAmount
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

Huomaa, että SalesOrderNumber and SalesOrderLineNumber vaaditaan rivien yksilöimiseen.

Haluamme palauttaa jokaiselle tilaukselle saman product (tuoteavaimen esittämän) previous myyntisumman, jonka SalesAmount on tilannut laskevassa järjestyksessä. Seuraava lauseke ei toimi, koska vRelationissa on mahdollisesti useita rivejä, koska se välitetään OFFSET:

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

Tämä lauseke palauttaa error: "OFFSET:n relaatioparametrissa voi olla rivien kaksoiskappaleita, joita ei sallita."

Jotta tämä lauseke toimisi, MATCHBY on määritettävä, and on sisällettävä all sarakkeet, jotka define riviä yksilöllisesti. MATCHBY tässä vaaditaan, koska suhde FactInternetSales ei sisällä eksplisiittisiä avaimia, or yksilöiviä sarakkeita. SalesOrderNumber-and SalesOrderLineNumber yhdessä muodostavat kuitenkin yhdistelmäavaimen avaimen, jossa niiden olemassaolo yhdessä on yksilöllinen suhteessa, and siksi kukin rivi voidaan tunnistaa yksilöllisesti. Pelkkä SalesOrderNumberor SalesOrderLineNumber-arvon määrittäminen ei riitä, koska molemmat sarakkeet sisältävät toistuvan values. Seuraava lauseke ratkaisee ongelman:

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 tämä lauseke todellakin palauttaa haluatamme tuloksia:

SalesOrderNumber	SalesOrderLineNumber	Tuoteavain	SalesAmount	Previous myynnin summa
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

sisällön Related

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER