SELECT - OVER 句 (Transact-SQL)SELECT - OVER Clause (Transact-SQL)
適用対象:Applies to: 
SQL ServerSQL Server (サポートされているすべてのバージョン) SQL ServerSQL Server (all supported versions) Azure SQL データベースAzure SQL DatabaseAzure SQL データベースAzure SQL DatabaseAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsParallel Data WarehouseParallel Data WarehouseParallel Data WarehouseParallel Data WarehouseSQL ServerSQL Server (サポートされているすべてのバージョン) SQL ServerSQL Server (all supported versions) Azure SQL データベースAzure SQL DatabaseAzure SQL データベースAzure SQL DatabaseAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure SQL Managed InstanceAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsParallel Data WarehouseParallel Data WarehouseParallel Data WarehouseParallel Data Warehouse
関連するウィンドウ関数が適用される前に、行セットのパーティション処理と並べ替えを決定します。Determines the partitioning and ordering of a rowset before the associated window function is applied. つまり、OVER 句はクエリ結果セット内のウィンドウまたはユーザー指定の行セットを定義します。That is, the OVER clause defines a window or user-specified set of rows within a query result set. その後、ウィンドウ関数はウィンドウ内の各行の値を計算します。A window function then computes a value for each row in the window. 関数で OVER 句を使用すると、移動平均、累積集計、集計途中経過、グループ結果ごとの上位 N などの集計値を計算できます。You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.
Transact-SQL 構文表記規則 Transact-SQL Syntax Conventions
構文Syntax
-- Syntax for SQL Server, Azure SQL Database, and 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 for Parallel Data Warehouse
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
注意
SQL Server 2014 以前の Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
引数Arguments
ウィンドウ関数の OVER 句には、次の関数が含まれる場合があります。Window functions might have the following arguments in their OVER clause:
- PARTITION BY。クエリ結果セットをパーティションに分割します。PARTITION BY that divides the query result set into partitions.
- ORDER BY。結果セットの各パーティション内の行の論理的な順序を定義します。ORDER BY that defines the logical order of the rows within each partition of the result set.
- ROWS/RANGE。パーティション内の開始点と終了点を指定することで、パーティション内の行をさらに制限します。ROWS/RANGE that limits the rows within the partition by specifying start and end points within the partition. これには
ORDER BY引数が必要です。また、ORDER BY引数が指定されている場合、既定値はパーティションの先頭から現在の要素までです。It requiresORDER BYargument and the default value is from the start of partition to the current element if theORDER BYargument is specified.
引数を指定しない場合、そのウィンドウ関数は結果セット全体に適用されます。If you don't specify any argument, the window functions will be applied on the entire result set.
select
object_id
, [min] = min(object_id) over()
, [max] = max(object_id) over()
from sys.objects
| object_idobject_id | 分min | maxmax |
|---|---|---|
| 33 | 33 | 21391546662139154666 |
| 55 | 33 | 21391546662139154666 |
| ...... | ...... | ...... |
| 21231546092123154609 | 33 | 21391546662139154666 |
| 21391546662139154666 | 33 | 21391546662139154666 |
PARTITION BYPARTITION BY
クエリ結果セットをパーティションに分割します。Divides the query result set into partitions. ウィンドウ関数は各パーティションに対して個別に適用され、各パーティションで計算が再開されます。The window function is applied to each partition separately and computation restarts for each partition.
PARTITION BY *value_expression*
PARTITION BY を指定しない場合、関数ではクエリ結果セットのすべての行を 1 つのパーティションとして扱います。If PARTITION BY is not specified, the function treats all rows of the query result set as a single partition.
ORDER BY 句を指定しない場合、関数はそのパーティション内のすべての行に適用されます。Function will be applied on all rows in the partition if you don't specify ORDER BY clause.
PARTITION BY value_expressionPARTITION BY value_expression
行セットをパーティションに分割するときに使用する列を指定します。Specifies the column by which the rowset is partitioned. value_expression で参照できるのは FROM 句で取得した列だけです。value_expression can only refer to columns made available by the FROM clause. value_expression は、選択リストの式または別名を参照できません。value_expression cannot refer to expressions or aliases in the select list. value_expression には、列式、スカラー サブクエリ、スカラー関数、またはユーザー定義変数を指定できます。value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.
select
object_id, type
, [min] = min(object_id) over(partition by type)
, [max] = max(object_id) over(partition by type)
from sys.objects
| object_idobject_id | typetype | 分min | maxmax |
|---|---|---|---|
| 6819529368195293 | PKPK | 6819529368195293 | 711673583711673583 |
| 631673298631673298 | PKPK | 6819529368195293 | 711673583711673583 |
| 711673583711673583 | PKPK | 6819529368195293 | 711673583711673583 |
| ...... | ...... | ...... | |
| 33 | SS | 33 | 9898 |
| 55 | SS | 33 | 9898 |
| ...... | ...... | ...... | |
| 9898 | SS | 33 | 9898 |
| ...... | ...... | ...... |
ORDER BYORDER BY
ORDER BY *order_by_expression* [COLLATE *collation_name*] [ASC|DESC]
結果セットの各パーティション内の行の論理的な順序を定義します。Defines the logical order of the rows within each partition of the result set. つまり、ウィンドウ関数の計算が実行される論理的な順序を指定します。That is, it specifies the logical order in which the window function calculation is performed.
- これが指定されていない場合、既定の順序は
ASCであり、ウィンドウ関数ではパーティション内のすべての行を使用します。If it is not specified, the default order isASCand window function will use all rows in partition. - これが指定されており、かつ ROWS/RANGE が指定されていない場合、省略可能な ROWS/RANGE の指定を受け入れることができる関数 (
minやmaxなど) によって、既定のRANGE UNBOUNDED PRECEDING AND CURRENT ROWがウィンドウ フレームの既定値として使用されます。If it is specified, and a ROWS/RANGE is not specified, then defaultRANGE UNBOUNDED PRECEDING AND CURRENT ROWis used as default for window frame by the functions that can accept optional ROWS/RANGE specification (for exampleminormax).
select
object_id, type
, [min] = min(object_id) over(partition by type order by object_id)
, [max] = max(object_id) over(partition by type order by object_id)
from sys.objects
| object_idobject_id | typetype | 分min | maxmax |
|---|---|---|---|
| 6819529368195293 | PKPK | 6819529368195293 | 6819529368195293 |
| 631673298631673298 | PKPK | 6819529368195293 | 631673298631673298 |
| 711673583711673583 | PKPK | 6819529368195293 | 711673583711673583 |
| ...... | ...... | ...... | |
| 33 | SS | 33 | 33 |
| 55 | SS | 33 | 55 |
| 66 | SS | 33 | 66 |
| ...... | ...... | ...... | |
| 9797 | SS | 33 | 9797 |
| 9898 | SS | 33 | 9898 |
| ...... | ...... | ...... |
order_by_expressionorder_by_expression
並べ替えに使用する列または式を指定します。Specifies a column or expression on which to sort. order_by_expression で参照できるのは FROM 句で取得した列だけです。order_by_expression can only refer to columns made available by the FROM clause. 整数を指定して列の名前または別名を表すことはできません。An integer cannot be specified to represent a column name or alias.
COLLATE collation_nameCOLLATE collation_name
collation_name で指定した照合順序に従って ORDER BY 操作を実行することを指定します。Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. collation_name には、Windows 照合順序名または SQL 照合順序名を指定できます。collation_name can be either a Windows collation name or a SQL collation name. 詳細については、「 Collation and Unicode Support」を参照してください。For more information, see Collation and Unicode Support. COLLATE は、char、varchar、nchar、nvarchar 型の列にのみ適用できます。COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.
ASC | DESCASC | DESC
指定した列の値を昇順と降順のどちらで並べ替えるかを指定します。Specifies that the values in the specified column should be sorted in ascending or descending order. ASC が既定の並べ替え順序です。ASC is the default sort order. NULL 値は最小値として扱われます。Null values are treated as the lowest possible values.
ROWS または RANGEROWS or RANGE
適用対象: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.
パーティション内の開始点と終了点を指定することで、パーティション内の行をさらに制限します。Further limits the rows within the partition by specifying start and end points within the partition. これは、論理アソシエーションまたは物理アソシエーションによって現在の行を基準に行の範囲を指定することで行います。This is done by specifying a range of rows with respect to the current row either by logical association or physical association. 物理アソシエーションは ROWS 句を使用することで実現されます。Physical association is achieved by using the ROWS clause.
ROWS 句では、現在行の前または後にある固定数の行を指定することにより、パーティション内の行が限定されます。The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. または、RANGE 句は、現在行の値を基準とする値の範囲を指定することにより、パーティション内の行を論理的に限定します。Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. 前後の行は、ORDER BY 句での順序に基づいて定義されます。Preceding and following rows are defined based on the ordering in the ORDER BY clause. ウィンドウ フレーム "RANGE ...CURRENT ROW ..." には、ORDER BY 式に現在行と同じ値を持つすべての行が含まれます。The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. たとえば、ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は、関数の操作対象である行のウィンドウが、現在行の 2 行前の行から現在行までの 3 行 (現在行を含みます) であることを意味します。For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.
select
object_id
, [preceding] = count(*) over(order by object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
, [central] = count(*) over(order by object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING )
, [following] = count(*) over(order by object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
from sys.objects
order by object_id asc
| object_idobject_id | precedingpreceding | centralcentral | followingfollowing |
|---|---|---|---|
| 33 | 11 | 33 | 156156 |
| 55 | 22 | 44 | 155155 |
| 66 | 33 | 55 | 154154 |
| 77 | 44 | 55 | 153153 |
| 88 | 55 | 55 | 152152 |
| ...... | ...... | ...... | ...... |
| 21127265792112726579 | 153153 | 55 | 44 |
| 21196785992119678599 | 154154 | 55 | 33 |
| 21231546092123154609 | 155155 | 44 | 22 |
| 21391546662139154666 | 156156 | 33 | 11 |
注意
ROWS または RANGE を使用する場合は、ORDER BY 句を指定する必要があります。ROWS or RANGE requires that the ORDER BY clause be specified. ORDER BY に複数の順序式が含まれる場合、ROW FOR RANGE では、現在行を決定するときに ORDER BY リスト内のすべての列が考慮されます。If ORDER BY contains multiple order expressions, CURRENT ROW FOR RANGE considers all columns in the ORDER BY list when determining the current row.
UNBOUNDED PRECEDINGUNBOUNDED PRECEDING
適用対象: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.
ウィンドウがパーティションの最初の行から開始することを指定します。Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING はウィンドウの開始位置としてのみ指定できます。UNBOUNDED PRECEDING can only be specified as window starting point.
<unsigned value specification> PRECEDING<unsigned value specification> PRECEDING
<unsigned value specification> は、現在行より前にある行または値の数を示します。Specified with <unsigned value specification>to indicate the number of rows or values to precede the current row. RANGE に対してはこの指定を使用できません。This specification is not allowed for RANGE.
CURRENT ROWCURRENT ROW
適用対象: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.
ウィンドウが現在の行 (ROWS の場合) または現在の値 (RANGE の場合) で開始または終了することを指定します。Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW は開始位置としても終了位置としても指定できます。CURRENT ROW can be specified as both a starting and ending point.
BETWEEN ANDBETWEEN AND
適用対象: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.
BETWEEN <window frame bound > AND <window frame bound >
ROWS または RANGE で使用し、ウィンドウの下限 (開始) と上限 (終了) の境界位置を指定します。Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. 前の <window frame bound> は境界の開始位置を定義し、後の <window frame bound> は境界の終了位置を定義します。<window frame bound> defines the boundary starting point and <window frame bound> defines the boundary end point. 上限を下限より小さくすることはできません。The upper bound cannot be smaller than the lower bound.
UNBOUNDED FOLLOWINGUNBOUNDED FOLLOWING
適用対象: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.
ウィンドウがパーティションの最後の行で終了することを指定します。Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING はウィンドウの終了位置としてのみ指定できます。UNBOUNDED FOLLOWING can only be specified as a window end point. たとえば、RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING では、現在行から開始してパーティションの最後の行で終了するウィンドウが定義されます。For example RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING defines a window that starts with the current row and ends with the last row of the partition.
<unsigned value specification> FOLLOWING<unsigned value specification> FOLLOWING
<unsigned value specification> は、現在行より後にある行または値の数を示します。Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. <unsigned value specification> FOLLOWING をウィンドウの開始位置として指定するときは、終了位置を <unsigned value specification> FOLLOWING にする必要があります。When <unsigned value specification> FOLLOWING is specified as the window starting point, the ending point must be <unsigned value specification>FOLLOWING. たとえば、ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING では、現在行より後の 2 行目から開始して現在行より後の 10 行目で終了するウィンドウが定義されます。For example, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING defines a window that starts with the second row that follows the current row and ends with the tenth row that follows the current row. RANGE に対してはこの指定を使用できません。This specification is not allowed for RANGE.
unsigned integer literalunsigned integer literal
適用対象: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.
現在の行または値より前または後にある行または値の数を指定する正の整数リテラル (0 を含む) です。Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. この指定は ROWS に対してのみ有効です。This specification is valid only for ROWS.
全般的な解説General Remarks
1 つの FROM 句を使用した 1 つのクエリで、複数のウィンドウ関数を使用できます。More than one window function can be used in a single query with a single FROM clause. 各関数の OVER 句は、パーティション分割や並べ替えが異なっていてもかまいません。The OVER clause for each function can differ in partitioning and ordering.
PARTITION BY を指定しない場合、関数ではクエリ結果セットのすべての行が 1 つのグループとして扱われます。If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.
重要:Important!
ROWS/RANGE を指定し、<window frame preceding> を <window frame extent> に対して使用すると (短い構文)、ウィンドウ フレーム境界の開始位置に対してはこの指定が使用され、境界の終了位置に対しては CURRENT ROW が使用されます。If ROWS/RANGE is specified and <window frame preceding> is used for <window frame extent> (short syntax) then this specification is used for the window frame boundary starting point and CURRENT ROW is used for the boundary ending point. たとえば、"ROWS 5 PRECEDING" は "ROWS BETWEEN 5 PRECEDING AND CURRENT ROW" と同じです。For example "ROWS 5 PRECEDING" is equal to "ROWS BETWEEN 5 PRECEDING AND CURRENT ROW".
注意
ORDER BY を指定しないと、パーティション全体がウィンドウ フレームに対して使用されます。If ORDER BY is not specified entire partition is used for a window frame. これは、ORDER BY 句を必要としない関数にのみ適用されます。This applies only to functions that do not require ORDER BY clause. ROWS/RANGE を指定しないで ORDER BY を指定すると、RANGE UNBOUNDED PRECEDING AND CURRENT ROW がウィンドウ フレームに対する既定値として使用されます。If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. これは、オプションの ROWS/RANGE 指定を受け入れることができる関数に対してのみ適用されます。This applies only to functions that have can accept optional ROWS/RANGE specification. たとえば、順位付け関数は ROWS/RANGE を受け入れないため、ORDER BY があり、ROWS/RANGE がなくても、このウィンドウ フレームは適用されません。For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.
制限事項と制約事項Limitations and Restrictions
OVER 句を CHECKSUM 集計関数と共に使用することはできません。The OVER clause cannot be used with the CHECKSUM aggregate function.
RANGE を <unsigned value specification> PRECEDING または <unsigned value specification> FOLLOWING と共に使用することはできません。RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.
OVER 句と共に使用される順位付け関数、集計関数、または分析関数によっては、<ORDER BY clause> と <ROWS and RANGE clause> のどちらか一方または両方がサポートされない場合があります。Depending on the ranking, aggregate, or analytic function used with the OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not be supported.
例Examples
A.A. OVER 句を ROW_NUMBER 関数と共に使用するUsing the OVER clause with the ROW_NUMBER function
次の例では、OVER 句と ROW_NUMBER 関数を使用して、パーティション内の各行の行番号を表示する方法を示します。The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. OVER 句に指定した ORDER BY 句によって、列 SalesYTD を基準に各パーティション内の行の順序付けが行われます。The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. SELECT ステートメントの ORDER BY 句によって、クエリ結果セット全体が返される順序が決まります。The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned.
USE AdventureWorks2012;
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
結果セットは次のようになります。Here is the result set.
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.B. OVER 句を集計関数と共に使用するUsing the OVER clause with aggregate functions
次の例では、クエリによって返されるすべての行に対して、OVER 句と集計関数を使用しています。The following example uses the OVER clause with aggregate functions over all rows returned by the query. この例では、OVER 句を使用した方が、サブクエリを使用して集計値を取得するより効率的です。In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.
USE AdventureWorks2012;
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
結果セットは次のようになります。Here is the result set.
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 句を集計関数と共に計算値の中で使用します。The following example shows using the OVER clause with an aggregate function in a calculated value.
USE AdventureWorks2012;
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
結果セットは次のようになります。Here is the result set. 集計は SalesOrderID ごとに計算され、Percent by ProductID は各 SalesOrderID の各行に対して計算されることに注意してください。Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each 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
(20 row(s) affected)
C.C. 移動平均および累積合計を生成するProducing a moving average and cumulative total
次の例では、OVER 句を指定した AVG および SUM 関数を使用して、Sales.SalesPerson テーブルに各区域の年間売り上げの移動平均と累積合計を入力します。The following example uses the AVG and SUM functions with the OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the Sales.SalesPerson table. データは TerritoryID によってパーティションに分割され、SalesYTD によって論理的に順序付けされます。The data is partitioned by TerritoryID and logically ordered by SalesYTD. つまり、AVG 関数は年を基にして区域ごとに計算されます。This means that the AVG function is computed for each territory based on the sales year. TerritoryID 1 の 2005 年については、その年の 2 人の営業担当者を表す 2 行があります。Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. これら 2 行の平均売上が計算された後、2006 年の売上を表す 3 番目の行が計算に組み込まれます。The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.
USE AdventureWorks2012;
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;
結果セットは次のようになります。Here is the result set.
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
(10 row(s) affected)
この例では、OVER 句に PARTITION BY が含まれません。In this example, the OVER clause does not include PARTITION BY. これは、関数がクエリによって返されるすべての行に適用されることを意味します。This means that the function will be applied to all rows returned by the query. OVER 句で指定されている ORDER BY 句によって、AVG 関数が適用される論理的な順序が決まります。The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. このクエリは、WHERE 句で指定されているすべての販売区域について、年ごとの売上の移動平均を返します。The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. SELECT ステートメントで指定されている ORDER BY 句により、クエリの行が表示される順序が決まります。The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.
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;
結果セットは次のようになります。Here is the result set.
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
(10 row(s) affected)
D.D. ROWS 句を指定するSpecifying the ROWS clause
適用対象: SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降。Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.
次の例では、ROWS 句を使用して、行の計算対象のウィンドウを、現在行からその後の N 行まで (この例では 1 行) と定義しています。The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).
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;
結果セットは次のようになります。Here is the result set.
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 を指定しています。In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. その結果、ウィンドウはパーティションの最初の行から開始します。The result is that the window starts at the first row of the partition.
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;
結果セットは次のようになります。Here is the result set.
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
例: Parallel Data WarehouseParallel Data WarehouseExamples: Parallel Data WarehouseParallel Data Warehouse
E.E. OVER 句を ROW_NUMBER 関数と共に使用するUsing the OVER clause with the ROW_NUMBER function
次の例は、割り当てられている販売ノルマに基づいて営業担当者の ROW_NUMBER を返します。The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
-- Uses AdventureWorks
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;
次に結果セットの一部を示します。Here is a partial result set.
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.F. OVER 句を集計関数と共に使用するUsing the OVER clause with aggregate functions
次の例では、OVER 句を集計関数と共に使用します。The following examples show using the OVER clause with aggregate functions. この例では、OVER 句を使用した方が、サブクエリを使用するより効率的です。In this example, using the OVER clause is more efficient than using subqueries.
-- Uses AdventureWorks
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;
結果セットは次のようになります。Here is the result set.
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 句を集計関数と共に計算値の中で使用します。The following example shows using the OVER clause with an aggregate function in a calculated value. 集計は SalesOrderNumber ごとに計算され、受注合計の割合は各 SalesOrderNumber の各行に対して計算されることに注意してください。Notice that the aggregates are calculated by SalesOrderNumber and the percentage of the total sales order is calculated for each line of each SalesOrderNumber.
-- Uses AdventureWorks
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;
この結果セットの最初の開始は次のようになります。The first start of this result set is:
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
参照See Also
集計関数 (Transact-SQL) Aggregate Functions (Transact-SQL)
分析関数 (Transact-SQL) Analytic Functions (Transact-SQL)
Itzik Ben-Gan によるsqlmag.com上のウィンドウ関数と OVERに関する優れたブログ投稿Excellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan