SELECT — klauzula OVER (Transact-SQL)

Artykuł
03/11/2025

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)punkt końcowy analizy SQL w usłudze Microsoft Fabric Warehouse w usłudze Microsoft Fabric SQL Database w usłudze Microsoft Fabric

Klauzula OVER określa partycjonowanie i kolejność zestawu wierszy przed zastosowaniem skojarzonej funkcji okna. Oznacza to, że klauzula OVER definiuje okno lub określony przez użytkownika zestaw wierszy w zestawie wyników zapytania. Następnie funkcja okna oblicza wartość dla każdego wiersza w oknie. Możesz użyć klauzuli OVER z funkcjami, aby obliczyć zagregowane wartości, takie jak średnie ruchome, skumulowane agregacje, sumy bieżące lub pierwsze N na wyniki grupy.

Transact-SQL konwencje składni

Składnia

Składnia dla programu SQL Server, usługi Azure SQL Database i usługi 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> }

Składnia dla magazynu danych równoległych.

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Argumenty

Funkcje okien mogą mieć następujące argumenty w klauzuli OVER:

PARTITION BY dzielący zestaw wyników zapytania na partycje.
ORDER BY definiujące logiczną kolejność wierszy w każdej partycji zestawu wyników.
wiersze lub zakres, które ograniczają wiersze w partycji przez określenie punktów początkowych i końcowych w partycji. Wymaga ORDER BY argumentu, a wartość domyślna to od początku partycji do bieżącego elementu, jeśli określono argument ORDER BY.

Jeśli nie określisz żadnego argumentu, funkcje okna zostaną zastosowane dla całego zestawu wyników.

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

PARTYCJONOWANIE WEDŁUG

Dzieli zestaw wyników zapytania na partycje. Funkcja okna jest stosowana do każdej partycji oddzielnie, a ponowne uruchamianie obliczeń dla każdej partycji.

PARTITION BY <value_expression>

Jeśli PARTITION BY nie zostanie określony, funkcja traktuje wszystkie wiersze zestawu wyników zapytania jako jedną partycję.

Funkcja jest stosowana we wszystkich wierszach w partycji, jeśli nie określisz klauzuli ORDER BY.

PARTYCJONOWANIE WEDŁUG value_expression

Określa kolumnę, według której zestaw wierszy jest partycjonowany. value_expression mogą odwoływać się tylko do kolumn udostępnianych przez klauzulę FROM. value_expression nie może odwoływać się do wyrażeń ani aliasów na liście wyboru. value_expression może być wyrażeniem kolumny, podzapytaniem skalarnym, funkcją skalarną lub zmienną zdefiniowaną przez użytkownika.

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 ]

Definiuje logiczną kolejność wierszy w każdej partycji zestawu wyników. Oznacza to, że określa kolejność logiczną, w której jest wykonywane obliczenie funkcji okna.

Jeśli nie zostanie określony, kolejność domyślna to ASC, a funkcja okna używa wszystkich wierszy w partycji.
Jeśli zostanie określona, a ROWS lub RANGE nie zostanie określona, domyślna RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW jest używana jako domyślna dla ramki okna przez funkcje, które mogą zaakceptować opcjonalną specyfikację ROWS lub RANGE (na przykład min lub max).

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

Określa kolumnę lub wyrażenie do sortowania. order_by_expression może odwoływać się tylko do kolumn udostępnionych przez klauzulę FROM. Nie można określić liczby całkowitej do reprezentowania nazwy kolumny lub aliasu.

SORTOWANIE collation_name

Określa, że operację ORDER BY należy wykonać zgodnie z sortowaniem określonym w collation_name. collation_name może być nazwą sortowania systemu Windows lub nazwą sortowania SQL. Aby uzyskać więcej informacji, zobacz obsługa sortowania i Unicode. COLLATE dotyczy tylko kolumn typu char, varchar, nchari nvarchar.

ASC | DESC

Określa, że wartości w określonej kolumnie powinny być sortowane w kolejności rosnącej lub malejącej. ASC jest domyślną kolejnością sortowania. Wartości null są traktowane jako najniższe możliwe wartości.

WIERSZE lub ZAKRES

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje.

