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> }
A párhuzamos adattárház 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 PARTITION BY nincs megadva, a függvény a lekérdezés eredményhalmazának összes sorát egyetlen partícióként kezeli.
A függvény akkor lesz alkalmazva a partíció összes sorára, ha nem adja meg ORDER BY záradékot.
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 nincs megadva, az alapértelmezett sorrend
ASC, és az ablakfüggvény a partíció összes sorát használja.Ha meg van adva, és nincs megadva egy
ROWSvagyRANGE, akkor az alapértelmezettRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWaz ablakkeret alapértelmezettROWSvagyRANGEspecifikációt elfogadó függvények használják (példáulminvagymax).
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. Egész szám nem adható meg oszlopnév vagy alias megjelenítéséhez.
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. A null értékeket a rendszer a lehető legalacsonyabb értékként kezeli.
SOROK vagy TARTOMÁNY
A: SQL Server 2012 (11.x) és újabb verziókra vonatkozik.
A partíción belüli sorokat további korlátozásokkal korlátozhatja a partíció kezdő és végpontjainak megadásával. Egy sortartományt határoz meg az aktuális sorra vonatkozóan logikai társítás vagy fizikai társítás alapján. A fizikai társítás a ROWS záradék használatával é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. A ROWS BETWEEN 2 PRECEDING AND CURRENT ROW például azt jelenti, hogy a függvény által üzemeltetett sorok ablaka három sornyi méretű, kezdve az aktuális sort megelőző 2 sorral.
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.
UNBOUNDED PRECEDING csak ablak kezdőpontjaként adható meg.
<nem aláírt érték specifikációja> ELŐZŐ
Az aktuális sort megelőző sorok vagy értékek számának jelzéséhez <unsigned value specification> van megadva. 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.
CURRENT ROW megadható kezdő és végpontként is.
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.
UNBOUNDED FOLLOWING csak ablakvégpontként adható meg. 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Ő
A <unsigned value specification> megadva az aktuális sort követendő sorok vagy értékek számának jelzéséhez. Ha <unsigned value specification> FOLLOWING az ablak kezdőpontjaként van megadva, a végpontnak <unsigned value specification> FOLLOWINGkell lennie.
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
Egynél több ablakfüggvény használható egyetlen lekérdezésben 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 PARTITION BY nincs megadva, a függvény a lekérdezés eredményhalmazának összes sorát egyetlen csoportként kezeli.
Important
Ha ROWS vagy RANGE van megadva, és <window frame preceding><window frame extent> (rövid szintaxis), akkor ezt a specifikációt használja az ablakkeret határának kezdőpontja, és CURRENT ROW a határvégponthoz. A ROWS 5 PRECEDING például egyenlő a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Ha nincs megadva ORDER BY, a rendszer a teljes partíciót egy ablakkerethez használja. Ez csak azokra a függvényekre vonatkozik, amelyekhez nincs szükség ORDER BY záradékra. Ha ROWS vagy RANGE nincs megadva, de ORDER BY van megadva, akkor a RANGE UNBOUNDED PRECEDING AND CURRENT ROW az ablakkeret alapértelmezett értékeként szolgál. Ez csak azokra a függvényekre vonatkozik, amelyek elfogadják az opcionális ROWS vagy RANGE specifikációt. A rangsorolási függvények például nem fogadnak el ROWS vagy RANGE, ezért ez az ablakkeret nem lesz alkalmazva annak ellenére, hogy ORDER BY jelen van, és ROWS vagy RANGE nem.
Limitations
A OVER záradék nem használható az DISTINCT összesítésekhez.
RANGE nem használható <unsigned value specification> PRECEDING vagy <unsigned value specification> FOLLOWING.
A OVER záradékkal használt rangsorolási, összesített vagy elemzési függvénytől függően előfordulhat, hogy a <ORDER BY clause> és/vagy a <ROWS and RANGE clause> nem támogatott.
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 azt mutatja be, hogy a OVER záradék és ROW_NUMBER függvény használatával jelenít meg egy sorszámot egy partíció minden egyes sorához. 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 AdventureWorks2022;
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 AdventureWorks2022;
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 a OVER záradékot mutatja be egy számított érték összesítő függvényével.
USE AdventureWorks2022;
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. Az adatokat TerritoryID particionálta, és logikailag az SalesYTDrendezi. 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. Az 1-es TerritoryID két sorból áll, 2005 az adott évben értékesítéssel rendelkező két értékesítőt jelöli. A számítás a két sor átlagos értékesítését számítja ki, majd a 2006 év értékesítését képviselő harmadik sort is belefoglalja a számításba.
USE AdventureWorks2022;
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 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 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 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 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 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 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 2005 559,697.56
287 NULL 519,905.93 2006 1,079,603.50
285 NULL 172,524.45 2007 1,252,127.95
283 1 1,573,012.94 2005 1,573,012.94
280 1 1,352,577.13 2005 2,925,590.07
284 1 1,576,562.20 2006 4,502,152.27
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 4,251,368.55
281 4 2,458,535.62 2005 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