SELECT – OVER záradék (Transact-SQL)

• 2017.11.11.
• 17 perc olvasás
• • V
  • L
  • c
  • j
  • M
  • +11

A következőre vonatkozik: SQL Server (minden Támogatott verziók) div id = “19fbb4c644”> Párhuzamos adattárház

Meghatározza a sorok felosztását és sorrendjét, mielőtt a társított ablak funkciót alkalmaznák. Vagyis az OVER záradék egy ablakot vagy egy felhasználó által megadott sorkészletet határoz meg a lekérdezés eredményhalmazán belül. Ezután egy ablakfüggvény kiszámítja az ablak minden sorának értékét. Az OVER záradékot a függvényekkel olyan összesített értékek kiszámításához használhatja, mint mozgó átlagok, összesített összesítők, futó összesítések vagy csoportonként a felső N.

• Rangsorolási függvények

• Összesítő függvények

• Analitikus függvények

• A NEXT VALUE FOR függvény

Transact-SQL szintaxis konvenciók

Szintaxis

Argumentumok

Az ablakfunkciók OVER záradékában a következő argumentumok lehetnek:

• PARTITION BY, amely partíciókra osztja a lekérdezés eredményhalmazát.
• ORDER BY, amely meghatározza a sorok logikai sorrendjét az eredménykészlet minden partícióján belül.
• ROWS / RANGE, amely korlátozza a partíció sorait a partíción belüli kezdő és végpont megadásával. Ehhez ORDER BY argumentum szükséges, és az alapértelmezett érték a partíció kezdetétől az aktuális elemig terjed, ha a ORDER BY argumentum meg van adva.

Ha nem ad meg argumentumot, akkor az ablakfüggvények a teljes eredményhalmazon lesznek alkalmazva.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

A lekérdezés eredményhalmazát partíciókra osztja. Az ablakfüggvényt minden partícióra külön alkalmazzák, és a számítás újraindul minden partíciónál ion.

Ha a PARTITION BY nincs megadva, a függvény a lekérdezés eredményhalmazának minden sorát egyetlen partícióként kezeli. A függvény minden sorban alkalmazásra kerül. a partícióban, ha nem adja meg a ORDER BY záradékot.

PARTITION BY value_expression

Megadja az oszlopot, amelyen a sor fel van osztva. Az érték_kifejezés csak a FROM záradék által elérhető oszlopokra vonatkozhat. Az érték_kifejezés nem hivatkozhat kifejezésekre vagy álnevekre a kiválasztási listában. Az érték_kifejezés lehet oszlopkifejezés, skaláris részlekérdezés, skalárfüggvény vagy a felhasználó által definiált változó.

RENDELÉS:

Meghatározza a sorok logikai sorrendjét az eredményhalmaz minden partícióján belül. Vagyis megadja az ablakfüggvény számításának logikai sorrendjét.

• Ha nincs megadva, akkor az alapértelmezett sorrend ASC és Az ablak függvény az összes sort a partícióban fogja használni.
• Ha meg van adva, és nincs megadva ROWS / RANGE, akkor az alapértelmezett RANGE UNBOUNDED PRECEDING AND CURRENT ROW értéket használja az ablakhoz keret azokkal a függvényekkel, amelyek elfogadják az opcionális ROWS / RANGE specifikációt (például min vagy max).

order_by_expression
Megad egy oszlopot vagy kifejezést, amelyen rendezni kell. a order_by_expression csak a FROM záradék által elérhető oszlopokra vonatkozhat. Az oszlop nevét vagy álnevét nem lehet egész számmal megadni.

COLLATE collation_name
Megadja, hogy az ORDER BY műveletet a collation_name mezőben megadott rendezés szerint kell végrehajtani. a collation_name lehet egy Windows rendezés neve vagy egy SQL összeállítás neve. További információkért lásd: Összevetés és Unicode támogatás. A COLLATE csak a char, varchar, nchar és nvarchar típusú oszlopokra alkalmazható.

ASC | DESC
Megadja, hogy a megadott oszlopban szereplő értékeket növekvő vagy csökkenő sorrendben kell rendezni. Az ASC az alapértelmezett rendezési sorrend.Az üres értékeket a lehető legalacsonyabb értékként kezeljük.

ROWS vagy RANGE

Érvényes: SQL Server 2012 (11.x) és újabb verziók.

