Bruk vindusfunksjoner for analyse

14 minutter

Analytiske spørringer krever ofte beregninger som dekker flere rader samtidig som de returnerer individuelle raddetaljer. Tradisjonelle aggregerte funksjoner kollapser rader til grupper, og mister radnivåinformasjon. Vindusfunksjoner løser denne utfordringen ved å utføre beregninger over et sett med rader relatert til den nåværende raden, uten å kollapse resultatsettet.

Forstå vindusfunksjonssyntaksen

Vindusfunksjoner beregner verdier over et "vindu" av rader definert av klausulen OVER . I motsetning til vanlige aggregerte funksjoner, grupperer ikke vindusfunksjoner rader til én enkelt utgangsrad. I stedet beregner de verdier på tvers av relaterte rader samtidig som de bevarer alle opprinnelige rader i resultatet.

Den generelle syntaksen for en vindusfunksjon er:

function_name(arguments) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC | DESC]]
    [ROWS | RANGE frame_specification]
)

Klausulkomponentene OVER styrer hvordan vinduet defineres:

PARTITION BY: Deler rader inn i grupper (partisjoner) for beregningen
ORDER BY: Bestemmer den logiske rekkefølgen på rader innenfor hver partisjon
RADER/OMRÅDE: Definerer rammegrensene i forhold til den nåværende raden

Følgende spørring demonstrerer en enkel vindusfunksjon som beregner et løpende antall ordrebeløp per kunde:

SELECT 
    CustomerID,
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Bemerkning

Når du spesifiserer ORDER BY i klausulen OVER uten rammespesifikasjon, er RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW standardrammen for aggregerte funksjoner. Dette skaper kumulative beregninger.

Bruk rangeringsfunksjoner

Rangeringsfunksjoner tildeler sekvensielle tall til rader basert på deres posisjon innenfor en partisjon. SQL Server tilbyr fire rangeringsfunksjoner. Hver funksjon håndterer uavgjort forskjellig:

ROW_NUMBER() tildeler et unikt sekvensielt nummer til hver rad, uten duplikater selv for likegyldige verdier:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Resultatsettet ser slik ut:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     2
751         Road-150 Red, 48            3578.27     3
771         Mountain-100 Silver, 38     3399.99     4

Dette spørsmålet rangerer alle produkter etter pris fra høyest til lavest. Hvert produkt får et unikt nummer, uavhengig av om flere produkter deler samme pris eller ikke.

RANK() tildeler samme rang til verdier som er likt, og hopper deretter over tall for å ta høyde for uavgjorte:

SELECT 
    ProductID,
    Name,
    ListPrice,
    RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Resultatsettet ser slik ut:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     4

Når to produkter har identiske priser, får begge samme rangering. Neste produkts rangering reflekterer totalt antall produkter rangert høyere, noe som skaper hull i rekkefølgen.

DENSE_RANK() tildeler samme rang til bundne verdier, men hopper ikke over tall:

SELECT 
    ProductID,
    Name,
    ListPrice,
    DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Resultatsettet ser slik ut:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     2

Som RANK(), har bundne verdier samme rang. Men DENSE_RANK() fortsetter med neste påfølgende tall, så du kan bruke det til å telle ulike prisnivåer.

NTILE(n) fordeler rader i et spesifisert antall omtrent like grupper:

SELECT 
    ProductID,
    Name,
    ListPrice,
    NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;

Resultatsettet ser slik ut:

ProductID   Name                          ListPrice   PriceQuartile
---------   ---------------------------   ---------   -------------
749         Road-150 Red, 62              3578.27     1
771         Mountain-100 Silver, 38       3399.99     1
722         LL Road Frame - Black, 58     337.22      2
859         Half-Finger Gloves, S         24.49       4

Dette spørsmålet deler produktene inn i fire grupper basert på pris. De dyreste produktene er i kvartil 1, og de rimeligste er i kvartil 4. Bruk NTILE() til prosentilanalyse eller jevn fordeling av arbeid.

