SELECT - OVER 句 (Transact-SQL)
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric Warehouse Microsoft Fabric SQL Database
OVER句は、関連付けられたウィンドウ関数が適用される前に行セットのパーティション分割と順序を決定します。 つまり、 OVER 句は、クエリ結果セット内のウィンドウまたはユーザー指定の行セットを定義します。 その後、ウィンドウ関数はウィンドウ内の各行の値を計算します。 OVER句と関数を使用すると、移動平均、累積集計、実行中の合計、グループの結果ごとの上位Nなどの集計値を計算できます。
構文
SQL Server、Azure SQL Database、および 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> }
Parallel Data Warehouse の構文。
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
引数
ウィンドウ関数の OVER 句には、次の関数が含まれる場合があります。
PARTITION BY。クエリ結果セットをパーティションに分割します。
ORDER BY。結果セットの各パーティション内の行の論理的な順序を定義します。
ROWS または 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が指定されていない場合、関数はクエリ結果セットのすべての行を 1 つのパーティションとして扱います。
この関数は、 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 | type | 分 | 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 ]
結果セットの各パーティション内の行の論理的な順序を定義します。 つまり、ウィンドウ関数の計算が実行される論理的な順序を指定します。
指定しない場合、既定の順序は
ASCされ、ウィンドウ関数はパーティション内のすべての行を使用します。ROWSまたはRANGEが指定されていない場合、省略可能なROWSまたはRANGEの指定 (minやmaxなど) を受け取ることができる関数によって、既定のRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWがウィンドウ フレームの既定値として使用されます。
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 | 分 | 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
collation_nameで指定した照合順序に従って、ORDER BY操作を実行するように指定します。 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 は、関数が操作する行のウィンドウのサイズが 3 行であり、現在の行の前に 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 は、ウィンドウの開始点としてのみ指定できます。
<unsigned value specification> 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>
ROWSまたはRANGEと共に使用して、ウィンドウの下限 (開始) と上 (終了) の境界ポイントを指定します。 <window frame bound> は境界の開始点を定義し、 <window frame bound> は境界エンドポイントを定義します。 上限を下限より小さくすることはできません。
UNBOUNDED FOLLOWING
適用対象: SQL Server 2012 (11.x) 以降のバージョン。
ウィンドウがパーティションの最後の行で終了することを指定します。 UNBOUNDED FOLLOWING は、ウィンドウ エンドポイントとしてのみ指定できます。 たとえば、 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING は、現在の行で始まり、パーティションの最後の行で終わるウィンドウを定義します。
<unsigned value specification> FOLLOWING
<unsigned value specification> は、現在行より後にある行または値の数を示します。 ウィンドウの始点として <unsigned value specification> FOLLOWING を指定する場合は、終了ポイントを <unsigned value specification> FOLLOWINGする必要があります。 たとえば、 ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING は、現在の行の後の 2 番目の行で始まり、現在の行の後の 10 行で終わるウィンドウを定義します。 この仕様は、 RANGEでは使用できません。
<符号なし整数リテラル>
適用対象: SQL Server 2012 (11.x) 以降のバージョン。
現在の行または値の前または後の行または値の数を指定する正の整数リテラル ( 0を含む)。 この仕様は、 ROWSに対してのみ有効です。
解説
1 つの FROM 句を含む 1 つのクエリで複数のウィンドウ関数を使用できます。 各関数の OVER 句は、パーティション分割と順序付けが異なる場合があります。
PARTITION BY指定されていない場合、関数はクエリ結果セットのすべての行を 1 つのグループとして扱います。
重要
ROWSまたはRANGEを指定し、<window frame extent> (短い構文) に<window frame preceding>を使用する場合、この仕様はウィンドウ フレーム境界の開始点に使用され、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 サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。
A. ROW_NUMBER関数で OVER 句を使用する
次の例は、 OVER 句と ROW_NUMBER 関数を使用して、パーティション内の各行の行番号を表示する方法を示しています。 ORDER BY 句に指定した OVER 句によって、列 SalesYTD を基準に各パーティション内の行の順序付けが行われます。 SELECT ステートメントのORDER BY句によって、クエリ結果セット全体が返される順序が決まります。
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: 移動平均と累積合計を生成する
次の例では、OVER 句でAVG関数とSUM関数を使用して、Sales.SalesPerson テーブル内の各地域の年間売上の移動平均と累積合計を提供します。 データは TerritoryID によってパーティションに分割され、SalesYTD によって論理的に順序付けされます。 つまり、 AVG 関数は、販売年に基づいて各担当地域に対して計算されます。 TerritoryID 1 の場合、売上年には、その年の売上を持つ 2 人の販売担当者を表す 2 つの行2005があります。 これら 2 つの行の平均売上が計算され、 2006 年の売上を表す 3 番目の行が計算に含まれます。
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は含まれません。 これは、クエリによって返されるすべての行に関数が適用されることを意味します。 OVER句で指定されたORDER BY句によって、AVG関数が適用される論理順序が決まります。 このクエリは、 WHERE 句で指定されたすべての販売地域の年別の売上の移動平均を返します。 SELECT ステートメントで指定されたORDER BY句によって、クエリの行の表示順序が決まります。
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句を使用して、行を現在の行として計算するウィンドウと、その後に続く行数 (この例では 1 行 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