Dodatkowo ogranicza wiersze w partycji, określając punkty początkowe i końcowe w partycji. Określa zakres wierszy w odniesieniu do bieżącego wiersza według skojarzenia logicznego lub skojarzenia fizycznego. Skojarzenie fizyczne jest osiągane przy użyciu klauzuli ROWS.

Klauzula ROWS ogranicza wiersze w partycji przez określenie stałej liczby wierszy poprzedzających lub po bieżącym wierszu. Alternatywnie klauzula RANGE logicznie ogranicza wiersze w partycji, określając zakres wartości w odniesieniu do wartości w bieżącym wierszu. Poprzednie i następujące wiersze są definiowane na podstawie kolejności w klauzuli ORDER BY. Ramka okna RANGE ... CURRENT ROW ... zawiera wszystkie wiersze, które mają te same wartości w wyrażeniu ORDER BY co bieżący wiersz. Na przykład ROWS BETWEEN 2 PRECEDING AND CURRENT ROW oznacza, że okno wierszy, na których działa funkcja, to trzy wiersze o rozmiarze, począwszy od 2 wierszy poprzedzających wiersze aż do i włącznie z bieżącym wierszem.

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	poprzedzający	centralny	następujący
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 lub RANGE wymaga określenia klauzuli ORDER BY. Jeśli ORDER BY zawiera wiele wyrażeń zamówień, CURRENT ROW FOR RANGE uwzględnia wszystkie kolumny na liście ORDER BY podczas określania bieżącego wiersza.

NIEZWIĄZANE POPRZEDNIE

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje.

Określa, że okno rozpoczyna się w pierwszym wierszu partycji. UNBOUNDED PRECEDING można określić tylko jako punkt początkowy okna.

<niepodpisanej specyfikacji wartości> POPRZEDNIE

Określony za pomocą <unsigned value specification>, aby wskazać liczbę wierszy lub wartości, które mają poprzedzać bieżący wiersz. Ta specyfikacja nie jest dozwolona dla RANGE.

BIEŻĄCY WIERSZ

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje.

Określa, że okno rozpoczyna się lub kończy się w bieżącym wierszu, gdy jest używane z ROWS lub bieżącą wartością, gdy jest używana z RANGE. CURRENT ROW można określić jako punkt początkowy i końcowy.

MIĘDZY I

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje.

BETWEEN <window frame bound> AND <window frame bound>

Używany z ROWS lub RANGE, aby określić dolne (początkowe) i górne (kończące) punkty graniczne okna. <window frame bound> definiuje punkt początkowy granicy i <window frame bound> definiuje punkt końcowy granicy. Górna granica nie może być mniejsza niż dolna granica.

NIEZWIĄZANE Z NASTĘPUJĄCYMI

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje.

Określa, że okno kończy się w ostatnim wierszu partycji. UNBOUNDED FOLLOWING można określić tylko jako punkt końcowy okna. Na przykład RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING definiuje okno, które rozpoczyna się od bieżącego wiersza i kończy się ostatnim wierszem partycji.

<specyfikacja niepodpisanej wartości> NASTĘPUJĄCE

Określony za pomocą <unsigned value specification>, aby wskazać liczbę wierszy lub wartości, które mają być zgodne z bieżącym wierszem. Po określeniu <unsigned value specification> FOLLOWING jako punktu początkowego okna punkt końcowy musi być <unsigned value specification> FOLLOWING. Na przykład ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING definiuje okno rozpoczynające się od drugiego wiersza, które następuje po bieżącym wierszu i kończy się dziesiątym wierszem, który następuje po bieżącym wierszu. Ta specyfikacja nie jest dozwolona dla RANGE.

<literału liczby całkowitej bez znaku>

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje.

Literał dodatniej liczby całkowitej (w tym 0), który określa liczbę wierszy lub wartości, które mają poprzedzać lub podążać za bieżącym wierszem lub wartością. Ta specyfikacja jest prawidłowa tylko dla ROWS.

Uwagi

W jednym zapytaniu można użyć więcej niż jednej funkcji okna z pojedynczą klauzulą FROM. Klauzula OVER dla każdej funkcji może się różnić w partycjonowaniu i porządkoweniu.

Jeśli PARTITION BY nie zostanie określona, funkcja traktuje wszystkie wiersze zestawu wyników zapytania jako pojedynczą grupę.

Ważny

