SELECT – OVER-sats (Transact-SQL)

2025-03-11

Gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analysplattformssystem (PDW)SQL-analysslutpunkt i Microsoft Fabric Lager i Microsoft Fabric SQL-databas i Förhandsversion av Microsoft Fabric

Satsen OVER avgör partitionering och ordning för en raduppsättning innan den associerade fönsterfunktionen tillämpas. Det innebär att OVER-satsen definierar ett fönster eller en användardefinierad uppsättning rader i en frågeresultatuppsättning. En fönsterfunktion beräknar sedan ett värde för varje rad i fönstret. Du kan använda OVER-satsen med funktioner för att beräkna aggregerade värden, till exempel glidande medelvärden, kumulativa aggregeringar, löpande summor eller högsta N per gruppresultat.

Transact-SQL syntaxkonventioner

Syntax

Syntax för SQL Server, Azure SQL Database och 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> }

Syntax för Parallel Data Warehouse.

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Arguments

Fönsterfunktioner kan ha följande argument i sin OVER-sats:

PARTITION BY som delar frågeresultatuppsättningen i partitioner.
ORDER BY som definierar den logiska ordningen för raderna i varje partition i resultatuppsättningen.
RADER eller INTERVALL som begränsar raderna i partitionen genom att ange start- och slutpunkter i partitionen. Det kräver ORDER BY argument och standardvärdet är från början av partitionen till det aktuella elementet om argumentet ORDER BY anges.

Om du inte anger något argument tillämpas fönsterfunktionerna på hela resultatuppsättningen.

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

Delar upp frågeresultatuppsättningen i partitioner. Fönsterfunktionen tillämpas på varje partition separat och beräkningen startas om för varje partition.

PARTITION BY <value_expression>

Om PARTITION BY inte anges behandlar funktionen alla rader i frågeresultatuppsättningen som en enda partition.

Funktionen tillämpas på alla rader i partitionen om du inte anger ORDER BY-sats.

PARTITION EFTER value_expression

Anger den kolumn med vilken raduppsättningen partitioneras. value_expression kan bara referera till kolumner som görs tillgängliga av FROM-satsen. value_expression kan inte referera till uttryck eller alias i urvalslistan. value_expression kan vara ett kolumnuttryck, en skalär underfråga, en skalär funktion eller en användardefinierad variabel.

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

Definierar den logiska ordningen för raderna i varje partition i resultatuppsättningen. Det innebär att den anger den logiska ordning i vilken beräkningen av fönsterfunktionen utförs.

