Použití funkcí oken pro analýzy
Návod
Další podrobnosti najdete na kartě Text a obrázky .
Analytické dotazy často vyžadují výpočty, které pokrývají více řádků a přesto vrací podrobnosti o jednotlivých řádcích. Tradiční agregační funkce komprimují řádky do skupin a ztrácí informace na úrovni řádků. Okénkové funkce tuto výzvu řeší tak, že provádějí výpočty napříč sadou řádků spojených s aktuálním řádkem, aniž by sbalily sadu výsledků.
Principy syntaxe funkce okna
Funkce okna vypočítají hodnoty napříč oknem řádků definovaných klauzulí OVER . Na rozdíl od běžných agregačních funkcí funkce okna neskupují řádky do jednoho výstupního řádku. Místo toho vypočítávají hodnoty napříč souvisejícími řádky a zachovávají všechny původní řádky ve výsledku.
Obecná syntaxe funkce okna je:
function_name(arguments) OVER (
[PARTITION BY partition_expression]
[ORDER BY order_expression [ASC | DESC]]
[ROWS | RANGE frame_specification]
)
Komponenty OVER klauzule určují, jak je okno definováno:
- PARTITION BY: Rozdělí řádky do skupin (oddílů) pro výpočet.
- ORDER BY: Určuje logické pořadí řádků v rámci každého oddílu.
- ŘÁDKY/ROZSAH: Definuje hranice rámce vzhledem k aktuálnímu řádku.
Následující dotaz ukazuje jednoduchou funkci okna, která vypočítá průběžný součet částek objednávek na zákazníka:
SELECT
CustomerID,
SalesOrderID,
OrderDate,
TotalDue,
SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;
Poznámka:
Když v ORDER BY klauzuli zadáte OVER bez specifikace rámce, bude RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW výchozí rámec pro agregační funkce. Tím se vytvoří kumulativní výpočty.
Použití funkcí řazení
Funkce řazení přiřazují pořadová čísla k řádkům na základě jejich pozice v rámci oddílu. SQL Server poskytuje čtyři funkce řazení. Každá funkce zpracovává vazby odlišně:
ROW_NUMBER() přiřadí každému řádku jedinečné pořadové číslo bez duplicit ani pro vázané hodnoty:
SELECT
ProductID,
Name,
ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
Sada výsledků vypadá takto:
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
Tento dotaz řadí všechny produkty podle ceny od nejvyššího po nejnižší. Každý produkt obdrží jedinečné číslo bez ohledu na to, jestli více produktů sdílí stejnou cenu.
RANK() přiřadí stejné pořadí ke svázaným hodnotám a pak přeskočí čísla, která budou zohledňovat vazby:
SELECT
ProductID,
Name,
ListPrice,
RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
Sada výsledků vypadá takto:
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
Pokud mají dva produkty stejné ceny, oba obdrží stejné pořadí. Pořadí dalšího produktu odráží celkový počet produktů seřazených výše a vytváří mezery v sekvenci.
DENSE_RANK() přiřadí stejné pořadí ke svázaným hodnotám, ale nepřekočí čísla:
SELECT
ProductID,
Name,
ListPrice,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;
Sada výsledků vypadá takto:
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
Stejně jako RANK()vázané hodnoty sdílejí stejné pořadí.
DENSE_RANK() Pokračuje ale s dalším po sobě jdoucím číslem, takže ho můžete použít k počítání odlišných cenových úrovní.
NTILE(n) distribuuje řádky do zadaného počtu zhruba stejných skupin:
SELECT
ProductID,
Name,
ListPrice,
NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;
Sada výsledků vypadá takto:
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
Tento dotaz rozdělí produkty do čtyř skupin na základě ceny. Produkty s nejvyšší cenou jsou v kvartilu 1 a nejnižší ceny jsou v kvartilu 4. Použijte NTILE() pro percentilovou analýzu nebo rovnoměrnou distribuci práce.
PARTITION BY Kombinace s funkcemi řazení umožňuje řazení podle skupin:
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;
Sada výsledků vypadá takto:
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
Tento dotaz řadí produkty v rámci každé kategorie zvlášť. Pořadí se restartuje na 1 pro každou kategorii, takže můžete identifikovat nejdražší produkt v každé kategorii filtrováním CategoryPriceRank = 1.
Návod
Použijte ROW_NUMBER(), když potřebujete přesně jeden řádek na pořadí (například určení prvních N na skupinu). Použijte RANK() nebo DENSE_RANK() v případě, že potřebujete zachovat informace o vazbách pro účely vytváření sestav.
Použití agregačních funkcí oken
Standardní agregační funkce jako SUM, , AVGCOUNTMIN, a MAX lze je použít jako funkce okna přidáním OVER klauzule. To vám umožní vypočítat agregace při zachování podrobností jednotlivých řádků.
Následující dotaz ukazuje výpočet průběžných součtů a kumulativních agregací:
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;
Sada výsledků vypadá takto:
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
Důležité
Při použití agregačních funkcí okna bez klauzule ORDER BYOVER se funkce vypočítává přes celý oddíl. Přidání ORDER BY vytvoří výpočet průběhu ze začátku oddílu k aktuálnímu řádku.
Definování rámců oken pomocí ROWS a RANGE
Rámečky oken umožňují přesně určit, které řádky vzhledem k aktuálnímu řádku by měly být zahrnuty do výpočtu. Klauzule ROWS počítá fyzické řádky, zatímco RANGE seskupuje řádky se stejnými hodnotami.
Hranice rámce je možné zadat pomocí:
-
UNBOUNDED PRECEDING: Od spuštění oddílu -
n PRECEDING:nřádky před aktuálním řádkem -
CURRENT ROW: Aktuální řádek -
n FOLLOWING:nřádky za aktuálním řádkem -
UNBOUNDED FOLLOWING: Na konec oddílu
Následující dotaz vypočítá klouzavý průměr za poslední tři objednávky:
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;
Sada výsledků vypadá takto:
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
Tento dotaz vypočítá klouzavý průměr ve 3 pořadí tak, že zahrne aktuální řádek a dva řádky před ním. Pro první řádek je k dispozici pouze jedna hodnota, takže se průměr rovná TotalDue. Ve třetím řádku obsahuje okno všechny tři řádky.
Použití analytických funkcí
Analytické funkce umožňují přistupovat k datům z jiných řádků bez použití samoobslužných spojení nebo poddotazů. Tyto funkce jsou užitečné pro analýzu časových řad, detekci trendu a porovnání aktuálních hodnot s historickými nebo budoucími hodnotami. Na rozdíl od agregačních funkcí okna, které shrnují souhrny, analytické funkce načítají konkrétní hodnoty z konkrétních řádků v okně.
LAG() a LEAD() přistupovat k hodnotám z předchozích nebo následujících řádků, například takto:
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;
Sada výsledků vypadá takto:
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() načte hodnotu z předchozího řádku a LEAD() načte z následujícího řádku. Druhý parametr určuje, kolik řádků se má vrátit zpět nebo dopředu (výchozí hodnota je 1) a třetí parametr poskytuje výchozí hodnotu, pokud neexistuje žádný řádek (například pro první řádek s LAG()). Tyto funkce slouží k výpočtu změn v průběhu období, identifikaci trendů nebo detekci anomálií v sekvenčních datech.
FIRST_VALUE() a LAST_VALUE() vrátí hodnoty z prvního nebo posledního řádku v rámci:
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;
Sada výsledků vypadá takto:
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() vrátí hodnotu z prvního řádku v seřazeném okně, což je v tomto případě nejdražší produkt na kategorii.
LAST_VALUE() vrátí nejlevnější, ale vyžaduje explicitní rámec, aby zahrnoval všechny řádky. Tyto funkce vám pomůžou porovnat každý řádek s hodnotami srovnávacích testů, jako je nejvyšší, nejnižší nebo základní hodnota ve skupině.
Poznámka:
LAST_VALUE() vyžaduje explicitní specifikaci rámce, která bude obsahovat řádky za aktuálním řádkem. Bez něj výchozí rámec obsahuje pouze řádky až do aktuálního řádku a vrátí LAST_VALUE() hodnotu aktuálního řádku.
PERCENT_RANK() a CUME_DIST() vypočítají relativní pozici v rámci oddílu:
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;
Sada výsledků vypadá takto:
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() vrátí hodnotu v rozmezí od 0 do 1 určující procento řádků nižší hodnoty (0 znamená nejnižší, jeden znamená nejvyšší).
CUME_DIST() zobrazuje kumulativní rozdělení označující, jaké procento řádků má hodnoty menší nebo rovno aktuálnímu řádku. Tyto funkce použijte pro percentilovou analýzu, identifikaci odlehlých hodnot nebo vytváření distribučních sestav.
Další informace o funkcích oken najdete v tématu Funkce okna (Transact-SQL) a Funkce řazení.