Begrip van ORDERBY, PARTITIONBY, andMATCHBY functies
De ORDERBY, PARTITIONBY, andMATCHBY functies in DAX zijn speciale functies die alleen samen met DAXWindow functies kunnen worden gebruikt: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.
Inzicht in ORDERBY, PARTITIONBY, andMATCHBY is essentieel om de Window-functies te gebruiken. De voorbeelden die hier worden gegeven, gebruiken OFFSET, maar zijn vergelijkbaar van toepassing op de andere Window functies.
Scenario
Laten we beginnen met een voorbeeld dat geen gebruik maakt van Window functies op all. Hieronder ziet u een tabel die de totale verkoop, per kleur, per calendaryearretourneert. Er zijn meerdere manieren om deze tabel te define, maar omdat we graag weten wat er in DAXgebeurt, gebruiken we een berekende tabel. Dit is de tabeluitdrukking:
BasicTable =
SUMMARIZECOLUMNS (
DimProduct[Color],
DimDate[CalendarYear],
"CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
)
Je zult zien dat deze berekende tabelexpressie SUMMARIZECOLUMNS tot calculate de SUM van de kolom SalesAmount in de tabel FactInternetSales, op basis van de kolom Color uit de tabel DimProduct, en and de kolom CalendarYear uit de tabel DimDate. Dit is het resultaat:
| Kleur | Kalenderjaar | VerkoopVanHetHuidigeJaar |
|---|---|---|
| "Zwart" | 2017 | 393885 |
| "Zwart" | 2018 | 1818835 |
| "Zwart" | 2019 | 3981638 |
| "Zwart" | 2020 | 2644054 |
| "Blauw" | 2019 | 994448 |
| "Blauw" | 2020 | 1284648 |
| "Multi" | 2019 | 48622 |
| Multi | 2020 | 57849 |
| "NA" | 2019 | 207822 |
| "NA" | 2020 | 227295 |
| Rood | 2017 | 2961198 |
| "Rood" | 2018 | 3686935 |
| Rood | 2019 | 900175 |
| "Rood" | 2020 | 176022 |
| "Zilver" | 2017 | 326399 |
| "Zilver" | 2018 | 750026 |
| "Zilver" | 2019 | 2165176 |
| "Zilver" | 2020 | 1871788 |
| Wit | 2019 | 2517 |
| "Wit" | 2020 | 2589 |
| "Geel" | 2018 | 163071 |
| "Geel" | 2019 | 2072083 |
| "Geel" | 2020 | 2621602 |
Now, stel dat we proberen de bedrijfsvraag op te lossen van het berekenen van het verschil in verkoop, year-over-year voor elke kleur. In feite hebben we een manier nodig om de verkoop voor dezelfde kleur in de previousyearand af find te trekken van de verkoop in de huidige year, in context. Voor de combinatie [Rood, 2019] zijn we bijvoorbeeld op zoek naar verkoop voor [Rood, 2018]. Zodra we dat hebben, kunnen we deze aftrekken van de huidige verkoop and de vereiste valueretourneren.
OFFSET gebruiken
OFFSET is perfect voor de typische vergelijken met previous typen berekeningen die nodig zijn om de hierboven beschreven zakelijke vraag te beantwoorden, omdat we hiermee een relatieve beweging kunnen uitvoeren. Onze first poging zou kunnen zijn:
1stAttempt =
VAR vRelation = SUMMARIZECOLUMNS (
DimProduct[Color],
DimDate[CalendarYear],
"CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
)
RETURN
ADDCOLUMNS (
vRelation,
"PreviousColorSales",
SELECTCOLUMNS (
OFFSET (
-1,
vRelation
),
[CurrentYearSales]
)
)
Er gebeurt veel met deze expressie. We hebben ADDCOLUMNS gebruikt om de vorige tabel te expand met een kolom genaamd "PreviousColorSales". De inhoud van die kolom is ingesteld op het CurrentYearSales, wat SUM(FactInternetSales[SalesAmount]) is, voor de previous-kleur (opgehaald met behulp van OFFSET).
Het resultaat is:
| Kleur | Kalenderjaar | VerkoopVanHuidigJaar | PreviousColorSales |
|---|---|---|---|
| "Zwart" | 2017 | 393885 | |
| "Zwart" | 2018 | 1818835 | 393885 |
| "Zwart" | 2019 | 3981638 | 1818835 |
| "Zwart" | 2020 | 2644054 | 3981638 |
| "Blauw" | 2019 | 994448 | 2644054 |
| "Blauw" | 2020 | 1284648 | 994448 |
| "Multi" | 2019 | 48622 | 1284648 |
| Multi | 2020 | 57849 | 48622 |
| "NA" | 2019 | 207822 | 57849 |
| "NA" | 2020 | 227295 | 207822 |
| Rood | 2017 | 2961198 | 227295 |
| "Rood" | 2018 | 3686935 | 2961198 |
| Rood | 2019 | 900175 | 3686935 |
| "Rood" | 2020 | 176022 | 900175 |
| "Zilver" | 2017 | 326399 | 176022 |
| "Zilver" | 2018 | 750026 | 326399 |
| "Zilver" | 2019 | 2165176 | 750026 |
| "Zilver" | 2020 | 1871788 | 2165176 |
| Wit | 2019 | 2517 | 1871788 |
| Wit | 2020 | 2589 | 2517 |
| "Geel" | 2018 | 163071 | 2589 |
| "Geel" | 2019 | 2072083 | 163071 |
| "Geel" | 2020 | 2621602 | 2072083 |
Dit is een stap dichter bij ons doel, maar if we goed kijken, komt het niet precies overeen met wat we hebben. Voor [Silver, 2017] is de PreviousColorSales bijvoorbeeld ingesteld op [Rood, 2020].
ORDERBY toevoegen
De bovenstaande definitie is gelijk aan:
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]
)
)
In dit geval gebruikt de aanroep van OFFSETORDERBY om de tabel op Color and CalendarYear in oplopende volgorde te ordenen, waarmee wordt bepaald wat wordt beschouwd als de previous rij die wordt geretourneerd.
De reden waarom deze twee resultaten gelijkwaardig zijn, is omdat ORDERBY automatisch kolommen containsall uit de relatie verwijdert die zich niet in PARTITIONBYbevinden. Omdat PARTITIONBY niet is opgegeven, is ORDERBY ingesteld op Color, CalendarYear and CurrentYearSales. Omdat de Color and CalendarYear-paren in de relatie echter uniek zijn, verandert het resultaat niet door CurrentYearSales toe te voegen. Als we in fact, evenif alleen de kleur in ORDERBYzouden specificeren, zijn de resultaten hetzelfde omdat CalendarYear automatisch zou worden toegevoegd. Dit komt doordat de functie zoveel kolommen toevoegt als nodig is aan ORDERBY om ervoor te zorgen dat elke rij uniek kan worden geïdentificeerd door de ORDERBYandPARTITIONBY kolommen.
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 toevoegen
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]
)
)
U ziet dat het opgeven van ORDERBY hier optioneel is, omdat ORDERBY automatisch de kolommen containsall uit de relatie die niet zijn opgegeven in PARTITIONBY. De volgende expressie retourneert dus dezelfde resultaten omdat ORDERBY is ingesteld op CalendarYear and CurrentYearSales automatisch:
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]
)
)
Notitie
Hoewel ORDERBY automatisch is ingesteld op CalendarYear and CurrentYearSales, wordt er geen garantie gegeven over de volgorde waarin ze worden toegevoegd. If CurrentYearSales wordt toegevoegd vóór CalendarYear, waardoor de resulterende volgorde niet overeenkomt met wat verwacht wordt. Expliciet zijn bij het opgeven van ORDERBYandPARTITIONBY om verwarring te voorkomen and onverwachte resultaten.
Beide uitdrukkingen leveren het gewenste resultaat op.
| Kleur | Kalenderjaar | HuidigeJaarVerkoop | VerkoopVorigeJaarVoorZelfdeKleur |
|---|---|---|---|
| "Zwart" | 2017 | 393885 | |
| "Zwart" | 2018 | 1818835 | 393885 |
| "Zwart" | 2019 | 3981638 | 1818835 |
| "Zwart" | 2020 | 2644054 | 3981638 |
| "Blauw" | 2019 | 994448 | |
| "Blauw" | 2020 | 1284648 | 994448 |
| "Multi" | 2019 | 48622 | |
| "Multi" | 2020 | 57849 | 48622 |
| "NA" | 2019 | 207822 | |
| "NA" | 2020 | 227295 | 207822 |
| Rood | 2017 | 2961198 | |
| "Rood" | 2018 | 3686935 | 2961198 |
| Rood | 2019 | 900175 | 3686935 |
| Rood | 2020 | 176022 | 900175 |
| "Zilver" | 2017 | 326399 | |
| "Zilver" | 2018 | 750026 | 326399 |
| "Zilver" | 2019 | 2165176 | 750026 |
| "Zilver" | 2020 | 1871788 | 2165176 |
| "Wit" | 2019 | 2517 | |
| Wit | 2020 | 2589 | 2517 |
| "Geel" | 2018 | 163071 | |
| "Geel" | 2019 | 2072083 | 163071 |
| "Geel" | 2020 | 2621602 | 2072083 |
Zoals u in deze tabel ziet, toont de kolom PreviousYearSalesForSameColor de verkoop voor de previousyear voor dezelfde kleur. Voor [Rood, 2020] geeft het de verkoopcijfers terug voor [Rood, 2019], and, enzovoort. If er geen previousyearis, bijvoorbeeld in het geval van [Rood, 2017], wordt er geen value geretourneerd.
U kunt PARTITIONBY beschouwen als een manier om de tabel te divide in delen waarin de OFFSET berekening moet worden uitgevoerd. In het bovenstaande voorbeeld is de tabel onderverdeeld in zoveel delen als er kleuren zijn, één voor elke kleur. Vervolgens wordt in elk gedeelte de OFFSET berekend, gesorteerd op CalendarYear.
Wat er visueel gebeurt, is dit:
Firstresulteert de aanroep van PARTITIONBY in de tabel in delen, één voor elke kleur. Dit wordt vertegenwoordigd door de lichtblauwe vakken in de tabelafbeelding. Nexten ORDERBY zorgen ervoor dat elk onderdeel op CalendarYear wordt gesorteerd (vertegenwoordigd door de oranje pijlen). Ten slotte vindt OFFSET in elk gesorteerd deel voor elke rij de rij erboven and retourneert die value in de kolom PreviousYearSalesForSameColor. Omdat voor elke first rij in elk deel geen previous rij in hetzelfde deel is, is het resultaat in die rij voor de kolom PreviousYearSalesForSameColor leeg.
Om het uiteindelijke resultaat te bereiken, moeten we gewoon CurrentYearSales aftrekken van de verkoop van previousyear voor dezelfde kleur die wordt geretourneerd door de aanroep naar OFFSET. Omdat we not geïnteresseerd zijn in het weergeven van de previousyear verkoop voor dezelfde kleur, maar alleen in de huidige year verkoop and de year meer dan year verschil. Dit is de uiteindelijke expressie voor de berekende tabel:
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 dit is het resultaat van die expressie:
| Kleur | Kalenderjaar | VerkoopHuidigJaar | YoYSalesForSameColor |
|---|---|---|---|
| "Zwart" | 2017 | 393885 | 393885 |
| "Zwart" | 2018 | 1818835 | 1424950 |
| "Zwart" | 2019 | 3981638 | 2162803 |
| "Zwart" | 2020 | 2644054 | -1337584 |
| "Blauw" | 2019 | 994448 | 994448 |
| "Blauw" | 2020 | 1284648 | 290200 |
| "Multi" | 2019 | 48622 | 48622 |
| "Multi" | 2020 | 57849 | 9227 |
| "NA" | 2019 | 207822 | 207822 |
| NA | 2020 | 227295 | 19473 |
| Rood | 2017 | 2961198 | 2961198 |
| Rood | 2018 | 3686935 | 725737 |
| Rood | 2019 | 900175 | -2786760 |
| Rood | 2020 | 176022 | -724153 |
| "Zilver" | 2017 | 326399 | 326399 |
| "Zilver" | 2018 | 750026 | 423627 |
| "Zilver" | 2019 | 2165176 | 1415150 |
| "Zilver" | 2020 | 1871788 | -293388 |
| Wit | 2019 | 2517 | 2517 |
| "Wit" | 2020 | 2589 | 72 |
| "Geel" | 2018 | 163071 | 163071 |
| "Geel" | 2019 | 2072083 | 1909012 |
| "Geel" | 2020 | 2621602 | 549519 |
MATCHBY gebruiken
U hebt misschien gemerkt dat we MATCHBY niet bij allhebben gespecificeerd. In dit geval is het niet nodig. De kolommen in ORDERBYandPARTITIONBY (voor zover ze zijn opgegeven in de bovenstaande voorbeelden) zijn voldoende om elke rij uniek te identificeren. Omdat we MATCHBYniet hebben opgegeven, worden de kolommen die zijn opgegeven in ORDERBYandPARTITIONBY gebruikt om elke rij uniek te identificeren, zodat ze kunnen worden vergeleken om OFFSET een zinvol resultaat te geven. If de kolommen in ORDERBYandPARTITIONBY niet elke rij uniek kunnen identificeren, kunnen extra kolommen worden toegevoegd aan de ORDERBY clausule if zodat elke rij uniek kan worden geïdentificeerd door deze extra kolommen. If dat not mogelijk is, wordt error geretourneerd. In dit last geval kan het opgeven van MATCHBY helpen om de errorop te lossen.
If MATCHBY wordt opgegeven, worden de kolommen in MATCHBYandPARTITIONBY gebruikt om elke rij uniek te identificeren. If dat not mogelijk is, wordt een error geretourneerd. Even if MATCHBY is niet vereist, overweeg dan expliciet MATCHBY op te geven om verwarring te voorkomen.
Als u verdergaat met de bovenstaande voorbeelden, ziet u hier de last expressie:
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 we expliciet willen zijn over hoe rijen uniek moeten worden geïdentificeerd, kunnen we MATCHBY opgeven, zoals wordt weergegeven in de volgende equivalente expressie:
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]
)
)
Aangezien MATCHBY is opgegeven, worden zowel de kolommen die zijn opgegeven in MATCHBY als in PARTITIONBY gebruikt om rijen uniek te identificeren. Omdat kleur is opgegeven in beide MATCHBYandPARTITIONBY, is de volgende expressie gelijk aan de previous expressie:
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]
)
)
Aangezien het opgeven van MATCHBY niet nodig is in de voorbeelden die we tot nu toe hebben bekeken, gaan we een iets ander voorbeeld bekijken waarvoor MATCHBYvereist is. In dit geval hebben we een lijst met orderregels. Elke rij vertegenwoordigt een orderregel voor een bestelling. Een order kan meerdere orderregels bevatten. and Orderregel 1 wordt weergegeven op veel orders. Daarnaast hebben we voor elke orderregel een ProductKey and en een SalesAmount. Een sample van de relevante kolommen in de tabel ziet er als volgt uit:
| Verkoopordernummer | Verkooporderregelnummer | ProductKey | Verkoopbedrag |
|---|---|---|---|
| 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 |
U ziet dat zowel SalesOrderNumber and, als SalesOrderLineNumber, beide vereist zijn om rijen uniek te identificeren.
Voor elke order willen we het previous verkoopbedrag retourneren van dezelfde product (vertegenwoordigd door de ProductKey) die is gesorteerd op verkoopbedrag in aflopende volgorde. De volgende expressie werkt niet omdat er mogelijk meerdere rijen in vRelation zijn, omdat deze wordt doorgegeven aan OFFSET:
ThisExpressionFailsBecauseMATCHBYIsMissing =
ADDCOLUMNS (
FactInternetSales,
"Previous Sales Amount",
SELECTCOLUMNS (
OFFSET (
-1,
FactInternetSales,
ORDERBY ( FactInternetSales[SalesAmount], DESC ),
PARTITIONBY ( FactInternetSales[ProductKey] )
),
FactInternetSales[SalesAmount]
)
)
Deze expressie retourneert een error: 'OFFSET's Relation-parameter heeft mogelijk dubbele rijen, die niet zijn toegestaan.'
Als u deze expressie wilt laten werken, moet MATCHBY worden opgegeven and moet all kolommen bevatten die uniek define een rij. MATCHBY is hier vereist omdat de relatie FactInternetSales geen expliciete sleutels or unieke kolommen bevat. De kolommen SalesOrderNumber and en SalesOrderLineNumber vormen echter samen een samengestelde sleutel, waarbij hun gezamenlijke bestaan uniek is in de relatie and en daarom elke rij uniek kan identificeren. Het opgeven van SalesOrderNumber or SalesOrderLineNumber is niet voldoende omdat beide kolommen herhalende valuesbevatten. Met de volgende expressie wordt het probleem opgelost:
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 deze expressie doet inderdaad de resultaten retourneren waar we naar op zoek zijn.
| Verkoopordernummer | Orderregelnummer | ProductKey | Verkoopbedrag | Previous verkoopbedrag |
|---|---|---|---|---|
| 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 |
inhoud Related
ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER