Megjegyzés
Az oldalhoz való hozzáféréshez engedély szükséges. Megpróbálhat bejelentkezni vagy módosítani a címtárat.
Az oldalhoz való hozzáféréshez engedély szükséges. Megpróbálhatja módosítani a címtárat.
A következőkre vonatkozik:
SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse AnalyticsElemzési platformrendszer (PDW)SQL Analytics-végpont a Microsoft FabricbenRaktár a Microsoft FabricbenSQL-adatbázis a Microsoft Fabricben
A OVER záradék határozza meg egy sorhalmaz particionálását és sorrendjét a társított ablakfüggvény alkalmazása előtt. Ez azt jelenti, hogy a OVER záradék egy ablak- vagy felhasználó által megadott sorkészletet határoz meg egy lekérdezés eredményhalmazában. Az ablakfüggvény ezután kiszámít egy értéket az ablak minden egyes sorához. A OVER záradék függvényekkel használható olyan összesített értékek kiszámításához, mint a mozgóátlagok, az összesített összesítések, a futó összegek vagy az N felső csoportonkénti eredményei.
Transact-SQL szintaxis konvenciói
Syntax
Szintaxis az SQL Serverhez, az Azure SQL Database-hez és az Azure Synapse Analyticshez.
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ , ...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{ <window frame preceding>
| <window frame between>
}
<window frame between> ::=
BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{ <window frame preceding>
| <window frame following>
}
<window frame preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification> PRECEDING
| CURRENT ROW
}
<window frame following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification> FOLLOWING
| CURRENT ROW
}
<unsigned value specification> ::=
{ <unsigned integer literal> }
Az Analytics Platform System (PDW) szintaxisa:
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Arguments
Az ablakfüggvények a következő argumentumokkal rendelkezhetnek a OVER záradékban:
PARTITION BY, amely partíciókra osztja a lekérdezés eredményhalmazát.
ORDER BY, amely meghatározza az eredményhalmaz egyes partícióiban lévő sorok logikai sorrendjét.
SOR VAGY TARTOMÁNY, amelyek a partíción belüli sorokat a partíció kezdő és végpontjainak megadásával korlátozzák. Ehhez
ORDER BYargumentumra van szükség, és az alapértelmezett érték a partíció elejétől az aktuális elemig van megadva, ha aORDER BYargumentum meg van adva.
Ha nem ad meg argumentumot, az ablakfüggvények a teljes eredményhalmazra lesznek alkalmazva.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | min | max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTÍCIÓ
A lekérdezés eredményhalmazát partíciókra osztja. Az ablakfüggvényt a rendszer külön alkalmazza az egyes partíciókra, és az egyes partíciók számítási újraindulnak.
PARTITION BY <value_expression>
Ha nem adja meg PARTITION BY, a függvény a lekérdezés eredményhalmazának összes sorát egyetlen partícióként kezeli.
Ha nem ad meg záradékot ORDER BY , a függvény a partíció összes sorára lesz alkalmazva.
PARTÍCIÓ VALUE_EXPRESSION SZERINT
Azt az oszlopot adja meg, amellyel a sorhalmaz particionálása történik.
value_expression csak a FROM záradék által elérhető oszlopokra hivatkozhat.
value_expression nem hivatkozhat a kijelölési listában szereplő kifejezésekre vagy aliasokra.
value_expression lehet oszlopkifejezés, skaláris alquery, skaláris függvény vagy felhasználó által definiált változó.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type) AS [min],
MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
| object_id | típus | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
Rendezés szerint
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Meghatározza az eredményhalmaz egyes partícióiban lévő sorok logikai sorrendjét. Ez azt a logikai sorrendet határozza meg, amelyben az ablakfüggvény számítása történik.
Ha nem ad meg rendelést, az alapértelmezett sorrend az
ASCablakfüggvény a partíció összes sorát használja.Ha rendelést ad meg, de nem adja meg, vagy
RANGEha az opcionálisROWSvagyRANGEspecifikációt elfogadó függvények (példáulMINMAX) alapértelmezett ablakkeretként használhatókRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.ROWS
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
| object_id | típus | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
Megadja a rendezendő oszlopot vagy kifejezést.
order_by_expression csak a FROM záradék által elérhetővé tett oszlopokra hivatkozhat. Nem adhat meg egész számot, amely oszlopnevet vagy aliast jelöl.
COLLATION_NAME RENDEZÉSE
Megadja, hogy a ORDER BY műveletet a collation_name.
collation_name lehet Windows-rendezési név vagy SQL-rendezés neve. További információ: rendezés és Unicode-támogatás.
COLLATE csak karakteres, varchar, ncharés nvarcharoszlopra alkalmazható.
{ ASC | DESC }
Megadja, hogy a megadott oszlop értékeit növekvő vagy csökkenő sorrendbe kell rendezni.
ASC az alapértelmezett rendezési sorrend.
NULL értékek a lehető legalacsonyabb értékek.
SOROK vagy TARTOMÁNY
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
Ezek a beállítások tovább korlátozzák a partíción belüli sorokat a partíció kezdő és végpontjának megadásával. Az aktuális sorhoz kapcsolódó sortartományt logikai társítással vagy fizikai társítással adhatja meg. A záradék használatával ROWS fizikai társítás érhető el.
A ROWS záradék korlátozza a partíción belüli sorokat az aktuális sort megelőző vagy követő sorok rögzített számának megadásával. Másik lehetőségként a RANGE záradék logikailag korlátozza a partíción belüli sorokat úgy, hogy egy értéktartományt ad meg az aktuális sorban lévő értékhez képest. Az előző és a következő sorok a ORDER BY záradékban szereplő sorrend alapján vannak definiálva. Az ablakkeret RANGE ... CURRENT ROW ... tartalmazza az összes olyan sort, amely ugyanazokat az értékeket tartalmazza a ORDER BY kifejezésben, mint az aktuális sor. Ez azt jelenti például, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW hogy a függvény által üzemeltetett sorok ablaka három sornyi méretű, kezdve az aktuális sort megelőző két sortal.
SELECT object_id,
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
| object_id | preceding | central | following |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
ROWS vagy RANGE meg kell adnia a ORDER BY záradékot. Ha ORDER BY több rendelési kifejezést tartalmaz, CURRENT ROW FOR RANGE az aktuális sor meghatározásakor figyelembe veszi a ORDER BY lista összes oszlopát.
KORLÁTLAN ELŐZMÉNY
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
Megadja, hogy az ablak a partíció első sorában kezdődik. Csak az ablak kezdőpontjaként adható meg UNBOUNDED PRECEDING .
<nem aláírt érték specifikációja> ELŐZŐ
<unsigned value specification> Adja meg az aktuális sort megelőző sorok vagy értékek számát. Ez a specifikáció nem engedélyezett RANGEesetében.
JELENLEGI SOR
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
Azt adja meg, hogy az ablak az aktuális sortól indul vagy végződik, ha ROWS vagy az aktuális értéket használja RANGE. Kezdő és záró pontként is megadható CURRENT ROW .
KÖZÖTT ÉS
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
BETWEEN <window frame bound> AND <window frame bound>
ROWS vagy RANGE az ablak alsó (kezdő) és felső (záró) határpontjainak megadásához használható.
<window frame bound> meghatározza a határ kezdőpontját, <window frame bound> pedig a határvégpontot. A felső határ nem lehet kisebb, mint az alsó határ.
KORLÁTLAN KÖVETŐTÁBOROK
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
Megadja, hogy az ablak a partíció utolsó sorában végződik-e. Csak ablakvégpontként adható meg UNBOUNDED FOLLOWING . Például RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definiál egy ablakot, amely az aktuális sorból indul ki, és a partíció utolsó sorával végződik.
<nem aláírt érték specifikációja> KÖVETKEZŐ
<unsigned value specification> Adja meg az aktuális sort követendő sorok vagy értékek számát. Amikor az ablak kezdőpontját adja meg <unsigned value specification> FOLLOWING , a végpontnak meg kell lennie <unsigned value specification> FOLLOWING.
ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING például egy olyan ablakot határoz meg, amely az aktuális sort követő második sorból indul ki, és az aktuális sort követő tizedik sortal végződik. Ez a specifikáció nem engedélyezett RANGEesetében.
<nem aláírt egész szám literális>
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
Pozitív egész számkonstans (beleértve a 0) az aktuális sort vagy értéket megelőző vagy követő sorok vagy értékek számát határozza meg. Ez a specifikáció csak ROWSérvényes.
Remarks
Egyetlen lekérdezésben több ablakfüggvényt is használhat egyetlen FROM záradékkal. Az egyes függvények OVER záradéka a particionálásban és a rendezésben eltérhet.
Ha nem adja meg PARTITION BY, a függvény a lekérdezés eredményhalmazának összes sorát egyetlen csoportként kezeli.
Important
Ha (rövid szintaxist) ad meg ROWS vagy RANGE használ <window frame preceding><window frame extent> , a lekérdezés ezt a specifikációt használja az ablakkeret határának kezdőpontjához és CURRENT ROW a határvégponthoz. A ROWS 5 PRECEDING például egyenlő a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Ha nem adja meg ORDER BY, a rendszer a teljes partíciót egy ablakkerethez használja. Ez a szabály csak olyan függvényekre vonatkozik, amelyekhez nincs szükség záradékra ORDER BY . Ha nem adja meg ROWS vagy RANGE nem adja meg ORDER BY, RANGE UNBOUNDED PRECEDING AND CURRENT ROW akkor a rendszer az ablakkeret alapértelmezett elemét használja. Ez a szabály csak olyan függvényekre vonatkozik, amelyek nem kötelező ROWS vagy RANGE specifikációt is elfogadnak. A rangsorolási függvények például nem fogadhatók el ROWS , így RANGEez az ablakkeret akkor sem lesz alkalmazva, ha ORDER BY jelen van, vagy ROWSRANGE nem.
Limitations
A záradék nem használható OVER az DISTINCT összesítésekkel.
Nem használható RANGE a következővel <unsigned value specification> PRECEDING : vagy <unsigned value specification> FOLLOWING.
ORDER BY A záradékok és a ROWSRANGE záradékok támogatása a záradékkal OVER használt rangsorolási, összesített vagy elemzési függvénytől függ.
Examples
A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.
A. A OVER záradék használata a ROW_NUMBER függvénnyel
Az alábbi példa bemutatja, hogyan használhatja a OVER záradékot a ROW_NUMBER függvénnyel egy sorszám megjelenítésére egy partíción belül. A ORDER BY záradékban megadott OVER záradék az egyes partíciók sorait az SalesYTDoszlop szerint rendezi. A ORDER BY utasítás SELECT záradéka határozza meg, hogy a teljes lekérdezési eredményhalmaz milyen sorrendben lesz visszaadva.
USE AdventureWorks2025;
GO
SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName,
s.SalesYTD,
a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
GO
Itt van az eredményhalmaz.
Row Number LastName SalesYTD PostalCode
--------------- ----------------------- --------------------- ----------
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
B. Az OVER záradék használata összesítő függvényekkel
Az alábbi példa a OVER záradékot használja összesítő függvényekkel a lekérdezés által visszaadott összes sorra. Ebben a példában a OVER záradék használata hatékonyabb, mint az aggregátumok használata az összesített értékek kinyeréséhez.
USE AdventureWorks2025;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg",
COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Count",
MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min",
MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Itt van az eredményhalmaz.
SalesOrderID ProductID OrderQty Total Avg Count Min Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659 776 1 26 2 12 1 6
43659 777 3 26 2 12 1 6
43659 778 1 26 2 12 1 6
43659 771 1 26 2 12 1 6
43659 772 1 26 2 12 1 6
43659 773 2 26 2 12 1 6
43659 774 1 26 2 12 1 6
43659 714 3 26 2 12 1 6
43659 716 1 26 2 12 1 6
43659 709 6 26 2 12 1 6
43659 712 2 26 2 12 1 6
43659 711 4 26 2 12 1 6
43664 772 1 14 1 8 1 4
43664 775 4 14 1 8 1 4
43664 714 1 14 1 8 1 4
43664 716 1 14 1 8 1 4
43664 777 2 14 1 8 1 4
43664 771 3 14 1 8 1 4
43664 773 1 14 1 8 1 4
43664 778 1 14 1 8 1 4
Az alábbi példa bemutatja, hogyan használhatja a OVER záradékot egy összesítő függvénnyel számított értékben.
USE AdventureWorks2025;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
CAST (1. * OrderQty / SUM(OrderQty) OVER (PARTITION BY SalesOrderID) * 100 AS DECIMAL (5, 2)) AS [Percent by ProductID]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
Itt van az eredményhalmaz. Az összesítéseket a SalesOrderID számítja ki, a Percent by ProductID pedig az egyes SalesOrderIDsoraihoz.
SalesOrderID ProductID OrderQty Total Percent by ProductID
------------ ----------- -------- ----------- ---------------------------------------
43659 776 1 26 3.85
43659 777 3 26 11.54
43659 778 1 26 3.85
43659 771 1 26 3.85
43659 772 1 26 3.85
43659 773 2 26 7.69
43659 774 1 26 3.85
43659 714 3 26 11.54
43659 716 1 26 3.85
43659 709 6 26 23.08
43659 712 2 26 7.69
43659 711 4 26 15.38
43664 772 1 14 7.14
43664 775 4 14 28.57
43664 714 1 14 7.14
43664 716 1 14 7.14
43664 777 2 14 14.29
43664 771 3 14 21.4
43664 773 1 14 7.14
43664 778 1 14 7.14
C. Mozgó átlag és összegző összeg előállítása
Az alábbi példa a AVG és SUM függvényeket használja a OVER záradékkal, hogy a Sales.SalesPerson táblában lévő egyes területek mozgóátlagát és összesített összesített éves értékesítését adja meg. A lekérdezés particionálja az adatokat, és logikailag rendezi SalesYTDazokatTerritoryID. Ez azt jelenti, hogy a AVG függvényt az értékesítési év alapján számítjuk ki az egyes területekre. Ebben TerritoryID az 1esetben két sor létezik az értékesítési évhez 2022, amely azt a két értékesítőt jelöli, akik az adott évben értékesítettek. A számítás a két sor átlagos értékesítését számítja ki, majd a 2023 év értékesítését képviselő harmadik sort is belefoglalja a számításba.
USE AdventureWorks2025;
GO
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear;
Itt van az eredményhalmaz.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2021 559,697.56 559,697.56 559,697.56
287 NULL 2023 519,905.93 539,801.75 1,079,603.50
285 NULL 2024 172,524.45 417,375.98 1,252,127.95
283 1 2022 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2022 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2023 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2022 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2022 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2022 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2022 2,458,535.62 3,354,952.08 6,709,904.17
Ebben a példában a OVER záradék nem tartalmazza PARTITION BY. Ez azt jelenti, hogy a függvény a lekérdezés által visszaadott összes sorra lesz alkalmazva. A ORDER BY záradékban megadott OVER záradék határozza meg a AVG függvény alkalmazásának logikai sorrendjét. A lekérdezés az értékesítések évenkénti mozgó átlagát adja vissza az WHERE záradékban megadott összes értékesítési terület esetében. A ORDER BY utasításban megadott SELECT záradék határozza meg a lekérdezés sorainak megjelenítésének sorrendjét.
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY SalesYear;
Itt van az eredményhalmaz.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2021 559,697.56 559,697.56 559,697.56
275 2 2022 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2022 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2022 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2022 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2022 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2022 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2023 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2023 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2024 172,524.45 1,941,678.09 19,416,780.93
D. A SOROK záradék megadása
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
Az alábbi példa a ROWS záradék használatával határoz meg egy ablakot, amelyen a sorok az aktuális sorként vannak kiszámítva, és az N az alábbi sorok számát (ebben a példában egy sort).
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Itt van az eredményhalmaz.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2021 1,079,603.50
287 NULL 519,905.93 2023 692,430.38
285 NULL 172,524.45 2024 172,524.45
283 1 1,573,012.94 2022 2,925,590.07
280 1 1,352,577.13 2022 2,929,139.33
284 1 1,576,562.20 2023 1,576,562.20
275 2 3,763,178.18 2022 3,763,178.18
277 3 3,189,418.37 2022 3,189,418.37
276 4 4,251,368.55 2022 6,709,904.17
281 4 2,458,535.62 2022 2,458,535.62
Az alábbi példában a ROWS záradék UNBOUNDED PRECEDINGvan megadva. Az eredmény az, hogy az ablak a partíció első sorában kezdődik.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS UNBOUNDED PRECEDING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
Itt van az eredményhalmaz.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2021 559,697.56
287 NULL 519,905.93 2023 1,079,603.50
285 NULL 172,524.45 2024 1,252,127.95
283 1 1,573,012.94 2022 1,573,012.94
280 1 1,352,577.13 2022 2,925,590.07
284 1 1,576,562.20 2023 4,502,152.27
275 2 3,763,178.18 2022 3,763,178.18
277 3 3,189,418.37 2022 3,189,418.37
276 4 4,251,368.55 2022 4,251,368.55
281 4 2,458,535.62 2022 6,709,904.17
Példák: Elemzési platformrendszer (PDW)
E. A OVER záradék használata a ROW_NUMBER függvénnyel
Az alábbi példa az értékesítési képviselők ROW_NUMBER adja vissza a hozzárendelt értékesítési kvótájuk alapján.
SELECT ROW_NUMBER() OVER (ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName,
LastName,
CONVERT (VARCHAR (13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Íme egy részleges eredményhalmaz.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. Az OVER záradék használata összesítő függvényekkel
Az alábbi példák a OVER záradék összesítő függvényekkel való használatát mutatják be. Ebben a példában a OVER záradék használata hatékonyabb, mint az al lekérdezések használata.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
AVG(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS AVG,
COUNT(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS COUNT,
MIN(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MIN,
MAX(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MAX
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Itt van az eredményhalmaz.
OrderNumber Product Qty Total Avg Count Min Max
----------- ------- --- ----- --- ----- --- ---
SO43659 218 6 16 3 5 1 6
SO43659 220 4 16 3 5 1 6
SO43659 223 2 16 3 5 1 6
SO43659 229 3 16 3 5 1 6
SO43659 235 1 16 3 5 1 6
SO43664 229 1 2 1 2 1 1
SO43664 235 1 2 1 2 1 1
Az alábbi példa a OVER záradékot mutatja be egy számított érték összesítő függvényével. Az összesítéseket SalesOrderNumber számítja ki, és a teljes értékesítési rendelés százalékos értékét az egyes SalesOrderNumbersoraihoz számítja ki.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey AS Product,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
CAST (1. * OrderQuantity / SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) * 100 AS DECIMAL (5, 2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
Az eredményhalmaz első kezdete a következő:
OrderNumber Product Qty Total PctByProduct
----------- ------- --- ----- ------------
SO43659 218 6 16 37.50
SO43659 220 4 16 25.00
SO43659 223 2 16 12.50
SO43659 229 2 16 18.75