Använda fönsterfunktioner för analys

14 minuter

Analysfrågor kräver ofta beräkningar som sträcker sig över flera rader samtidigt som enskilda radinformation returneras. Traditionella aggregeringsfunktioner döljer rader i grupper och förlorar information på radnivå. Fönsterfunktioner löser den här utmaningen genom att utföra beräkningar över en uppsättning rader som är relaterade till den aktuella raden, utan att komprimera resultatuppsättningen.

Förstå funktionssyntax för fönster

Fönsterfunktioner beräknar värden i ett "fönster" med rader som definierats av OVER -satsen. Till skillnad från vanliga mängdfunktioner grupperar inte fönsterfunktioner rader i en enda utdatarad. I stället beräknar de värden över relaterade rader samtidigt som alla ursprungliga rader i resultatet bevaras.

Den allmänna syntaxen för en fönsterfunktion är:

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

Satskomponenterna OVER styr hur fönstret definieras:

PARTITION BY: Delar upp rader i grupper (partitioner) för beräkningen
ORDER BY: Avgör den logiska ordningen för rader inom varje partition
RADER/INTERVALL: Definierar ramgränserna i förhållande till den aktuella raden

Följande fråga visar en enkel fönsterfunktion som beräknar en löpande summa av orderbelopp per kund:

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

Anmärkning

När du anger ORDER BY i OVER -satsen utan en ramspecifikation är RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW standardramen för mängdfunktioner. Detta skapar kumulativa beräkningar.

Använda rangordningsfunktioner

Rangordningsfunktioner tilldelar sekventiella tal till rader baserat på deras position i en partition. SQL Server har fyra rankningsfunktioner. Varje funktion hanterar banden på olika sätt:

ROW_NUMBER() tilldelar ett unikt sekventiellt nummer till varje rad, utan dubbletter även för bundna värden:

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

Resultatuppsättningen ser ut så här:

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

Den här frågan rangordnar alla produkter efter pris från högsta till lägsta. Varje produkt får ett unikt nummer oavsett om flera produkter delar samma pris.

RANK() tilldelar samma rangordning till bundna värden och hoppar sedan över nummer för att ta hänsyn till banden:

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

Resultatuppsättningen ser ut så här:

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 två produkter har identiska priser får båda samma rangordning. Nästa produkts rangordning återspeglar det totala antalet produkter som rangordnas högre, vilket skapar luckor i sekvensen.

DENSE_RANK() tilldelar samma rangordning till bundna värden men hoppar inte över tal:

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

Resultatuppsättningen ser ut så här:

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

Precis som RANK()delar bundna värden samma rangordning. DENSE_RANK() Men fortsätter med nästa nummer i följd, så du kan använda det för att räkna distinkta prisnivåer.

NTILE(n) distribuerar rader till ett angivet antal ungefär lika med grupper:

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

Resultatuppsättningen ser ut så här:

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

Den här frågan delar upp produkter i fyra grupper baserat på pris. De högst prissatta produkterna finns i kvartil 1, och de lägsta priserna är i kvartil 4. Använd NTILE() för percentilanalys eller för att distribuera arbetet jämnt.

Kombination PARTITION BY med rangordningsfunktioner möjliggör rangordning per grupp:

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;

Resultatuppsättningen ser ut så här:

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

Den här frågan rangordnar produkter inom varje kategori separat. Rangordningen startar om vid 1 för varje kategori, så att du kan identifiera den dyraste produkten i varje kategori genom att filtrera efter CategoryPriceRank = 1.

Tips/Råd

Använd ROW_NUMBER() när du behöver exakt en rad per rangordning (till exempel hitta den översta N per grupp). Använd RANK() eller DENSE_RANK() när du behöver bevara kopplingsinformation i rapporteringssyfte.

Använda mängdfönsterfunktioner

Standardaggregatfunktioner som SUM, AVG, COUNT, MINoch MAX kan användas som fönsterfunktioner genom att lägga till OVER -satsen. På så sätt kan du beräkna aggregeringar samtidigt som du behåller enskild radinformation.

Följande fråga visar hur du beräknar löpande summor och kumulativa aggregeringar:

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;