További korlátozások a partíción belüli sorokat a partíción belüli kezdő és végpont megadásával. Ez úgy történik, hogy sorokat határoz meg az aktuális sorhoz képest logikai asszociációval vagy fizikai asszociációval. 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ó sorait azáltal, hogy rögzített számú sort ad meg az aktuális sor előtt vagy után. Alternatív megoldásként a RANGE záradék logikusan korlátozza a partíció sorait azáltal, hogy értéktartományt határoz meg az aktuális sor értékéhez képest. Az előző és a következő sorokat az ORDER BY záradék sorrendje alapján határozzuk meg. A “RANGE … CURRENT ROW …” ablakkeret tartalmazza azokat a sorokat, amelyek az ORDER BY kifejezésben ugyanazokkal az értékekkel rendelkeznek, mint az aktuális sor. Például a 2. ELŐZŐ ÉS A JELENLEGI SOR KÖZÖTTI SOROK azt jelenti, hogy a funkciók által működtetett sorok ablaka három sor méretű, kezdve 2 sorral, amelyek megelőzik az aktuális sort és azt is beleértve.

UNBOUNDED PRECEDING

Érvényes: SQL Server 2012 (11.x) és újabb.

Megadja, hogy az ablak a partíció első sorától induljon. A KÖRNYEZETT ELŐZMÉNY csak ablak kezdőpontként határozható meg.

< aláíratlan érték specifikáció > ELŐZETES
Megadva < aláíratlan érték specifikációval > az aktuális sort megelőző sorok vagy értékek számának jelzésére. Ez a specifikáció nem engedélyezett a RANGE számára.

AKTUÁLIS SOR

Az alábbiakra vonatkozik: SQL Server 2012 (11.x) és újabb verziók.

Megadja, hogy az ablak elinduljon vagy az aktuális sorban végződik, ha ROWS-szal használja, vagy az aktuális értékkel, ha a RANGE-vel együtt használja. A CURRENT ROW kezdő és befejező pontként is megadható.

ÉS

Érvényes: SQL Server 2012 (11.x) és újabb verziók.

A ROWS vagy a RANGE elemekkel együtt használható az ablak alsó (kezdő) és felső (végső) határpontjának megadásához. < ablakkeret kötött > meghatározza a határ kezdőpontját, és < ablakkeret kötött > határozza meg a határ végpontját. A felső határ nem lehet kisebb, mint az alsó határ.

UNBOUNDED FOLLOWING

Érvényes: SQL Server 2012 (11.x) és újabb verziók.

Megadja, hogy az ablak a partíció utolsó sorában ér véget. A KÖRNYEZETT FOLLOWING csak ablak végpontként határozható meg. Például a JELENLEGES SOR KÖZÖTT ÉS A FELTÉTELT KÖVETKEZÉS ALAPÚ ablakot definiál, amely az aktuális sorral kezdődik és a partíció utolsó sorával végződik.

< aláíratlan érték specifikáció > KÖVETKEZŐ
< aláíratlan érték specifikációval megadva > a sorok vagy értékek az aktuális sor követéséhez. Ha < aláíratlan értékspecifikáció > FOLLOWING van megadva ablak kezdőpontként, akkor a végpontnak < aláíratlan érték specifikáció > KÖVETKEZŐ. Például a 2 KÖVETKEZŐ ÉS 10 KÖVETKEZŐ SOROK ablakot határoz meg, amely az aktuális sort követő második sorral kezdődik, és az aktuális sort követő tizedik sorral végződik. Ez a specifikáció nem engedélyezett a RANGE számára.

aláíratlan egész literál
Érvényes: SQL Server 2012 (11.x) és újabb verziók.

Pozitív egész literál (beleértve a 0-t is) ), amely meghatározza az aktuális sort vagy értéket megelőző vagy követő sorok vagy értékek számát. Ez a specifikáció csak a ROWS-ra érvényes.

Általános megjegyzések

Egynél több ablakfunkció használható egyetlen lekérdezésben egyetlen FROM záradékkal. Minden funkció függvényében az OVER záradék különbözhet a felosztásban és a sorrendben.

Ha a PARTITION BY nincs megadva, akkor a függvény a lekérdezés eredményhalmazának minden sorát egyetlen csoportként kezeli.

Fontos !

Ha a ROWS / RANGE meg van adva, és a < ablakkeretet megelőzi a > < ablakkeret kiterjedése > (rövid szintaxis), akkor ezt a specifikációt használják az ablakkeret határ kezdőpontjához, és a CURRENT ROW a határ végpontjához. Például az “5. SOROK ELŐZMÉNYE” egyenlő: “AZ ELŐZŐ SOROK ÉS AZ AKTUÁLIS SOROK KÖZÖTT”.

Korlátozások és korlátozások

