SELECT – OVER Clause (Transact-SQL) (Čeština)

• 08/11/2017
• 17 minut ke čtení
• • V
  • L
  • c
  • j
  • M
  • +11

Platí pro: SQL Server (vše podporované verze) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Parallel Data Warehouse

Určuje rozdělení a řazení sady řádků před použitím přidružené funkce okna. To znamená, že klauzule OVER definuje okno nebo uživatelem zadanou sadu řádků v sadě výsledků dotazu. Funkce okna pak vypočítá hodnotu pro každý řádek v okně. Klauzuli OVER s funkcemi můžete použít k výpočtu agregovaných hodnot, jako jsou klouzavé průměry, kumulativní agregáty, průběžné součty nebo nejvyšší N na výsledky skupiny.

• Ranking functions

• Agregační funkce

• Analytické funkce

• DALŠÍ HODNOTA PRO funkci

Konvence syntaxe jazyka Transact-SQL

Syntaxe

Argumenty

Funkce okna mohou mít ve své klauzuli OVER následující argumenty:

• PARTITION BY, která rozděluje sadu výsledků dotazu na oddíly.
• ORDER BY, které definuje logické pořadí řádků v každém oddílu výsledkové sady.
• ROWS / RANGE, který omezuje řádky v oddílu zadáním počátečních a koncových bodů v oddílu. Vyžaduje argument ORDER BY a výchozí hodnota je od začátku oddílu do aktuálního prvku, pokud je zadán argument ORDER BY.

Pokud nezadáte žádný argument, funkce okna se použijí na celou sadu výsledků.

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

PARTITION BY

Rozdělí sadu výsledků dotazu na oddíly. Funkce okna se použije na každý oddíl zvlášť a výpočet se restartuje pro každý partit ion.

Pokud není zadán PARTITION BY, funkce považuje všechny řádky sady výsledků dotazu za jeden oddíl. Funkce bude použita na všechny řádky v oddílu, pokud nezadáte klauzuli ORDER BY.

PARTITION BY value_expression

Určuje sloupec, podle kterého je sada řádků rozdělena. value_expression může odkazovat pouze na sloupce zpřístupněné klauzulí FROM. value_expression nemůže odkazovat na výrazy nebo aliasy v seznamu pro výběr. value_expression může být sloupcový výraz, skalární poddotaz, skalární funkce nebo uživatelem definovaná proměnná.

OBJEDNAT

Definuje logické pořadí řádků v každém oddílu výsledkové sady. To znamená, že určuje logické pořadí, ve kterém se provádí výpočet funkce okna.

• Pokud není zadán, výchozí pořadí je ASC a funkce okna použije všechny řádky v oddílu.
• Pokud je zadán a není zadán ROWS / RANGE, použije se jako výchozí pro okno výchozí RANGE UNBOUNDED PRECEDING AND CURRENT ROW rámec podle funkcí, které mohou přijmout volitelnou specifikaci ROWS / RANGE (například min nebo max).

order_by_expression
Určuje sloupec nebo výraz, podle kterého se mají seřadit. order_by_expression může odkazovat pouze na sloupce zpřístupněné klauzulí FROM. Nelze zadat celé číslo, které by představovalo název nebo alias sloupce.

COLLATE collation_name
Určuje, že operace ORDER BY by měla být provedena podle řazení uvedeného v collation_name. collation_name může být buď název řazení Windows, nebo název řazení SQL. Další informace najdete v tématu podpora řazení a Unicode. COLLATE je použitelné pouze pro sloupce typu char, varchar, nchar a nvarchar.

ASC | DESC
Určuje, že hodnoty v zadaném sloupci by měly být seřazeny vzestupně nebo sestupně. ASC je výchozí pořadí řazení.S nulovými hodnotami se zachází jako s nejnižšími možnými hodnotami.

ROWS or RANGE

Platí pro: SQL Server 2012 (11.x) a novější.

Další omezení řádky v oddílu zadáním počátečních a koncových bodů v oddílu. To se provádí zadáním rozsahu řádků s ohledem na aktuální řádek buď logickým přidružením nebo fyzickým přidružením. Fyzické asociace je dosaženo použitím klauzule ROWS.