Jeśli określono ROWS lub RANGE i <window frame preceding> jest używana do <window frame extent> (krótka składnia), ta specyfikacja jest używana dla punktu początkowego granicy okna i CURRENT ROW jest używana dla punktu końcowego granicy granicy. Na przykład ROWS 5 PRECEDING jest równa ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.

Jeśli nie określono ORDER BY, cała partycja jest używana dla ramki okna. Dotyczy to tylko funkcji, które nie wymagają ORDER BY klauzuli. Jeśli nie określono ROWS lub RANGE, ale określono ORDER BY, RANGE UNBOUNDED PRECEDING AND CURRENT ROW jest używana jako domyślna dla okna. Dotyczy to tylko funkcji, które mogą zaakceptować opcjonalną specyfikację ROWS lub RANGE. Na przykład funkcje klasyfikacji nie mogą akceptować ROWS ani RANGE, dlatego ta ramka okna nie jest stosowana, mimo że ORDER BY jest obecna, a ROWS lub RANGE nie jest.

Ograniczenia

Klauzula OVER nie może być używana z agregacjami DISTINCT.

RANGE nie można używać z <unsigned value specification> PRECEDING ani <unsigned value specification> FOLLOWING.

W zależności od klasyfikacji, agregacji lub funkcji analitycznej używanej z klauzulą OVER<ORDER BY clause> i/lub <ROWS and RANGE clause> mogą nie być obsługiwane.

Przykłady

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

A. Używanie klauzuli OVER z funkcją ROW_NUMBER

W poniższym przykładzie pokazano użycie klauzuli OVER z funkcją ROW_NUMBER, aby wyświetlić liczbę wierszy dla każdego wiersza w partycji. Klauzula ORDER BY określona w klauzuli OVER porządkuje wiersze w każdej partycji według kolumny SalesYTD. Klauzula ORDER BY w instrukcji SELECT określa kolejność zwracania całego zestawu wyników zapytania.

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

Oto zestaw wyników.

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. Używanie klauzuli OVER z funkcjami agregowanymi

W poniższym przykładzie użyto klauzuli OVER z funkcjami agregowanymi we wszystkich wierszach zwracanych przez zapytanie. W tym przykładzie użycie klauzuli OVER jest bardziej wydajne niż używanie podzapytania do uzyskiwania wartości zagregowanych.

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

Oto zestaw wyników.

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

W poniższym przykładzie pokazano użycie klauzuli OVER z funkcją agregacji w wartości obliczeniowej.

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

Oto zestaw wyników. Agregacje są obliczane przez SalesOrderID, a Percent by ProductID jest obliczana dla każdego wiersza każdego 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. Tworzenie średniej ruchomej i sumy skumulowanej

W poniższym przykładzie użyto funkcji AVG i SUM z klauzulą OVER, aby zapewnić średnią ruchomą i skumulowaną sumę sprzedaży rocznej dla każdego terytorium w tabeli Sales.SalesPerson. Dane są partycjonowane przez TerritoryID i logicznie uporządkowane przez SalesYTD. Oznacza to, że funkcja AVG jest obliczana dla każdego terytorium na podstawie roku sprzedaży. W przypadku TerritoryID z 1 istnieją dwa wiersze dla roku sprzedaży 2005 reprezentujące dwie osoby sprzedaży z sprzedażą w tym roku. Średnia sprzedaż tych dwóch wierszy jest obliczana, a następnie trzeci wiersz reprezentujący sprzedaż dla roku 2006 jest uwzględniony w obliczeniach.

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;

Oto zestaw wyników.

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

W tym przykładzie klauzula OVER nie zawiera PARTITION BY. Oznacza to, że funkcja jest stosowana do wszystkich wierszy zwracanych przez zapytanie. Klauzula ORDER BY określona w klauzuli OVER określa kolejność logiczną, do której jest stosowana funkcja AVG. Zapytanie zwraca średnią ruchomą sprzedaży według roku dla wszystkich terytoriów sprzedaży określonych w klauzuli WHERE. Klauzula ORDER BY określona w instrukcji SELECT określa kolejność wyświetlania wierszy zapytania.

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;

Oto zestaw wyników.

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. Określanie klauzuli ROWS

Dotyczy: SQL Server 2012 (11.x) i nowsze wersje.

