Az OVER záradékot az SQL Server “visszafelé” adták hozzá az SQL Server 2005-ben, és kibővítették az SQL-ben Server 2012. Főleg az “Ablakfunkciók” -val használják; az egyetlen kivétel a NEXT VALUE FOR szekvenciafüggvény. Az OVER záradék segítségével meghatározható, hogy a lekérdezés mely sorait alkalmazzák a függvényre, milyen sorrendben értékelik azokat a függvények, és mikor kell a függvény számításait újraindítani. Mivel más függvényekkel együtt használják, és ez a cikk kifejezetten csak az OVER klauzuláról szól, ezekről a funkciókról csak akkor fogunk beszélni, mint ami az adott példákban szereplő OVER klauzulára vonatkozik.

A az OVER záradék:

<function> OVER ( )

A szintaxist vizsgálva úgy tűnik, hogy az összes tagmondat nem kötelező. Valójában minden olyan funkció, amely képes használni az OVER klauzulát, meghatározza, hogy melyik albekezdés engedélyezett és melyik szükséges. A használt függvénytől függően maga az OVER záradék is opcionális lehet. A cikk végén található egy diagram, amely megmutatja, hogy mely funkciók engedélyezik / igénylik az OVER záradék egyes részeit.

A PARTITION BY záradék arra szolgál, hogy a lekérdezés eredményhalmazát adathalmazokra ossza fel, vagy partíciók. Ha a PARTITION BY záradékot nem használják, akkor a lekérdezés teljes eredményhalmaza lesz a használni kívánt partíció. A használt ablakfunkciót minden partícióra külön alkalmazzák, és a függvény által végrehajtott számítást minden partíción újraindítják. Megad egy értékkészletet, amely meghatározza azokat a partíciókat, amelyekre fel kell osztani a lekérdezést. Ezek az értékek lehetnek oszlopok, skalárfüggvények, skaláralekérdezések vagy változók.

Vizsgáljuk meg például a következő lekérdezést:

SELECT COUNT(*)FROM .sys.indexes;

Ez a lekérdezés a következő eredménykészletet adja vissza:

Ez egyszerűen a lekérdezés által visszaadott sorok száma – ebben az esetben az msdb adatbázis indexeinek száma. Most adjuk hozzá az OVER záradékot ehhez a lekérdezéshez:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

A rövidített eredmények a következők:

Ez a lekérdezés az egyes indexek objektumazonosítóját és index_azonosítóját adja vissza, valamint az összesített indexet az eredményhalmazban. Mivel a PARTITION BY záradékot nem használták, a teljes eredményhalmazt egyetlen partícióként kezelték. Itt az ideje, hogy hozzáadjuk a PARTITION BY záradékot, és megnézzük, hogy ez hogyan változtatja meg az eredményeket:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

A rövidített eredmények a következők:

Ez a lekérdezés egy sort ad vissza minden indexhez, de a lekérdezés most megadja az object_id oszlop PARTITION BY záradékát, így a count függvény visszaadja az indexek számát azon a bizonyos objektumazonosítón. Az ORDER BY záradék szabályozza a sorrendet, amelyben a sorokat a függvény értékeli. Ez hamarosan be fog mutatkozni. A ROWS vagy RANGE záradék határozza meg a partíción belül a sorok azon részhalmazát, amelyeket alkalmazni kell a függvényre. A ROWS vagy RANGE használatakor meg kell adnia az ablak kezdő és végpontját. A megengedett értékek:

Az ablak megadásához két szintaxis létezik:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Ha csak a kezdő keret van megadva, akkor az alapértelmezett befejező keret a CURRENT ROW.

Az UNBOUNDED kulcsszó adja meg a partíció elejét (PRECEDING számára) vagy a végét a partíció (a KÖVETÉSHEZ). AKTUÁLIS SOR adja meg, hogy az aktuális sor vagy az ablak kezdete, vagy az ablak vége, attól függően, hogy melyik ablakkeret pozícióban használatos. Az „N” sorokat határoz meg az aktuális sor előtt (ELŐZMÉNYEKHEZ) ), vagy az aktuális sor után (a KÖVETKEZŐHEZ) az ablakkerethez.

A következők érvényesek az ablakokra:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

A ROWS vagy RANGE záradék használatához meg kell adnia a ORDER BY záradékot is. Ellenben, ha az ORDER BY záradékot használja, és nem ad meg ROWS vagy RANGE záradékot, akkor az alapértelmezett VÁLTOZATT ELŐZETT ÉS ELJÁRÁS KÖZÖTT A ROW-t használják.

A RENDELÉS ÉS ROWS vagy RANGE záradék bemutatásához hozzunk létre néhány tesztadatot: két fiókot, számlánként négy dátumot és egy összeget minden dátumhoz. A lekérdezés mindkettőt megjeleníti különböző módon használt záradékokat:

Ez a lekérdezés a következő eredményhalmazt adja vissza:

