SELECT - OVER – klauzule (Transact-SQL)
platí pro:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)koncový bod SQL Analytics ve službě Microsoft FabricWarehouse v Microsoft Fabricdatabáze SQL v Microsoft Fabric
Klauzule OVER určuje dělení a řazení sady řádků před použití přidružené funkce okna. To znamená, že klauzule OVER definuje sadu řádků zadaných uživatelem 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í agregace, průběžné součty nebo nejvyšší N výsledků skupiny.
Syntax
Syntaxe PRO SQL Server, Azure SQL Database a Azure Synapse Analytics
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> }
Syntaxe paralelního datového skladu
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Argumenty
Funkce okna můžou mít v klauzuli OVER následující argumenty:
PARTITION BY, která rozdělí sadu výsledků dotazu na oddíly.
ORDER BY, která definuje logické pořadí řádků v rámci každého oddílu sady výsledků.
řádky nebo oblast, které omezují řádky v oddílu zadáním počátečních a koncových bodů v oddílu. Vyžaduje
ORDER BYargument a výchozí hodnota je od začátku oddílu k aktuálnímu prvku, pokud je zadán argumentORDER BY.
Pokud nezadáte žádný argument, funkce okna se použijí na celou sadu výsledků.
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 |
PARTITION BY
Rozdělí sadu výsledků dotazu na oddíly. Funkce okna se použije pro každý oddíl samostatně a pro každý oddíl se restartuje výpočty.
PARTITION BY <value_expression>
Pokud není zadaný PARTITION BY, funkce zachází se všemi řádky sady výsledků dotazu jako s jedním oddílem.
Pokud nezadáte klauzuli ORDER BY, použije se funkce na všechny řádky oddílu.
DĚLENÍ PODLE value_expression
Určuje sloupec, podle kterého je sada řádků rozdělena.
value_expression může odkazovat pouze na sloupce dostupné klauzulí FROM.
value_expression nemůžou odkazovat na výrazy nebo aliasy v seznamu výběrů.
value_expression může být výraz sloupce, skalární poddotaz, skalární funkce nebo uživatelsky definovaná proměnná.
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 | typ | 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 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
Definuje logické pořadí řádků v rámci každého oddílu sady výsledků. To znamená, že určuje logické pořadí, ve kterém se provádí výpočet funkce okna.
Pokud není zadáno, výchozí pořadí je
ASCa funkce okna používá všechny řádky v oddílu.Pokud je zadaný a
ROWSneboRANGEnení zadán, použije se výchozíRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWjako výchozí pro rámec okna funkce, které můžou přijmout volitelnou specifikaciROWSneboRANGE(napříkladminnebomax).
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 | typ | 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
Určuje sloupec nebo výraz, podle kterého se má seřadit.
order_by_expression může odkazovat pouze na sloupce dostupné klauzulí FROM. Celé číslo nelze zadat pro reprezentaci názvu nebo aliasu sloupce.
COLLATION_NAME COLLATE
Určuje, že operace ORDER BY by měla být provedena podle kolace zadané v collation_name.
collation_name může být buď název kolace Systému Windows, nebo název kolace SQL. Další informace najdete v tématu podpora kolace a kódování Unicode.
COLLATE platí pouze pro sloupce typu char, varchar, nchara 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í. Hodnoty Null jsou považovány za nejnižší možné hodnoty.
ŘÁDKY nebo OBLAST
platí pro: SQL Server 2012 (11.x) a novější verze.
Další omezení řádků v oddílu zadáním počátečních a koncových bodů v oddílu. Určuje rozsah řádků s ohledem na aktuální řádek buď logickým přidružením, nebo fyzickým přidružením. Fyzické přidružení se dosahuje pomocí klauzule ROWS.
Klauzule ROWS omezuje řádky v rámci oddílu zadáním pevného počtu řádků předcházejících aktuálnímu řádku 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ě pořadí v klauzuli ORDER BY. Rámec okna RANGE ... CURRENT ROW ... obsahuje všechny řádky, které mají stejné hodnoty ve výrazu ORDER BY jako aktuální řádek. Například ROWS BETWEEN 2 PRECEDING AND CURRENT ROW znamená, že okno řádků, na kterých funkce pracuje, má velikost tří řádků, počínaje 2 řádky předcházejícími a zahrnutím aktuálního řádku.
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 | předchozí | centrální | následující |
|---|---|---|---|
| 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 nebo RANGE vyžaduje, abyste zadali klauzuli ORDER BY. Pokud ORDER BY obsahuje více výrazů pořadí, CURRENT ROW FOR RANGE při určování aktuálního řádku bere v úvahu všechny sloupce v seznamu ORDER BY.
NEVÁZANÉ PŘEDCHOZÍ
platí pro: SQL Server 2012 (11.x) a novější verze.
Určuje, že okno začíná na prvním řádku oddílu.
UNBOUNDED PRECEDING lze zadat pouze jako výchozí bod okna.
<specifikace bez znaménka> PŘEDCHOZÍ
Zadané pomocí <unsigned value specification> označující počet řádků nebo hodnot, které mají předcházet aktuálnímu řádku. Tato specifikace není povolená pro RANGE.
AKTUÁLNÍ ŘÁDEK
platí pro: SQL Server 2012 (11.x) a novější verze.
Určuje, že okno začíná nebo končí na aktuálním řádku při použití s ROWS nebo aktuální hodnotou při použití s RANGE.
CURRENT ROW lze zadat jako počáteční i koncový bod.
BETWEEN AND
platí pro: SQL Server 2012 (11.x) a novější verze.
BETWEEN <window frame bound> AND <window frame bound>
Používá se s ROWS nebo RANGE k určení dolního (počátečního) a horního (koncového) hraničního bodu okna.
<window frame bound> definuje výchozí bod hranice a <window frame bound> definuje koncový bod hranice. Horní mez nemůže být menší než dolní mez.
NEVÁZANÉ NÁSLEDUJÍCÍ
platí pro: SQL Server 2012 (11.x) a novější verze.
Určuje, že okno končí na posledním řádku oddílu.
UNBOUNDED FOLLOWING lze zadat pouze jako koncový bod okna. Například RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definuje okno, které začíná aktuálním řádkem a končí posledním řádkem oddílu.
<specifikace bez znaménka> NÁSLEDUJÍCÍ
Zadané pomocí <unsigned value specification> označující počet řádků nebo hodnot, které mají následovat za aktuálním řádkem. Je-li <unsigned value specification> FOLLOWING zadán jako počáteční bod okna, musí být koncový bod <unsigned value specification> FOLLOWING. Například ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING definuje 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í povolená pro RANGE.
<celočíselné literály bez znaménka>
platí pro: SQL Server 2012 (11.x) a novější verze.
Kladný celočíselná literál (včetně 0), který určuje počet řádků nebo hodnot, které mají předcházet nebo následovat za aktuálním řádkem nebo hodnotou. Tato specifikace je platná pouze pro ROWS.
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 dělení a řazení.
Pokud není zadaný PARTITION BY, funkce zachází se všemi řádky sady výsledků dotazu jako s jednou skupinou.
Důležitý
Pokud je zadán ROWS nebo RANGE a <window frame preceding> se používá pro <window frame extent> (krátká syntaxe), použije se tato specifikace pro počáteční bod rámce okna a CURRENT ROW se použije pro koncový bod hranice. Například ROWS 5 PRECEDING se rovná ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
Pokud není zadaný ORDER BY, použije se pro rámeček okna celý oddíl. To platí jenom pro funkce, které nevyžadují klauzuli ORDER BY. Pokud není zadaný ROWS nebo RANGE, ale ORDER BY je zadaný, RANGE UNBOUNDED PRECEDING AND CURRENT ROW se použije jako výchozí pro rámec okna. To platí jenom pro funkce, které mohou přijímat volitelné ROWS nebo specifikace RANGE. Funkce řazení například nemůžou přijímat ROWS nebo RANGE, proto se tento rámec okna nepoužije, i když ORDER BY je přítomen a ROWS nebo RANGE není.
Omezení
Klauzuli OVER nelze použít s agregacemi DISTINCT.
RANGE nelze použít s <unsigned value specification> PRECEDING ani <unsigned value specification> FOLLOWING.
V závislosti na funkci řazení, agregace nebo analytické funkce používané s klauzulí OVER se nemusí podporovat <ORDER BY clause> nebo <ROWS and RANGE clause>.
Příklady
Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2022 nebo AdventureWorksDW2022, kterou si můžete stáhnout z domovské stránky ukázky a komunitní projekty Microsoft SQL Serveru.
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 rámci oddílu. Klauzule ORDER BY zadaná v klauzuli OVER seřadí řádky v každém oddílu podle sloupce SalesYTD. Klauzule ORDER BY v příkazu SELECT určuje pořadí, ve kterém se vrátí celá sada výsledků dotazu.
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
Tady je sada výsledků.
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. Použití klauzule OVER s agregačními funkcemi
Následující příklad používá klauzuli OVER s agregačními funkcemi pro všechny řádky vrácené dotazem. V tomto příkladu je použití klauzule OVER efektivnější než použití poddotazů k odvození agregovaných hodnot.
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
Tady je sada výsledků.
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
Následující příklad ukazuje použití klauzule OVER s agregační funkcí v počítané hodnotě.
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
Tady je sada výsledků. Agregace se počítají podle SalesOrderID a Percent by ProductID se vypočítá pro každý řádek každého SalesOrderID.
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. Vytvoření 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 poskytnutí klouzavého průměru a kumulativního součtu ročních prodejů pro každé území v tabulce Sales.SalesPerson. Data jsou rozdělena podle TerritoryID a logicky seřazena podle SalesYTD. To znamená, že AVG funkce se vypočítá pro každé území na základě prodejního roku. Pro TerritoryID z 1 jsou dva řádky pro prodejní rok 2005 představující dva prodejce s prodejem za daný rok. Vypočítá se průměrný prodej těchto dvou řádků a třetí řádek představující prodej za rok 2006 se zahrne do výpočtu.
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;
Tady je sada výsledků.
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
V tomto příkladu klauzule OVER neobsahuje PARTITION BY. To znamená, že funkce se použije na všechny řádky vrácené dotazem. Klauzule ORDER BY zadaná v klauzuli OVER určuje logické pořadí, ve kterém se použije AVG funkce. Dotaz vrátí klouzavý průměr prodeje po roce pro všechna prodejní teritoria zadaná v klauzuli WHERE. Klauzule ORDER BY zadaná v příkazu SELECT určuje pořadí zobrazení řádků dotazu.
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;
Tady je sada výsledků.
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. Zadání klauzule ROWS
platí pro: SQL Server 2012 (11.x) a novější verze.
Následující příklad používá klauzuli ROWS k definování okna, přes které se řádky počítají jako aktuální řádek, a N počet řádků, které následují (jeden řádek v tomto příkladu).
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;
Tady je sada výsledků.
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
V následujícím příkladu je klauzule ROWS zadána s UNBOUNDED PRECEDING. Výsledkem je, že okno začíná na prvním řádku oddílu.
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;
Tady je sada výsledků.
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říklady: Systém analytických platforem (PDW)
E. Použití klauzule OVER s funkcí ROW_NUMBER
Následující příklad vrátí ROW_NUMBER pro zástupce prodeje na základě přiřazené prodejní kvóty.
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;
Tady je částečná sada výsledků.
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. 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ů.
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;
Tady je sada výsledků.
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
Následující příklad ukazuje použití klauzule OVER s agregační funkcí v počítané hodnotě. Agregace se počítají podle SalesOrderNumber a procento celkové prodejní objednávky se vypočítá pro každý řádek každého SalesOrderNumber.
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;
První začátek této sady výsledků je následující:
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
Související obsah
- agregační funkce (Transact-SQL)
- analytických funkcí (Transact-SQL)
- Vynikající blogový příspěvek o funkcích oken a OVER, na sqlmag.com, od Itzik Ben-Gan