SELECT - OVER 子句（Transact-SQL）

發行項
10/31/2024

適用於：sql Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Platform System （PDW） SQL 分析端點在 Microsoft Fabric SQL 資料庫中Microsoft網狀架構倉儲Microsoft網狀架構

OVER子句會在套用相關聯的視窗函式之前，決定數據列集的數據分割和排序。也就是說， OVER 子句會定義查詢結果集中的視窗或使用者指定的數據列集。然後視窗函數會針對視窗中的每個資料列來計算值。您可以使用 OVER 子句搭配函式來計算匯總值，例如移動平均值、累計匯總、執行總計或每個群組結果的前 N 個。

Transact-SQL 語法慣例

Syntax

SQL Server、Azure SQL 資料庫和 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> }

平行處理資料倉儲的語法。

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

引數

視窗函數的 OVER 子句中可能有下列引數：

PARTITION BY 可將查詢結果集分成幾個資料分割。
ORDER BY 可定義結果集的每個資料分割內資料列的邏輯順序。
數據列或 RANGE ，藉由指定資料分割內的起點和終點來限制資料分割中的數據列。其需要 ORDER BY 引數，且在指定 ORDER BY 引數的情況下，其預設值為資料分割的開頭至目前的元素。

如果您未指定任何自變數，則會在整個結果集上套用視窗函式。

SELECT object_id,
       MIN(object_id) OVER () AS [min],
       MAX(object_id) OVER () AS [max]
FROM sys.objects;

object_id	分鐘	max
3	3	2139154666
5	3	2139154666
...	...	...
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

將查詢結果集分成幾個資料分割。視窗函數會分別套用至每個資料分割，並且針對每個資料分割重新開始計算。

PARTITION BY <value_expression>

如果未 PARTITION BY 指定，函式會將查詢結果集的所有數據列視為單一數據分割。

如果您沒有指定 ORDER BY 子句，函式會套用至分割區中的所有數據列。

PARTITION BY value_expression

指定分割資料列集所根據的資料行。 value_expression只能參考子句所提供的FROM數據行。 value_expression無法參考選取清單中的表達式或別名。 value_expression 可以是資料行運算式、純量子查詢、純量函數或使用者定義的變數。

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	類型	分鐘	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_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]

定義結果集的每個資料分割內資料列的邏輯順序。也就是說，其會指定執行視窗函數計算的邏輯順序。

如果未指定，則預設順序為 ASC ，而視窗函式會使用分割區中的所有數據列。
如果指定，且ROWS未指定或 RANGE ，則預設RANGE UNBOUNDED PRECEDING AND CURRENT ROW會作為視窗框架的預設值，由可接受選擇性ROWS或規格的函式使用（例如 min 或 RANGE 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	類型	分鐘	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

指定排序的資料行或運算式。 order_by_expression只能參考子句所提供的FROM數據行。無法指定整數來表示數據行名稱或別名。

COLLATE collation_name

指定ORDER BY作業應該根據collation_name中指定的定序來執行。 collation_name 可以是 Windows 定序名稱或 SQL 定序名稱。如需詳細資訊，請參閱定序和 Unicode 支援。 COLLATE僅適用於 char、varchar、nchar 和 nvarchar 類型的數據行。

ASC | DESC

指定指定之資料行的值應該以遞增或遞減順序排序。 ASC 為預設的排序順序。 Null 值會當做最低的可能值來處理。

ROWS 或 RANGE

適用於：SQL Server 2012 （11.x）和更新版本。

指定資料分割內的起始點和結束點，以進一步限制資料分割中的資料列。它會指定邏輯關聯或實體關聯，相對於目前數據列的數據列範圍。實體關聯是使用 ROWS 子句達成的。

子 ROWS 句會指定目前數據列之前或之後固定數目的數據列，以限制數據分割中的數據列。或者，子句會 RANGE 藉由指定與目前數據列中值相關的值範圍，以邏輯方式限制數據分割中的數據列。前面和下列數據列是根據子句中的 ORDER BY 排序來定義。視窗框架 RANGE ... CURRENT ROW ... 包含表達式中 ORDER BY 與目前數據列具有相同值的所有數據列。例如， ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示函式所操作的數據列視窗大小為三個數據列，從之前的 2 個數據列開始，包括目前的數據列。

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 或 RANGE 會要求您指定 ORDER BY 子句。如果 ORDER BY 包含多個順序表示式， CURRENT ROW FOR RANGE 則決定目前數據列時，會考慮清單中的所有數據 ORDER BY 行。

UNBOUNDED PRECEDING

適用於：SQL Server 2012 （11.x）和更新版本。

指定視窗從資料分割的第一個資料列開始。 UNBOUNDED PRECEDING 只能指定為視窗起點。

<不帶正負號值規格> PRECEDING

使用 <unsigned value specification> 指定，表示要位於目前數據列之前的數據列或值數目。不允許此規格。RANGE

CURRENT ROW

適用於：SQL Server 2012 （11.x）和更新版本。

指定當搭配 ROWS 使用時，視窗會以目前數據列開始或結束，或是搭配 RANGE使用時目前的值。 CURRENT ROW 可以同時指定為起點和結束點。

BETWEEN AND

適用於：SQL Server 2012 （11.x）和更新版本。

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

與或 RANGE 搭配ROWS使用，以指定視窗的下限（開始）和上限（結束）界限點。 <window frame bound> 定義界限起點，並 <window frame bound> 定義界限端點。上限不能小於下限。

UNBOUNDED FOLLOWING

適用於：SQL Server 2012 （11.x）和更新版本。

指定視窗在資料分割的最後一個資料列結束。 UNBOUNDED FOLLOWING 只能指定為視窗端點。例如， RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 定義以目前數據列開頭的視窗，並以數據分割的最後一個數據列結尾。

<不帶正負號值規格> FOLLOWING

<unsigned value specification>使用指定，表示要追蹤目前數據列的數據列或值數目。指定為視窗起點時 <unsigned value specification> FOLLOWING ，結束點必須是 <unsigned value specification> FOLLOWING。例如， ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING 定義以第二個數據列開頭的視窗，該數據列後面接著目前數據列，並以第十個數據列結尾。不允許此規格。RANGE

<不帶正負號的整數常值>

適用於：SQL Server 2012 （11.x）和更新版本。

正整數常值（包括 0），指定要在目前數據列或值之前或之後的數據列或值數目。此規格僅適用於 ROWS。

備註

單一查詢中可以使用一個以上的視窗函式搭配單 FROM 一子句。 OVER每個函式的子句在分割和排序上可能會有所不同。

如果未 PARTITION BY 指定，函式會將查詢結果集的所有數據列視為單一群組。

重要

如果 ROWS 指定或 RANGE 並 <window frame preceding> 用於 <window frame extent> （簡短語法），則此規格會用於視窗框架界限起點，並 CURRENT ROW 用於界限結束點。例如， ROWS 5 PRECEDING 等於 ROWS BETWEEN 5 PRECEDING AND CURRENT ROW。

如果未 ORDER BY 指定，則會針對視窗框架使用整個分割區。這隻適用於不需要 ORDER BY 子句的函式。如果未 ROWS 指定或 RANGE ，但 ORDER BY 已指定， RANGE UNBOUNDED PRECEDING AND CURRENT ROW 則會當做視窗框架的預設值使用。這隻適用於可接受選擇性 ROWS 或 RANGE 規格的函式。例如，排名函式無法接受 ROWS 或 RANGE，因此即使 ORDER BY 存在 ROWS 或 RANGE 不存在，也不會套用此視窗框架。

限制

OVER子句無法與匯總搭配DISTINCT使用。

RANGE 無法與 <unsigned value specification> PRECEDING 或 <unsigned value specification> FOLLOWING 搭配使用。

視子句所使用的 OVER 排名、匯總或分析函式而定， <ORDER BY clause> 以及/或 <ROWS and RANGE clause> 可能不受支援。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 搭配ROW_NUMBER函式使用 OVER 子句

下列範例示範搭配函ROW_NUMBER式使用 OVER 子句來顯示數據分割內每個數據列的數據列編號。 ORDER BY 子句中指定的 OVER 子句會依資料行 SalesYTD 排列每個分割區的資料列。 ORDER BY語句中的 SELECT 子句會決定傳回整個查詢結果集的順序。

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

結果集如下所示。

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. 搭配聚合函數使用 OVER 子句

下列範例會針對查詢傳回的所有資料列來搭配彙總函式使用 OVER 子句。在這個範例中，使用 OVER 子句比使用子查詢來衍生彙總值更有效率。

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

結果集如下所示。

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

下列範例顯示在計算值中搭配彙總函式來使用 OVER 子句。

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

結果集如下所示。匯總的計算方式 SalesOrderID 為，而且 Percent by ProductID 會針對每個 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. 產生移動平均和累計總計

下列範例會使用 AVG 和 SUM 函式搭配 OVER 子句，為數據表中的每個 Sales.SalesPerson 地區提供移動平均和累計年銷售額總計。 TerritoryID 負責分割資料，而 SalesYTD 會進行邏輯性地排序。這表示函 AVG 式會根據銷售年度計算每個地區。在 TerritoryID 1 中，銷售年度 2005 有兩個數據列，代表當年銷售的兩個銷售人員。計算這兩個數據列的平均銷售額，然後計算中會包含代表年度 2006 銷售額的第三個數據列。

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;

結果集如下所示。

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

在這裡範例中，子 OVER 句不包含 PARTITION BY。這表示函式會套用至查詢傳回的所有數據列。 ORDER BY子句中指定的 OVER 子句會決定套用函式的AVG邏輯順序。查詢會針對子句中指定的 WHERE 所有銷售領域，傳回依年份的移動平均銷售額。 ORDER BY語句中指定的 SELECT 子句會決定查詢數據列的顯示順序。

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;

結果集如下所示。

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. 指定 ROWS 子句

適用於：SQL Server 2012 （11.x）和更新版本。

下列範例會 ROWS 使用子句來定義一個視窗，其中數據列會計算為目前數據列，以及 後續的 N 個數據列數目（在此範例中為一個數據列）。

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;

結果集如下所示。

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

在下列範例中， ROWS 子句是以指定 UNBOUNDED PRECEDING。結果是視窗從資料分割的第一個資料列開始。

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;

結果集如下所示。

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

範例：Analytics Platform System (PDW)

E. 搭配ROW_NUMBER函式使用 OVER 子句

下列範例會根據銷售代表被指派的銷售配額，傳回銷售代表的 ROW_NUMBER。

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;

以下為部分結果集。

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. 搭配聚合函數使用 OVER 子句

下列範例示範搭配聚合函數使用 OVER 子句。在此範例中 OVER ，使用子句比使用子查詢更有效率。

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;

結果集如下所示。

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

下列範例顯示在計算值中搭配彙總函式來使用 OVER 子句。匯總的計算方式 SalesOrderNumber 為，而且會針對每個 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;

此結果集的第一個開頭如下所示：

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

共用方式為

SELECT - OVER 子句（Transact-SQL）

Syntax

引數

PARTITION BY

PARTITION BY value_expression

排序依據

order_by_expression

COLLATE collation_name

ASC | DESC

ROWS 或 RANGE

UNBOUNDED PRECEDING

<不帶正負號值規格> PRECEDING

CURRENT ROW

BETWEEN AND

UNBOUNDED FOLLOWING

<不帶正負號值規格> FOLLOWING

<不帶正負號的整數常值>

備註

限制

範例

A. 搭配ROW_NUMBER函式使用 OVER 子句

B. 搭配聚合函數使用 OVER 子句

C. 產生移動平均和累計總計

D. 指定 ROWS 子句

範例：Analytics Platform System (PDW)

E. 搭配ROW_NUMBER函式使用 OVER 子句

F. 搭配聚合函數使用 OVER 子句

意見反應

其他資源

共用方式為

SELECT - OVER 子句 （Transact-SQL）

Syntax

引數

PARTITION BY

PARTITION BY value_expression

排序依據

order_by_expression

COLLATE collation_name

ASC | DESC

ROWS 或 RANGE

UNBOUNDED PRECEDING

<不帶正負號值規格> PRECEDING

CURRENT ROW

BETWEEN AND

UNBOUNDED FOLLOWING

<不帶正負號值規格> FOLLOWING

<不帶正負號的整數常值>

備註

限制

範例

A. 搭配ROW_NUMBER函式使用 OVER 子句

B. 搭配聚合函數使用 OVER 子句

C. 產生移動平均和累計總計

D. 指定 ROWS 子句

範例：Analytics Platform System (PDW)

E. 搭配ROW_NUMBER函式使用 OVER 子句

F. 搭配聚合函數使用 OVER 子句

相關內容

意見反應

其他資源

SELECT - OVER 子句（Transact-SQL）