A ” RowNbr ”oszlopot használjuk g a COUNT funkcióval adja vissza, hogy hány sor van a partícióban. A partíciót a TranDate rendezi, és meghatározzuk az összes sor ablakkeretét a partíció kezdetétől az aktuális sorig. Az első sorban csak egy sor van az ablakkeretben, így az “1” értéket adja vissza. A második sorhoz most két sor tartozik az ablakkeretben, így a “2” értéket adja vissza. És így tovább a fiók többi sorában.

Mivel a PARTITION BY záradék megadja a fiókot, a fiók változásakor a függvény számításai visszaállnak, ami az eredményhalmaz második fiókjának sorait vizsgálva látható. Ez egy példa egy “futó” összesítésre, ahol az összesítés a korábbi számításokra épít. Egy példa arra, hogy ezt mikor használná, amikor a bankszámlaegyenlegét minden tranzakció után kiszámítja (más néven futó összeg).

A “DateCount” oszlop megszámolja, hogy hány sor van felosztva a dátum szerint. Ebben a példában mindegyik számlának ugyanazon a négy dátumon van tranzakciója, így mindegyik dátumnak két tranzakciója van (mindegyik számlához egy). Ez azt eredményezi, hogy a “2” értéket adják vissza az egyes sorokhoz. Ez hasonló ahhoz a számláláshoz, amely a GROUP BY-t használja a dátumhoz; a különbség az, hogy az összeget az egyes sorokra adjuk vissza, ahelyett, hogy minden egyes dátumhoz csak egyszer használnánk. amikor ezt a módszert használná, az “Y X sora” megjelenítése, vagy az aktuális sor százalékának kiszámítása az összeghez.

A “Last2Count” oszlop elvégzi a sorok számlálását. a partíción belül, az aktuális és az azt közvetlenül megelőző sor számára. Mivel minden fiók első sorában nincsenek sorok előtte, az “1” értéket adja vissza. Az egyes számlák fennmaradó soraihoz a “2” érték kerül visszaadásra. Ez egy példa egy “mozgó” vagy “csúszó” összesítésre. Például ezt a módszert használhatja, ha a bónuszt kiszámítja a elmúlt két hónap értékesítés.

Ezen a ponton csak a ROWS záradékot mutattam be. A RANGE záradék hasonló módon működik, de ahelyett, hogy a sorokat pozicionálisan kezelné, a a sor által visszaadott értékek. Azért van, mert nem helyzeti, hogy az N ELŐZŐ / KÖVETŐ záradék nem használható. Vessünk egy gyors pillantást a ROWS és a RANGE közötti különbségre, ha mindkettőt ugyanazon lekérdezésben használjuk. emberek listája (nevezzük őket DBA-knak) és óradíjaik. Vegye figyelembe, hogy a 4-es sor & 5 és 12 & sorok 13 azonos arányú. A lekérdezés kétszer összegzi az arányokat, egyszer a ROWS, a másik pedig a RANGE használatával:

Ez a lekérdezés a következő eredménykészletet hozza létre:

A SumByRows és a SumByRange oszlopokban az OVER záradék megegyezik a ROWS / RANGE záradék kivételével. Vegye figyelembe azt is, hogy mivel a végtartomány nem volt megadva, az alapértelmezett a CURRENT ROW használata. Mivel a fizetést a beállított eredmény elejétől az aktuális soron át összegezzük, amit valóban kiszámítunk, az a Fizetés oszlop futóösszege. A SumByRows oszlopban az érték kiszámítása a ROWS záradék segítségével történik, és láthatjuk, hogy az aktuális sor összege az aktuális sor Fizetése plusz az előző sor összege. A RANGE záradék azonban a Fizetés oszlop értékéből működik, így az összes sort azonos vagy alacsonyabb fizetéssel összesíti. Ez azt eredményezi, hogy a SumByRange értéke ugyanaz az érték minden azonos fizetésű sor esetében.

Egy fontos megjegyzés: az OVER záradék ORDER BY záradéka csak a partíció sorainak felhasználási sorrendjét szabályozza. az ablak funkcióval. Nem ellenőrzi a végeredmény halmazának sorrendjét. A lekérdezés ORDER BY záradéka nélkül a sorok sorrendje nem garantált. Észreveheti, hogy a lekérdezés a legutóbb megadott OVER záradék sorrendjében térhet vissza – ez annak a módjának köszönhető, hogy ezt jelenleg megvalósítják az SQL Server rendszerben. Ha a Microsoft SQL Server csapata megváltoztatja a működését, előfordulhat, hogy már nem rendezi az eredményeket az Ön által megfigyelt módon. Ha konkrét sorrendre van szüksége az eredményhalmazhoz, akkor magának a lekérdezésnek meg kell adnia egy ORDER BY záradékot.

Végül itt van egy diagram az OVER záradékot használó különféle funkciókról, valamint a záradék mely részei engedélyezettek / kötelezőek / opcionálisak.

R-szükséges, O-opcionális, X-nem engedélyezett