Kombinasjon PARTITION BY med rangeringsfunksjoner muliggjør rangeringer per gruppe:

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice,
    ROW_NUMBER() OVER (
        PARTITION BY p.ProductCategoryID 
        ORDER BY p.ListPrice DESC
    ) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;

Resultatsettet ser slik ut:

Category          Product                     ListPrice   CategoryPriceRank
---------------   -------------------------   ---------   -----------------
Road Bikes        Road-150 Red, 62            3578.27     1
Road Bikes        Road-150 Red, 44            3578.27     2
Mountain Bikes    Mountain-100 Silver, 38     3399.99     1
Mountain Bikes    Mountain-100 Black, 38      3374.99     2

Denne spørringen rangerer produkter innenfor hver kategori separat. Rangeringen starter på nytt på 1 for hver kategori, så du kan identifisere det dyreste produktet i hver kategori ved å filtrere for CategoryPriceRank = 1.

Tips

Bruk ROW_NUMBER() når du trenger nøyaktig én rad per rang (for eksempel å finne de øverste N per gruppe). Bruk RANK() eller DENSE_RANK() når du trenger å bevare båndinformasjon for rapporteringsformål.

Bruk aggregerte vindusfunksjoner

Standard aggregerte funksjoner som SUM, AVG, , COUNT, MINog MAX kan brukes som vindusfunksjoner ved å legge til klausulen OVER . Dette lar deg beregne aggregater samtidig som du beholder individuelle raddetaljer.

Følgende spørring demonstrerer hvordan man beregner løpende totaler og kumulative aggregater:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
    AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
    COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;

Resultatsettet ser slik ut:

SalesOrderID   OrderDate    TotalDue    RunningTotal   RunningAverage   OrderNumber
------------   ----------   ---------   ------------   --------------   -----------
71774          2008-06-01   972.785     972.785        972.785          1
71776          2008-06-01   87.083      1059.868       529.934          2
71780          2008-06-01   42452.65    43512.518      14504.172        3
71782          2008-06-01   43962.79    87475.308      21868.827        4

Viktig!

Når man bruker aggregerte vindusfunksjoner uten ORDER BY i klausulen OVER , beregner funksjonen over hele partisjonen. Å legge til ORDER BY skaper en løpende beregning fra partisjonsstarten til gjeldende rad.

Definer vindusrammer med `ROWS` og `RANGE`

Vindusrammer lar deg spesifisere nøyaktig hvilke rader i forhold til den nåværende raden som skal inkluderes i beregningen. Klausulen ROWS teller fysiske rader, mens RANGE den grupperer rader med lik verdi.

Rammegrenser kan spesifiseres ved hjelp av:

UNBOUNDED PRECEDING: Fra partisjonsstarten
n PRECEDING: n rader før nåværende rad
CURRENT ROW: Den nåværende raden
n FOLLOWING: n rader etter nåværende rad
UNBOUNDED FOLLOWING: Til delingssiden

Følgende spørring beregner et glidende gjennomsnitt over de tre siste ordenene:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    AVG(TotalDue) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Resultatsettet ser slik ut:

SalesOrderID   OrderDate    TotalDue    MovingAvg3Orders
------------   ----------   ---------   ----------------
71774          2008-06-01   972.785     972.785
71776          2008-06-01   87.083      529.934
71780          2008-06-01   42452.65    14504.172
71782          2008-06-01   43962.79    28834.174

Denne spørringen beregner et 3-ordens glidende gjennomsnitt ved å inkludere gjeldende rad og de to radene før den. For første rad er bare én verdi tilgjengelig, så gjennomsnittet er TotalDuelik . Ved tredje rad inkluderer vinduet alle tre radene.

Bruk analytiske funksjoner

Analytiske funksjoner lar deg få tilgang til data fra andre rader uten å bruke selvsammenkoblinger eller underforespørsler. Disse funksjonene er nyttige for tidsserieanalyse, trenddeteksjon og sammenligning av nåværende verdier med historiske eller fremtidige verdier. I motsetning til aggregerte vindusfunksjoner som beregner sammendrag, henter analytiske funksjoner spesifikke verdier fra bestemte rader i vinduet.

