Klauzule OVER byla přidána na SQL Server „zpět“ v SQL Server 2005 a byla rozšířena v SQL Server 2012. Používá se převážně u „okenních funkcí“; jedinou výjimkou je sekvenční funkce DALŠÍ HODNOTA PRO. Klauzule OVER se používá k určení, které řádky z dotazu se použijí na funkci, v jakém pořadí jsou touto funkcí vyhodnoceny a kdy se mají výpočty funkce restartovat. Jelikož se používá ve spojení s dalšími funkcemi a tento článek pojednává konkrétně o klauzuli OVER, bude o těchto funkcích hovořeno pouze v souvislosti s klauzulí OVER v uvedených příkladech.

Syntaxe klauzule OVER je:

<function> OVER ( )

Při pohledu na syntaxi se zdá, že všechny dílčí klauzule jsou volitelné. Ve skutečnosti každá funkce, která může použít klauzuli OVER, určuje, které z dílčích klauzulí jsou povoleny a které jsou požadovány. V závislosti na použité funkci může být klauzule OVER sama o sobě volitelná. Na konci tohoto článku je graf, který ukazuje, které funkce umožňují / vyžadují, které části klauzule OVER.

Klauzule PARTITION BY se používá k rozdělení sady výsledků z dotazu na datové podmnožiny, nebo oddíly. Pokud se klauzule PARTITION BY nepoužije, celá výsledná sada z dotazu je oddíl, který se použije. Použitá funkce okna se aplikuje na každý oddíl zvlášť a výpočet, který funkce provádí, se restartuje pro každý oddíl. Definujete sadu hodnot, které určují oddíl (oddíly), na které se má dotaz rozdělit. Těmito hodnotami mohou být sloupce, skalární funkce, skalární poddotazy nebo proměnné.

Podívejme se například na následující dotaz:

SELECT COUNT(*)FROM .sys.indexes;

Tento dotaz vrací následující sadu výsledků:

Toto je jednoduše počet řádků vrácených dotazem – v tomto případě počet indexů v databázi msdb. Nyní k tomuto dotazu přidáme klauzuli OVER:

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

Zkrácené výsledky jsou:

Tento dotaz vrátí identifikátor_objektu a index_id pro každý index a celkový počet indexů ve výsledkové sadě. Protože nebyla použita klauzule PARTITION BY, byla celá sada výsledků považována za jeden oddíl. Nyní je čas přidat klauzuli PARTITION BY a zjistit, jak to změní výsledky:

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

Zkrácené výsledky jsou:

Tento dotaz vrací řádek pro každý index, ale nyní dotaz určuje klauzuli PARTITION BY sloupce object_id, takže funkce count vrací počet indexů na tomto konkrétním object_id. Klauzule ORDER BY řídí pořadí, ve kterém jsou řádky vyhodnocovány funkcí. To se brzy ukáže. Klauzule ROWS nebo RANGE určuje podmnožinu řádků v oddílu, které mají být použity na funkci. Při použití ROWS nebo RANGE určíte počáteční a konečný bod okna. Povolené hodnoty jsou:

K určení okna existují dvě syntaxe:

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

Je-li zadán pouze počáteční snímek, je výchozím koncovým rámcem CURRENT ROW.

Klíčové slovo UNBOUNDED určuje začátek oddílu (pro PRECEDING) nebo konec oddíl (pro SLEDOVÁNÍ). CURRENT ROW určuje, že aktuální řádek je buď začátkem okna, nebo koncem okna, v závislosti na tom, ve které pozici rámu okna se použije. „N“ určuje počet řádků před aktuálním řádkem (pro PRECEDING ), nebo po aktuálním řádku (pro NÁSLEDUJÍCÍ) použít pro rám okna.

Platné specifikace okna jsou následující:

-- 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

Chcete-li použít klauzuli ROWS nebo RANGE, musíte také zadat klauzuli ORDER BY. Naopak, pokud použijete klauzuli ORDER BY a nezadáte klauzuli ROWS nebo RANGE, pak výchozí ROZSAH MEZI NEBEZPEČNÝM PŘEDCHOZÍM A AKTUÁLNÍM Používá se ROW.

Abychom demonstrovali klauzule ORDER BY a ROWS nebo RANGE, vytvořme několik testovacích dat: dva účty, čtyři data na účet a částku za každé datum. Dotaz zobrazí obě tyto klauze používané různými způsoby:

Tento dotaz vrací následující sadu výsledků:

