Window 関数

Window 関数を使用すると、集計関数のように行のセットに対して1つの計算結果を返すのではなく、行のグループ（Window）から計算フィールドを作成し、各行に対して1つの計算結果を返すことができます。本製品は、以下のWindow 関数構文をサポートしています。

Note：Window 関数のサポートは本製品の実験的な機能です。この機能は、SQL-92準拠という本製品のコアスコープを超えています。そのため、Window 関数のパフォーマンスは最適ではない場合があります。

Window 関数句

OVER

OVER 句は、Window 関数が実行されるWindow を定義します。

SELECT A, B, <window function> OVER (<window frame>) FROM TableName

<window function> はサポートされているWindow 関数句を指し、<window frame> はWindow を定義するロジックを指定する1つ以上の句を指します。

PARTITION BY

PARTITION BY 句は、Window をパーティションと呼ばれるサブウィンドウに細分化します。PARTITION BY 句で指定された列の一意な値ごとに、その値を持つすべてのレコードがまとめて個々のパーティションを形成します。

SELECT A, B, <window function> OVER (PARTITION BY A ORDER BY B) From Invoices

<window function> はサポートされているWindow 関数句を指します。

Window 関数

本製品は、計算、ランク付け、分析のためのWindow 関数をサポートします。

計算

このWindow 関数は、Window 内のレコードに対して数学的操作を実行します。

COUNT()

各パーティションのレコード数を計算します。計算される列のデータ型は"int" です。

各パーティションでは、すべてのレコードにそのパーティション内のレコードの合計数が表示されます。

SELECT Name, Role, Earnings, COUNT() OVER (PARTITION BY Role) FROM Employees

COUNT_BIG()

各パーティションのレコード数を計算します。計算される列のデータ型は"bigint" です。

各パーティションでは、すべてのレコードにそのパーティション内のレコードの合計数が表示されます。

SELECT Name, Role, Earnings, COUNT_BIG() OVER (PARTITION BY Role) FROM Employees

MIN(numeric_column)

パーティションごとの数値列の最小値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の最小値が表示されます。

SELECT Name, Role, Earnings, MIN(Earnings) OVER (PARTITION BY Role) FROM Employees

MAX(numeric_column)

パーティションごとの数値列の最大値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の最大値が表示されます。

SELECT Name, Role, Earnings, MAX(Earnings) OVER (PARTITION BY Role) FROM Employees

SUM(numeric_column)

パーティションごとの数値列の合計を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の合計が表示されます。

SELECT Name, Role, Earnings, SUM(Earnings) OVER (PARTITION BY Role) FROM Employees

AVG(numeric_column)

パーティションごとの数値列の平均値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の平均値が表示されます。

SELECT Name, Role, Earnings, AVG(Earnings) OVER (PARTITION BY Role) FROM Employees

MEDIAN(numeric_column)

パーティションごとの数値列の中央値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の中央値が表示されます。