Klauzule ROWS omezuje řádky v oddílu zadáním pevného počtu řádků před nebo za aktuálním řádkem. Alternativně klauzule RANGE logicky omezuje řádky v oddílu zadáním rozsahu hodnot s ohledem na hodnotu v aktuálním řádku. Předchozí a následující řádky jsou definovány na základě řazení v klauzuli ORDER BY. Rámeček okna „RANGE … CURRENT ROW …“ zahrnuje všechny řádky, které mají ve výrazu ORDER BY stejné hodnoty jako aktuální řádek. Například ŘÁDKY MEZI 2 PŘEDBĚŽNÝMI A AKTUÁLNÍMI ŘÁDKY znamenají, že okno řádků, na kterých tato funkce pracuje, má tři řádky, počínaje 2 řádky předcházejícími až po aktuální řádek.

NEBEZPEČNÉ PRECEDOVÁNÍ

Platí pro: SQL Server 2012 (11.x) a novější.

Určuje, že okno začíná v prvním řádku oddílu. NEBEZPEČNÉ PŘEDCHÁZENÍ lze zadat pouze jako výchozí bod okna.

< specifikace nepodepsané hodnoty > PŘEDCHOZÍ
specifikováno s < specifikací nepodepsané hodnoty > k označení počtu řádků nebo hodnot před aktuálním řádkem. Tato specifikace není pro RANGE povolena.

CURRENT ROW

Platí pro: SQL Server 2012 (11.x) a novější.

Určuje, že se okno spustí nebo končí na aktuálním řádku při použití s ROWS nebo aktuální hodnota při použití s RANGE. AKTUÁLNÍ ŘADU lze určit jako počáteční i konečný bod.

MEZI A

Platí pro: SQL Server 2012 (11.x) a novější.

Používá se buď s ROWS, nebo RANGE k určení dolních (počátečních) a horních (koncových) hraničních bodů okna. < ohraničení rámečku okna > definuje počáteční hraniční bod a < ohraničení rámečku okna > definuje hraniční koncový bod. Horní hranice nemůže být menší než dolní hranice.

NEBEZPEČNÉ SLEDOVÁNÍ

Platí pro: SQL Server 2012 (11.x) a novější.

Určuje, že okno končí v poslední řadě oddílu. NEBEZPEČNÉ SLEDOVÁNÍ lze určit pouze jako koncový bod okna. Například ROZSAH MEZI AKTUÁLNÍM ŘÁDKEM A NEBEZPEČNÝM SLEDOVÁNÍ definuje okno, které začíná aktuálním řádkem a končí posledním řádkem oddílu.

< specifikace nepodepsané hodnoty > SLEDOVÁNÍ
Určeno < specifikací nepodepsané hodnoty > k označení počtu řádky nebo hodnoty, které sledují aktuální řádek. Pokud je jako počáteční bod okna zadána < specifikace nepodepsané hodnoty > FOLLOWING, musí být koncový bod < Specifikace nepodepsané hodnoty > SLEDOVÁNÍ. Například ŘÁDY MEZI 2 SLEDUJÍCÍMI A 10 SLEDUJÍCÍmi definují okno, které začíná druhým řádkem, který následuje za aktuálním řádkem, a končí desátým řádkem, který následuje za aktuálním řádkem. Tato specifikace není pro RANGE povolena.

celočíselný literál bez znaménka
Platí pro: SQL Server 2012 (11.x) a novější.

Je kladný celočíselný literál (včetně 0 ), který určuje počet řádků nebo hodnot, které mají předcházet nebo sledovat aktuální řádek nebo hodnotu. Tato specifikace je platná pouze pro ROWS.

Obecné poznámky

V jednom dotazu s jednou klauzulí FROM lze použít více než jednu funkci okna. Klauzule OVER pro každou funkci se může lišit v rozdělení a řazení.

Pokud není zadáno PARTITION BY, funkce považuje všechny řádky sady výsledků dotazu za jednu skupinu.

Důležité !

Pokud je zadán ROWS / RANGE a pro iv id = „je použit < okenní rám před > f12c526403 „> rozsah rámu okna > (krátká syntaxe), pak se tato specifikace použije pro počáteční bod hranice okna a pro koncový bod hranice se použije CURRENT ROW. Například „ŘADY 5 PRECEDOVÁNÍ“ se rovná „ŘÁDKY MEZI 5 PŘEDCHÁZEJÍCÍMI A AKTUÁLNÍMI ŘÁDKY“.

Omezení a omezení

Klauzuli OVER nelze použít s agregační funkcí CHECKSUM.