The “ Sloupec RowNbr ”je použit g Funkce COUNT pro vrácení počtu řádků v oddílu. Oddíl je seřazen podle TranDate a my určujeme okenní rámec všech řádků od začátku oddílu přes aktuální řádek. U prvního řádku je v rámu okna pouze jeden řádek, takže se vrátí hodnota „1“. U druhého řádku jsou nyní v rámu okna dva řádky, takže se vrátí hodnota „2“. A tak dále přes zbývající řádky na tomto účtu.

Protože klauzule PARTITION BY specifikuje účet, dojde při změně účtu k resetování výpočtů funkcí, což lze zjistit zkoumáním řádků druhého účtu ve výsledkové sadě. Toto je příklad „běžící“ agregace, kde agregace vychází z předchozích výpočtů. Příkladem, kdy byste to mohli použít, by bylo, když vypočítáte zůstatek na bankovním účtu po každé transakci (jinak se to nazývá průběžný součet).

Sloupec „DateCount“ provádí počet kolik řádků rozdělených podle data. V tomto příkladu má každý z účtů transakci ve stejných čtyřech datech, takže každé datum má dvě transakce (jedna pro každý účet). To má za následek vrácení hodnoty „2“ pro každý řádek. Je to podobné jako při provádění počítání, které pro dané datum používá GROUP BY; rozdíl spočívá v tom, že se pro každý řádek namísto pouze jednou pro každé datum vrací součet. kdy byste tuto metodu použili, by bylo zobrazit „řádek X z Y“ nebo vypočítat procento aktuálního řádku z celkového počtu.

Sloupec „Last2Count“ provádí počet řádků v rámci oddílu pro aktuální řádek a jeden řádek bezprostředně před ním. Pro první řádek v každém účtu, protože před ním nejsou žádné řádky, je vrácena hodnota „1“. U zbývajících řádků v každém účtu je vrácena hodnota „2“. Toto je příklad agregace „přesunutí“ nebo „posunutí“. Příkladem, kdy byste tuto metodu použili, by byl výpočet bonusu na základě prodej za poslední dva měsíce.

V tomto okamžiku jsem ukázal pouze klauzuli ROWS. Klauzule RANGE funguje podobným způsobem, ale místo toho, aby se s řádky zacházelo pozičně, zabývá se hodnoty vrácené tímto řádkem. Je to proto, že není poziční, že nelze použít klauzule N PRECEDING / FOLLOWING. Podívejme se rychle na rozdíl mezi ROWS a RANGE pomocí obou z nich ve stejném dotazu. Zde máme seznam lidí (řekněme jim DBA) a jejich hodinové sazby. Všimněte si, že řádky s RowIDs 4 & 5 a 12 & 13 mají stejnou sazbu. Dotaz sečte sazby dvakrát, jednou pomocí ROWS a druhý pomocí RANGE:

Tento dotaz vytvoří následující sadu výsledků:

Ve sloupcích SumByRows a SumByRange je klauzule OVER identická s výjimkou klauzule ROWS / RANGE. Všimněte si také, že vzhledem k tomu, že nebyl zadán koncový rozsah, je výchozí použití CURRENT ROW. Jelikož sčítáme plat od začátku sady výsledků přes aktuální řádek, to, co ve skutečnosti počítáme, je průběžný součet sloupce Plat. Ve sloupci SumByRows se hodnota vypočítá pomocí klauzule ROWS a vidíme, že součet aktuálního řádku je plat aktuálního řádku plus součet předchozího řádku. Klauzule RANGE však pracuje s hodnotou sloupce Plat, takže shrnuje všechny řádky se stejným nebo nižším platem. Výsledkem je, že hodnota SumByRange je stejná hodnota pro všechny řádky se stejným platem.

Jedna důležitá poznámka: klauzule ORDER BY v klauzuli OVER řídí pouze pořadí, ve kterém budou použity řádky v oddílu. funkcí okna. Neovládá pořadí konečné sady výsledků. Bez klauzule ORDER BY na samotném dotazu není zaručeno pořadí řádků. Můžete si všimnout, že se váš dotaz může vracet v pořadí podle poslední zadané klauzule OVER – je to způsobeno způsobem, který je aktuálně implementován na serveru SQL Server. Pokud tým serveru SQL Server ve společnosti Microsoft změní způsob, jakým funguje, nemusí již vaše výsledky řadit způsobem, který aktuálně pozorujete. Pokud potřebujete konkrétní objednávku sady výsledků, musíte zadat klauzuli ORDER BY proti samotnému dotazu.

Nakonec je zde tabulka různých funkcí, které mohou klauzuli OVER používat, stejně jako které části klauzule jsou povoleny / povinné / volitelné.

R-povinné, O-volitelné, X-nepřípustné