LAG() og LEAD() tilgang til verdier fra tidligere eller påfølgende rader, slik som dette:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
    LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
    TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Resultatsettet ser slik ut:

SalesOrderID   OrderDate    TotalDue    PreviousOrderTotal   NextOrderTotal   ChangeFromPrevious
------------   ----------   ---------   ------------------   --------------   ------------------
71774          2008-06-01   972.785     0                    87.083           972.785
71776          2008-06-01   87.083      972.785              42452.65         -885.702
71780          2008-06-01   42452.65    87.083               43962.79         42365.567
71782          2008-06-01   43962.79    42452.65             0                1510.14

LAG() henter en verdi fra en tidligere rad, mens LEAD() henter fra en påfølgende rad. Den andre parameteren spesifiserer hvor mange rader som skal ses bakover eller fremover (standard er 1), og den tredje parameteren gir en standardverdi når det ikke finnes noen rad (for eksempel for første rad med LAG()). Bruk disse funksjonene til å beregne periode-over-periode-endringer, identifisere trender eller oppdage avvik i sekvensielle data.

FIRST_VALUE() og LAST_VALUE() returnerer verdier fra første eller siste rad i rammen:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ProductCategoryID,
    FIRST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
    ) AS MostExpensiveInCategory,
    LAST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;

Resultatsettet ser slik ut:

ProductID   Name                        ListPrice   ProductCategoryID   MostExpensiveInCategory    LeastExpensiveInCategory
---------   -------------------------   ---------   -----------------   ------------------------   ------------------------
749         Road-150 Red, 62            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
750         Road-150 Red, 44            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
722         LL Road Frame - Red, 58     337.22      5                   Road-150 Red, 62           LL Road Frame - Red, 58
771         Mountain-100 Silver, 38     3399.99     6                   Mountain-100 Silver, 38    Mountain-500 Black, 52

FIRST_VALUE() returnerer verdien fra første rad i det ordnede vinduet, som i dette tilfellet er det dyreste produktet per kategori. LAST_VALUE() Returer er de billigste, men krever en eksplisitt ramme som inkluderer alle rader. Disse funksjonene hjelper deg å sammenligne hver rad med referanseverdier som høyeste, laveste eller basisverdi i en gruppe.

Bemerkning

LAST_VALUE() krever en eksplisitt rammespesifikasjon for å inkludere rader etter gjeldende rad. Uten den inkluderer standardrammen kun rader opp til gjeldende rad, noe som gjør LAST_VALUE() at verdien til den nåværende raden returneres.

PERCENT_RANK() og CUME_DIST() beregner relativ posisjon innenfor en partisjon:

SELECT 
    Name,
    ListPrice,
    PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
    CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;

Resultatsettet ser slik ut:

Name                        ListPrice   PercentRank   CumulativeDistribution
-------------------------   ---------   -----------   ----------------------
Patch Kit/8 Patches         2.29        0.0           0.0081
Road Tire Tube              3.99        0.0081        0.0162
Touring Tire Tube           4.99        0.0162        0.0243
Road-150 Red, 62            3578.27     0.9919        1.0

PERCENT_RANK() returnerer en verdi mellom 0 og 1 som angir hvor stor prosentandel av radene som har lavere verdier (0 betyr laveste, én betyr høyest). CUME_DIST() viser den kumulative fordelingen, som indikerer hvor stor andel radene som har verdier mindre enn eller lik den nåværende raden. Bruk disse funksjonene til prosentilanalyse, identifisering av uteliggere eller opprettelse av distribusjonsrapporter.

For mer informasjon om vindusfunksjoner, se Vindusfunksjoner (Transact-SQL) og Rangeringsfunksjoner.

Tilbakemeldinger

Var denne siden nyttig?

Bruk vindusfunksjoner for analyse

Forstå vindusfunksjonssyntaksen

Bruk rangeringsfunksjoner

Bruk aggregerte vindusfunksjoner

Definer vindusrammer med ROWS og RANGE

Bruk analytiske funksjoner

Tilbakemeldinger

Definer vindusrammer med `ROWS` og `RANGE`