RANGE nelze použít se < specifikací nepodepsané hodnoty > PRECEDING nebo < specifikace nepodepsané hodnoty > SLEDOVÁNÍ.

V závislosti na hodnocení, agregaci nebo analytice funkce použitá s klauzulí OVER, < klauzule ORDER BY > a / nebo < Klauzule ROWS and RANGE > nemusí být podporována.

Příklady

A. Použití klauzule OVER s funkcí ROW_NUMBER

Následující příklad ukazuje použití klauzule OVER s funkcí ROW_NUMBER k zobrazení čísla řádku pro každý řádek v oddílu. Klauzule ORDER BY uvedená v klauzuli OVER objednává řádky v každém oddílu podle sloupce SalesYTD. Klauzule ORDER BY v příkazu SELECT určuje pořadí, ve kterém je vrácena celá sada výsledků dotazu.

Zde je sada výsledků.

B. Použití klauzule OVER s agregačními funkcemi

Následující příklad používá klauzuli OVER s agregačními funkcemi ve všech řádcích vrácených dotazem. V tomto příkladu je použití klauzule OVER efektivnější než použití poddotazů k odvození agregovaných hodnot.

Zde je sada výsledků.

Následující příklad ukazuje použití klauzule OVER s agregovanou funkcí ve vypočítané hodnotě.

Zde je sada výsledků. Všimněte si, že agregáty se počítají pomocí SalesOrderID a Percent by ProductID se počítá pro každý řádek každého SalesOrderID.

C. Produkce klouzavého průměru a kumulativního součtu

Následující příklad používá funkce AVG a SUM s klauzulí OVER k zajištění klouzavého průměru a kumulativního součtu ročních prodejů pro každé území v Sales.SalesPerson tabulka. Data jsou rozdělena podle TerritoryID a logicky uspořádána podle SalesYTD. To znamená, že funkce AVG je počítána pro každé území na základě roku prodeje. Všimněte si, že pro TerritoryID 1 existují dva řádky pro prodejní rok 2005, které představují dva prodejní lidi s prodejem v daném roce. Vypočítají se průměrné tržby za tyto dva řádky a do výpočtu se poté zahrne třetí řádek představující tržby za rok 2006.

Zde je sada výsledků.

V tomto příkladu klauzule OVER nezahrnuje PARTITION BY. To znamená, že funkce bude použita na všechny řádky vrácené dotazem. Klauzule ORDER BY uvedená v klauzuli OVER určuje logické pořadí, ve kterém je funkce AVG použita. Dotaz vrací klouzavý průměr prodejů podle roku pro všechna prodejní teritoria uvedená v klauzuli WHERE. Klauzule ORDER BY uvedená v příkazu SELECT určuje pořadí, ve kterém jsou zobrazeny řádky dotazu.

Zde je sada výsledků.

D. Určení klauzule ROWS

Platí pro: SQL Server 2012 (11.x) a novější.

Následující příklad používá klauzuli ROWS k definování okna, přes které jsou řádky počítány jako aktuální řádek a počet N řádků, které následují (v tomto příkladu 1 řádek).

Zde je sada výsledků.

V následujícím příkladu je klauzule ROWS zadána s NEBEZPEČNÝM PRECEDINGEM. Výsledkem je, že okno začíná na prvním řádku oddílu.

Zde je sada výsledků.

Příklady: Parallel Data Warehouse

E. Použití klauzule OVER s funkcí ROW_NUMBER

Následující příklad vrátí ROW_NUMBER pro obchodní zástupce na základě jejich přidělené prodejní kvóty.

Zde je částečná sada výsledků.

F. Použití klauzule OVER s agregačními funkcemi

Následující příklady ukazují použití klauzule OVER s agregačními funkcemi. V tomto příkladu je použití klauzule OVER efektivnější než použití poddotazů.

Zde je sada výsledků.

Následující příklad ukazuje použití klauzule OVER s agregační funkcí ve vypočítané hodnotě. Všimněte si, že agregáty se počítají pomocí SalesOrderNumber a procento z celkové prodejní objednávky se počítá pro každý řádek každého SalesOrderNumber.

První začátek této sady výsledků je:

Viz také

Agregační funkce (Transact-SQL)
Analytické funkce (Transact-SQL)
Vynikající blogový příspěvek o funkcích oken a NAD, na sqlmag.com, od Itzika Ben-Gana