SELECT Name, Role, Earnings, MEDIAN(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEV(numeric_column)

パーティションごとの数値列の標準偏差を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の標準偏差が表示されます。

SELECT Name, Role, Earnings, STDEV(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEVP(numeric_column)

パーティションごとの数値列の母標準偏差を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の母標準偏差が表示されます。

SELECT Name, Role, Earnings, STDEVP(Earnings) OVER (PARTITION BY Role) FROM Employees

VAR(numeric_column)

パーティションごとの数値列の統計的標準分散を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の統計的標準分散が表示されます。

SELECT Name, Role, Earnings, VAR(Earnings) OVER (PARTITION BY Role) FROM Employees

VARP(numeric_column)

パーティションごとの数値列の母集団分散を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の母集団分散が表示されます。

SELECT Name, Role, Earnings, VARP(Earnings) OVER (PARTITION BY Role) FROM Employees

ランク付け

これらのWindow 関数は、Window とそのパーティション内にあるレコードをランク付けします。

RANK()

必須のORDER BY 句で指定されたカラムの値に基づいて、Window 内の各レコードにランク番号を割り当てます。

2つ以上のレコードがランク付けされたカラムで同じ値を持つ場合、それらはすべて同じランク番号を受け取り、ランクカウントは内部的にインクリメントされ、ORDER BY カラムで重複する値を持つ各レコードのランク番号を1つ先にスキップします。

SELECT Id, DueDate, RANK() OVER (ORDER BY DueDate) AS Rank FROM Invoices

PARTITION BY 句を追加すると、各パーティションに対して個別のランクが計算されます。

SELECT Id, DueDate, RANK() OVER (PARTITION BY Id ORDER BY DueDate) AS Rank FROM Invoices

DENSE_RANK()

RANK() 関数のように動作しますが、ランク付けされたカラムで重複した値を持つ各レコードの内部ランクカウンタをインクリメントしません。

つまり、ORDER BY カラムで同じ値を持つレコードは同じランク番号を持ちますが、この関数がランク番号をスキップすることはありません。

SELECT Id, DueDate, DENSE_RANK() OVER (PARTITION BY Id ORDER BY DueDate) AS Rank FROM Invoices

PARTITION BY 句を追加すると、各パーティションに対して個別のランクが計算されます。

SELECT Id, DueDate, DENSE_RANK() OVER (PARTITION BY Id ORDER BY DueDate) AS Rank FROM Invoices

ROW_NUMBER()

各レコードの行番号を計算します。OVER 句の中のORDER BY 句は必須です。

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (ORDER BY Role) FROM Employees

PARTITION BY で複数のパーティションを定義すると、パーティションごとに新しい行番号のセットが計算されます。

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Earnings) FROM Employees

NTILE()

順序付けされたパーティションの行を、指定された数にほぼ等しいグループ、またはバケットに分割します。各グループに1から始まるバケット番号を割り当てます。グループ内の各行に対して、NTILE() 関数は、その行が属するグループを表すバケット番号を割り当てます。

NTILE() の構文は次のとおりです。

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

NTILE() がサポートするパラメータを以下に示します。

buckets：行を分割するバケット数。バケットには、正の整数として返される式やサブクエリを指定します。これはWindow 関数ではありません。
PARTITION BY：結果セットの行をNTILE() 関数が適用されるパーティションに分割します。
ORDER BY は、NTILE() が適用される各パーティションの行の論理的な順序を指定する句です。

行数がバケット数で割り切れない場合、NTILE() 関数は2つのサイズのグループを1つの差をつけて返します。OVER() 句のORDER BY で設定された順序に従い、大きなグループは小さなグループに常に先行します。

行の合計がバケット数で割り切れる場合、この関数は行をバケットに均等に分割します。次のステートメントは、10個の整数を格納するntile_demo という新しいテーブルを作成します。

CREATE TABLE sales.ntile_demo (
	v INT NOT NULL
);
	
INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);	
	
SELECT * FROM sales.ntile_demo;

このステートメントでは、NTILE() 関数を使用して、10行を3つのグループに分割します。

SELECT 
	v, 
	NTILE (3) OVER (
		ORDER BY v
	) buckets
FROM 
	sales.ntile_demo;

分析

このWindow 関数は、Window 内のレコードに対して分析操作を実行します。

PERCENT_RANK()

各行の相対ランクSQL パーセンタイルを計算します。0より大きな値を返しますが、最大値は1です。NULL 値はカウントしません。この関数は非決定的です。

PERCENT_RANK() の構文は次のとおりです。

PERCENT_RANK() OVER (
      [PARTITION BY partition_expression, ... ]
      ORDER BY sort_expression [ASC | DESC], ...
  )

この構文では以下のパラメータを使用します。

PARTITION BY：デフォルトでは、SQL Server はデータセット全体を1つのセットとして扱います。PARTITION BY 句を指定すると、データを複数のセットに分けることができます。Percent_Rank 関数は、各セットの分析計算を行います。このパラメータはオプションです。
ORDER BY：データを昇順または降順に並べ替えます。このパラメータは必須です。

Cmdlets for Monday

Window 関数

Window 関数句

OVER

PARTITION BY

Window 関数

計算

COUNT()

COUNT_BIG()

MIN(numeric_column)

MAX(numeric_column)

SUM(numeric_column)

AVG(numeric_column)

MEDIAN(numeric_column)

STDEV(numeric_column)

STDEVP(numeric_column)

VAR(numeric_column)

VARP(numeric_column)

ランク付け

RANK()

DENSE_RANK()

ROW_NUMBER()

NTILE()

分析

PERCENT_RANK()