OVER 句 (Transact-SQL)

更新 : 2006 年 7 月 17 日

関連するウィンドウ関数が適用される前に、行セットのパーティション処理と並べ替えを決定します。

適用対象

順位付け関数。

集計関数。詳細については、「集計関数 (Transact-SQL)」を参照してください。

Transact-SQL 構文表記規則

構文

 Ranking Window Functions  <OVER_CLAUSE> :: =     OVER ( [ PARTITION BY value_expression , ... [ n ] ]            <ORDER BY_Clause> )  Aggregate Window Functions  <OVER_CLAUSE> :: =      OVER ( [ PARTITION BY value_expression , ... [ n ] ] ) 

引数

• PARTITION BY
  結果セットをパーティションに分割します。ウィンドウ関数は各パーティションに対して個別に適用され、各パーティションで計算が再開されます。

• value_expression
  対応する FROM 句によって作成された行セットのパーティション分割するのに使用する列を指定します。<<value_expression> で参照できるのは、FROM 句で取得した列だけです。選択リストの式または別名は参照できません。<value_expression> には、列の式、スカラ サブクエリ、スカラ関数、またはユーザー定義変数を指定できます。

• <ORDER BY 句>
  順位付け関数を適用する順序を指定します。詳細については、「ORDER BY 句 (Transact-SQL)」を参照してください。

  重要 :
  <ORDER BY 句> を順位付け関数のコンテキストで使用する場合、この句で参照できるのは FROM 句で取得した列だけです。整数値を指定して、選択リスト内の列の名前または別名の位置を表すことはできません。<ORDER BY 句>を集計関数と共に使用することはできません。

解説

ウィンドウ関数は ISO SQL 標準で定義されており、SQL Server では順位付け関数と集計関数を提供しています。ウィンドウとは、ユーザーが指定した行セットです。ウィンドウ関数は、ウィンドウから生成された結果セットの各行に対し値を計算します。

1 つの FROM 句を使用した 1 つのクエリで、複数の順位付け関数または集計関数を使用することができますが、パーティション分割や並べ替えでは、各関数の OVER 句は異なる場合があります。OVER 句を CHECKSUM 集計関数と共に使用することはできません。

例

A. OVER 句を ROW_NUMBER 関数と共に使用する

順位付け関数 ROW_NUMBER、DENSE_RANK、RANK、NTILE では OVER 句が使用されます。次の例では、OVER 句を ROW_NUMBER と共に使用します。

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;
GO

B. OVER 句を集計関数と共に使用する

次の例では、OVER 句を集計関数と共に使用します。この例では、OVER 句を使用した方が、サブクエリを使用するより効率的です。

USE AdventureWorks;
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 AdventureWorks;
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.43
43664	773	1	14	7.14
43664	778	1	14	7.14

参照

関連項目

順位付け関数 (Transact-SQL)
集計関数 (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2006 年 7 月 17 日	追加内容 : 例を追加。