W poniższym przykładzie użyto klauzuli ROWS, aby zdefiniować okno, w którym wiersze są obliczane jako bieżący wiersz, a N liczbę wierszy, które następują (jeden wiersz w tym przykładzie).

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;

Oto zestaw wyników.

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

W poniższym przykładzie klauzula ROWS jest określona z UNBOUNDED PRECEDING. Wynikiem jest to, że okno rozpoczyna się w pierwszym wierszu partycji.

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;

Oto zestaw wyników.

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

Przykłady: Analytics Platform System (PDW)

E. Używanie klauzuli OVER z funkcją ROW_NUMBER

Poniższy przykład zwraca ROW_NUMBER dla przedstawicieli sprzedaży na podstawie przypisanego limitu przydziału sprzedaży.

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;

Oto zestaw wyników częściowych.

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. Używanie klauzuli OVER z funkcjami agregowanymi

W poniższych przykładach pokazano użycie klauzuli OVER z funkcjami agregowanymi. W tym przykładzie użycie klauzuli OVER jest bardziej wydajne niż używanie podzapytania.

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;

Oto zestaw wyników.

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

W poniższym przykładzie pokazano użycie klauzuli OVER z funkcją agregacji w wartości obliczeniowej. Agregacje są obliczane przez SalesOrderNumber, a procent całkowitego zamówienia sprzedaży jest obliczany dla każdego wiersza każdego 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;

Pierwszy początek tego zestawu wyników jest następujący:

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

funkcje agregujące (Transact-SQL)
funkcje analityczne (Transact-SQL)
Doskonały wpis w blogu na temat funkcji okien i OVER, na sqlmag.com, przez Itzik Ben-Gan

Udostępnij za pośrednictwem

SELECT — klauzula OVER (Transact-SQL)

Składnia

Argumenty

PARTYCJONOWANIE WEDŁUG

PARTYCJONOWANIE WEDŁUG value_expression

ORDER BY

order_by_expression

SORTOWANIE collation_name

ASC | DESC

WIERSZE lub ZAKRES

NIEZWIĄZANE POPRZEDNIE

<niepodpisanej specyfikacji wartości> POPRZEDNIE

BIEŻĄCY WIERSZ

MIĘDZY I

NIEZWIĄZANE Z NASTĘPUJĄCYMI

<specyfikacja niepodpisanej wartości> NASTĘPUJĄCE

<literału liczby całkowitej bez znaku>

Uwagi

Ograniczenia

Przykłady

A. Używanie klauzuli OVER z funkcją ROW_NUMBER

B. Używanie klauzuli OVER z funkcjami agregowanymi

C. Tworzenie średniej ruchomej i sumy skumulowanej

D. Określanie klauzuli ROWS

Przykłady: Analytics Platform System (PDW)

E. Używanie klauzuli OVER z funkcją ROW_NUMBER

F. Używanie klauzuli OVER z funkcjami agregowanymi

Opinia

Dodatkowe zasoby

Udostępnij za pośrednictwem

SELECT — klauzula OVER (Transact-SQL)

Składnia

Argumenty

PARTYCJONOWANIE WEDŁUG

PARTYCJONOWANIE WEDŁUG value_expression

ORDER BY

order_by_expression

SORTOWANIE collation_name

ASC | DESC

WIERSZE lub ZAKRES

NIEZWIĄZANE POPRZEDNIE

<niepodpisanej specyfikacji wartości> POPRZEDNIE

BIEŻĄCY WIERSZ

MIĘDZY I

NIEZWIĄZANE Z NASTĘPUJĄCYMI

<specyfikacja niepodpisanej wartości> NASTĘPUJĄCE

<literału liczby całkowitej bez znaku>

Uwagi

Ograniczenia

Przykłady

A. Używanie klauzuli OVER z funkcją ROW_NUMBER

B. Używanie klauzuli OVER z funkcjami agregowanymi

C. Tworzenie średniej ruchomej i sumy skumulowanej

D. Określanie klauzuli ROWS

Przykłady: Analytics Platform System (PDW)

E. Używanie klauzuli OVER z funkcją ROW_NUMBER

F. Używanie klauzuli OVER z funkcjami agregowanymi

Powiązana zawartość

Opinia

Dodatkowe zasoby