Om den inte anges är standardordningen ASC och fönsterfunktionen använder alla rader i partitionen.
Om det anges och en ROWS eller RANGE inte anges används standard RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW som standard för fönsterramen av de funktioner som kan acceptera en valfri ROWS eller RANGE specifikation (till exempel min eller 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	type	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

Anger en kolumn eller ett uttryck som ska sorteras på. order_by_expression kan bara referera till kolumner som görs tillgängliga av FROM-satsen. Det går inte att ange ett heltal för att representera ett kolumnnamn eller alias.

COLLATE collation_name

Anger att den ORDER BY åtgärden ska utföras enligt sorteringen som anges i collation_name. collation_name kan vara antingen ett Windows-sorteringsnamn eller ett SQL-sorteringsnamn. Mer information finns i Sortering och Unicode-stöd. COLLATE gäller endast för kolumner av typen char, varchar, ncharoch nvarchar.

ASC | DESC

Anger att värdena i den angivna kolumnen ska sorteras i stigande eller fallande ordning. ASC är standardsorteringsordningen. Null-värden behandlas som de lägsta möjliga värdena.

RADER eller INTERVALL

gäller för: SQL Server 2012 (11.x) och senare versioner.

Ytterligare begränsar raderna i partitionen genom att ange start- och slutpunkter i partitionen. Den anger ett radintervall med avseende på den aktuella raden, antingen efter logisk association eller fysisk association. Fysisk association uppnås med hjälp av ROWS-satsen.

Satsen ROWS begränsar raderna i en partition genom att ange ett fast antal rader före eller efter den aktuella raden. Alternativt begränsar RANGE-satsen raderna i en partition logiskt genom att ange ett värdeintervall med avseende på värdet i den aktuella raden. Föregående och följande rader definieras baserat på ordningen i ORDER BY-satsen. Fönsterramen RANGE ... CURRENT ROW ... innehåller alla rader som har samma värden i ORDER BY uttryck som den aktuella raden. Till exempel innebär ROWS BETWEEN 2 PRECEDING AND CURRENT ROW att fönstret med rader som funktionen fungerar på är tre rader i storlek, från och med 2 rader som föregår till och med den aktuella raden.

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	preceding	central	following
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 eller RANGE kräver att du anger ORDER BY-satsen. Om ORDER BY innehåller flera orderuttryck tar CURRENT ROW FOR RANGE hänsyn till alla kolumner i listan ORDER BY när den aktuella raden fastställs.

UNBOUNDED PRECEDING

gäller för: SQL Server 2012 (11.x) och senare versioner.

Anger att fönstret startar på den första raden i partitionen. UNBOUNDED PRECEDING kan bara anges som startpunkt för fönstret.

<osignerad värdespecifikation> FÖREGÅENDE

Anges med <unsigned value specification> för att ange antalet rader eller värden som ska föregå den aktuella raden. Den här specifikationen är inte tillåten för RANGE.

CURRENT ROW

gäller för: SQL Server 2012 (11.x) och senare versioner.

Anger att fönstret startar eller slutar på den aktuella raden när det används med ROWS eller det aktuella värdet när det används med RANGE. CURRENT ROW kan anges som både en start- och slutpunkt.

BETWEEN AND

gäller för: SQL Server 2012 (11.x) och senare versioner.

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

Används med antingen ROWS eller RANGE för att ange de nedre (start) och övre (avslutande) gränspunkterna i fönstret. <window frame bound> definierar gränsstartpunkten och <window frame bound> definierar gränsslutpunkten. Den övre gränsen får inte vara mindre än den nedre gränsen.

UNBOUNDED FOLLOWING

gäller för: SQL Server 2012 (11.x) och senare versioner.

Anger att fönstret slutar på den sista raden i partitionen. UNBOUNDED FOLLOWING kan bara anges som en fönsterslutpunkt. Till exempel definierar RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ett fönster som börjar med den aktuella raden och slutar med den sista raden i partitionen.

<osignerad värdespecifikation> FÖLJANDE

Anges med <unsigned value specification> för att ange antalet rader eller värden som ska följa den aktuella raden. När <unsigned value specification> FOLLOWING anges som startpunkt för fönstret måste slutpunkten vara <unsigned value specification> FOLLOWING. Till exempel definierar ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING ett fönster som börjar med den andra raden som följer den aktuella raden och slutar med den tionde raden som följer den aktuella raden. Den här specifikationen är inte tillåten för RANGE.

<osignerad heltalsliteral>

gäller för: SQL Server 2012 (11.x) och senare versioner.

En positiv heltal (inklusive 0) som anger antalet rader eller värden som ska föregå eller följa den aktuella raden eller värdet. Den här specifikationen är endast giltig för ROWS.

Remarks

Fler än en fönsterfunktion kan användas i en enda fråga med en enda FROM-sats. Satsen OVER för varje funktion kan skilja sig åt i partitionering och ordning.

Om PARTITION BY inte anges behandlar funktionen alla rader i frågeresultatuppsättningen som en enda grupp.

Important

Om ROWS eller RANGE anges och <window frame preceding> används för <window frame extent> (kort syntax) används den här specifikationen för startpunkten för fönstrets ramgräns och CURRENT ROW används för gränsslutpunkten. Till exempel är ROWS 5 PRECEDING lika med ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.

Om ORDER BY inte anges används hela partitionen för en fönsterram. Detta gäller endast funktioner som inte kräver ORDER BY-satsen. Om ROWS eller RANGE inte har angetts men ORDER BY har angetts används RANGE UNBOUNDED PRECEDING AND CURRENT ROW som standard för fönsterram. Detta gäller endast funktioner som kan acceptera valfria ROWS eller RANGE specifikation. Rangordningsfunktioner kan till exempel inte acceptera ROWS eller RANGE, därför tillämpas inte den här fönsterramen trots att ORDER BY finns och ROWS eller RANGE inte är det.

Limitations

Satsen OVER kan inte användas med DISTINCT sammansättningar.

RANGE kan inte användas med <unsigned value specification> PRECEDING eller <unsigned value specification> FOLLOWING.

Beroende på vilken rangordning, aggregering eller analysfunktion som används med OVER-satsen kanske <ORDER BY clause> och/eller <ROWS and RANGE clause> inte stöds.

Examples

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

A. Använd OVER-satsen med funktionen ROW_NUMBER

I följande exempel visas hur du använder OVER-satsen med funktionen ROW_NUMBER för att visa ett radnummer för varje rad i en partition. Den ORDER BY-sats som anges i OVER-satsen beställer raderna i varje partition efter kolumnen SalesYTD. Satsen ORDER BY i instruktionen SELECT avgör i vilken ordning hela frågeresultatuppsättningen returneras.

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

Här är resultatuppsättningen.

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. Använda OVER-satsen med aggregerade funktioner

I följande exempel används OVER-satsen med aggregerade funktioner över alla rader som returneras av frågan. I det här exemplet är det effektivare att använda OVER-satsen än att använda underfrågor för att härleda aggregerade värden.

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

Här är resultatuppsättningen.

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

I följande exempel visas hur du använder OVER-satsen med en mängdfunktion i ett beräknat värde.

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

Här är resultatuppsättningen. Aggregeringarna beräknas av SalesOrderID och Percent by ProductID beräknas för varje rad i varje 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. Producera ett glidande medelvärde och en kumulativ summa

I följande exempel används funktionerna AVG och SUM med satsen OVER för att tillhandahålla ett glidande medelvärde och en ackumulerad total total försäljning per år för varje område i tabellen Sales.SalesPerson. Data partitioneras av TerritoryID och ordnas logiskt av SalesYTD. Det innebär att funktionen AVG beräknas för varje område baserat på försäljningsåret. För TerritoryID på 1 finns det två rader för försäljningsåret 2005 som representerar de två säljare med försäljning det året. Den genomsnittliga försäljningen för dessa två rader beräknas och sedan inkluderas den tredje raden som representerar försäljning för året 2006 i beräkningen.

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;

Här är resultatuppsättningen.

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

I det här exemplet innehåller OVER-satsen inte PARTITION BY. Det innebär att funktionen tillämpas på alla rader som returneras av frågan. Den ORDER BY-sats som anges i OVER-satsen avgör den logiska ordning som funktionen AVG tillämpas på. Frågan returnerar ett glidande medelvärde av försäljningen per år för alla försäljningsterritorier som anges i WHERE-satsen. Den ORDER BY-sats som anges i SELECT-instruktionen avgör i vilken ordning frågans rader visas.

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;

Här är resultatuppsättningen.

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. Ange ROWS-satsen

gäller för: SQL Server 2012 (11.x) och senare versioner.

I följande exempel används ROWS-satsen för att definiera ett fönster där raderna beräknas som den aktuella raden och N antal rader som följer (en rad i det här exemplet).

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;

Här är resultatuppsättningen.

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

I följande exempel anges ROWS-satsen med UNBOUNDED PRECEDING. Resultatet är att fönstret börjar på den första raden i partitionen.

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;

Här är resultatuppsättningen.

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

Exempel: Analysplattformssystem (PDW)

E. Använd OVER-satsen med funktionen ROW_NUMBER

I följande exempel returneras ROW_NUMBER för säljare baserat på deras tilldelade försäljningskvot.

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;

Här är en partiell resultatuppsättning.

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. Använda OVER-satsen med aggregerade funktioner

I följande exempel visas hur du använder OVER-satsen med aggregerade funktioner. I det här exemplet är det effektivare att använda OVER-satsen än att använda underfrågor.

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;

Här är resultatuppsättningen.

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

I följande exempel visas hur du använder OVER-satsen med en mängdfunktion i ett beräknat värde. Aggregeringarna beräknas med SalesOrderNumber och procentandelen av den totala försäljningsordern beräknas för varje rad i varje 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;

Den första starten av den här resultatuppsättningen är följande:

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

Feedback

Var den här sidan till hjälp?

Dela via

SELECT – OVER-sats (Transact-SQL)

Syntax

Arguments

PARTITION BY

PARTITION EFTER value_expression

ORDER BY

order_by_expression

COLLATE collation_name

ASC | DESC

RADER eller INTERVALL

UNBOUNDED PRECEDING

<osignerad värdespecifikation> FÖREGÅENDE

CURRENT ROW

BETWEEN AND

UNBOUNDED FOLLOWING

<osignerad värdespecifikation> FÖLJANDE

<osignerad heltalsliteral>

Remarks

Limitations

Examples

A. Använd OVER-satsen med funktionen ROW_NUMBER

B. Använda OVER-satsen med aggregerade funktioner

C. Producera ett glidande medelvärde och en kumulativ summa

D. Ange ROWS-satsen

Exempel: Analysplattformssystem (PDW)

E. Använd OVER-satsen med funktionen ROW_NUMBER

F. Använda OVER-satsen med aggregerade funktioner

Related content

Feedback

Ytterligare resurser