次の方法で共有


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)」を参照してください。

    ms189461.note(ja-jp,SQL.90).gif重要 :
    <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 日

追加内容 :
  • 例を追加。