Az OVER záradék nem használható a CHECKSUM aggregate függvénnyel.

A RANGE nem használható < aláíratlan érték specifikációval > ELŐZŐ vagy < aláíratlan érték specifikáció > KÖVETKEZŐ.

A rangsorolástól, összesítéstől vagy elemzéstől függően az OVER záradékkal használt függvény, < ORDER BY záradék > és / vagy a < A RIVS és a RANGE záradék > nem biztos, hogy támogatott.

Példák

A. Az OVER záradék használata a ROW_NUMBER függvénnyel

Az alábbi példa az OVER záradék használatát mutatja a ROW_NUMBER függvénnyel, hogy a partíción belül minden sorhoz sorszámot jelenítsen meg. Az OVER záradékban megadott ORDER BY záradék az egyes partíciók sorait a SalesYTD oszlop alapján rendezi meg. A SELECT utasítás ORDER BY záradéka meghatározza a teljes lekérdezés eredményhalmazának sorrendjét.

Itt található az eredménykészlet.

B. Az OVER záradék használata összesített függvényekkel

A következő 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 alkérdezések használata az összesített értékek levezetésére.

Itt található az eredménykészlet.

Az alábbi példa a OVER záradék használatát mutatja egy összesített függvénnyel egy kiszámított értékben.

Itt található az eredménykészlet. Figyelje meg, hogy az aggregátumokat SalesOrderID számítja ki, és a Percent by ProductID számításokat minden SalesOrderID.

C. Mozgó átlag és összesített összeg előállítása

A következő példa az AVG és SUM függvényeket használja az OVER klauzulával, hogy mozgóátlagot és összesített összesített éves összértéket adjon meg az egyes területekre a Sales.SalesPerson táblázat. Az adatokat TerritoryID particionálja, és logikusan a SalesYTD rendezi. Ez azt jelenti, hogy az AVG függvény minden területre kiszámításra kerül az értékesítési év alapján. Figyelje meg, hogy a TerritoryID 1 esetében a 2005-ös értékesítési évre két sor tartozik, amelyek a két értékesítőt képviselik abban az évben. Ennek a két sornak az átlagos eladását kiszámítják, majd a harmadik sor a 2006-os év értékesítését mutatja be a számításba.

Itt található az eredménykészlet.

Ebben a példában az OVER záradék nem tartalmazza a PARTITION BY-t. Ez azt jelenti, hogy a függvény a lekérdezés által visszaadott összes sorra vonatkozik. Az OVER záradékban megadott ORDER BY záradék határozza meg az AVG függvény logikai sorrendjét. A lekérdezés az eladások mozgó átlagát adja vissza évente a WHERE záradékban megadott összes értékesítési területre. A SELECT utasításban megadott ORDER BY záradék meghatározza a lekérdezés sorainak megjelenítési sorrendjét.

Itt található az eredménykészlet.

D. A ROWS záradék megadása

Érvényes: SQL Server 2012 (11.x) és újabb verziók.

A következő példa a ROWS záradékkal definiálja azt az ablakot, amelyre a sorokat kiszámítják az aktuális sor és a következő sorok N száma (ebben a példában 1 sor).

Itt található az eredménykészlet.

A következő példában a ROWS záradék meg van adva a KÖRNYEZETT ELŐZETES. Ennek eredményeként az ablak a partíció első sorától indul.

Itt található az eredménykészlet.

Példák: Párhuzamos adattárház

E. Az OVER záradék használata a ROW_NUMBER függvénnyel

Az alábbi példa a ROW_NUMBER számot adja vissza az értékesítési képviselők számára a hozzájuk rendelt értékesítési kvóta alapján.

Itt van egy részleges eredménykészlet.

F. Az OVER záradék használata összesített függvényekkel

A következő példák az OVER záradék összesített függvényekkel történő használatát mutatják be. Ebben a példában az OVER záradék használata hatékonyabb, mint az alkérdezések használata.

Itt található az eredménykészlet.

A következő példa mutatja az OVER záradékot egy összesített függvénnyel egy számított értékben. Figyelje meg, hogy az aggregátumokat a SalesOrderNumber számítja ki, és a teljes értékesítési megrendelés százalékát kiszámítja az egyes SalesOrderNumber sorokba.

Ennek az eredménykészletnek az első kezdete:

Lásd még:

Összesített függvények (Transact-SQL)
Analitikus függvények (Transact-SQL)
Kiváló blogbejegyzés az ablakfunkciókról és az OVER-ről, az sqlmag.com-on, Itzik Ben-Gan