Resultatuppsättningen ser ut så här:

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

Viktigt!

När du använder aggregerade fönsterfunktioner utan ORDER BY i OVER -satsen beräknas funktionen över hela partitionen. När du lägger till ORDER BY skapas en löpande beräkning från partitionsstarten till den aktuella raden.

Definiera fönsterramar med `ROWS` och `RANGE`

Med fönsterramar kan du ange exakt vilka rader i förhållande till den aktuella raden som ska ingå i beräkningen. ROWS Satsen räknar fysiska rader, medan RANGE rader med samma värden grupperas.

Ramgränser kan anges med hjälp av:

UNBOUNDED PRECEDING: Från partitionsstarten
n PRECEDING: n rader före nuvarande rad
CURRENT ROW: Den aktuella raden
n FOLLOWING: n rader efter aktuell rad
UNBOUNDED FOLLOWING: Till partitionsslutet

Följande fråga beräknar ett glidande medelvärde för de tre senaste beställningarna:

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;

Resultatuppsättningen ser ut så här:

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

Den här frågan beräknar ett glidande medelvärde på 3 order genom att inkludera den aktuella raden och de två raderna före den. För den första raden är endast ett värde tillgängligt, så medelvärdet är lika med TotalDue. Vid den tredje raden innehåller fönstret alla tre raderna.

Använda analysfunktioner

Med analysfunktioner kan du komma åt data från andra rader utan att använda självkopplingar eller underfrågor. Dessa funktioner är användbara för tidsserieanalys, trendidentifiering och jämförelse av aktuella värden med historiska eller framtida värden. Till skillnad från aggregeringsfönsterfunktioner som beräknar sammanfattningar hämtar analysfunktioner specifika värden från specifika rader i fönstret.

LAG() och LEAD() har åtkomst till värden från tidigare eller följande rader, så här:

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;

Resultatuppsättningen ser ut så här:

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() hämtar ett värde från en tidigare rad medan LEAD() det hämtas från följande rad. Den andra parametern anger hur många rader som ska se tillbaka eller framåt (standardvärdet är 1) och den tredje parametern ger ett standardvärde när det inte finns någon rad (till exempel för den första raden med LAG()). Använd dessa funktioner för att beräkna period-över-period-ändringar, identifiera trender eller identifiera avvikelser i sekventiella data.

FIRST_VALUE() och LAST_VALUE() returnera värden från den första eller sista raden i ramen:

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;

Resultatuppsättningen ser ut så här:

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() returnerar värdet från den första raden i det ordnade fönstret, vilket i det här fallet är den dyraste produkten per kategori. LAST_VALUE() returnerar den billigaste, men kräver en explicit ram för att inkludera alla rader. De här funktionerna hjälper dig att jämföra varje rad med benchmark-värden som det högsta, lägsta eller baslinjevärdet i en grupp.

Anmärkning

LAST_VALUE() kräver en explicit ramspecifikation för att inkludera rader efter den aktuella raden. Utan den innehåller standardramen bara rader upp till den aktuella raden, vilket gör att LAST_VALUE() den aktuella radens värde returneras.

PERCENT_RANK() och CUME_DIST() beräknar relativ position inom en partition:

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;

Resultatuppsättningen ser ut så här:

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() returnerar ett värde mellan 0 och 1 som anger vilken procentandel av raderna som har lägre värden (0 innebär lägst, en betyder högst). CUME_DIST() visar den kumulativa fördelningen, som anger vilken procentandel rader som har värden som är mindre än eller lika med den aktuella raden. Använd dessa funktioner för percentilanalys, identifiering av extremvärden eller skapande av distributionsrapporter.

Mer information om fönsterfunktioner finns i Window Functions (Transact-SQL) och Rankningsfunktioner.

Feedback

Var den här sidan till hjälp?

Använda fönsterfunktioner för analys

Förstå funktionssyntax för fönster

Använda rangordningsfunktioner

Använda mängdfönsterfunktioner

Definiera fönsterramar med ROWS och RANGE

Använda analysfunktioner

Feedback

Definiera fönsterramar med `ROWS` och `RANGE`