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 [My Contacts]
<window function> はサポートされているWindow 関数句を指します。
Window 関数
RANK()
必須のORDER BY 句で指定されたカラムの値に基づいて、Window 内の各レコードにランク番号を割り当てます。
2つ以上のレコードがランク付けされたカラムで同じ値を持つ場合、それらはすべて同じランク番号を受け取り、ランクカウントは内部的にインクリメントされ、ORDER BY カラムで重複する値を持つ各レコードのランク番号を1つ先にスキップします。
SELECT GivenName, HomeEmailAddresses, RANK() OVER (ORDER BY HomeEmailAddresses) AS Rank FROM [My Contacts]
PARTITION BY 句を追加すると、各パーティションに対して個別のランクが計算されます。
SELECT GivenName, HomeEmailAddresses, RANK() OVER (PARTITION BY GivenName ORDER BY HomeEmailAddresses) AS Rank FROM [My Contacts]
DENSE_RANK()
RANK() 関数のように動作しますが、ランク付けされたカラムで重複した値を持つ各レコードの内部ランクカウンタをインクリメントしません。
つまり、ORDER BY カラムで同じ値を持つレコードは同じランク番号を持ちますが、この関数がランク番号をスキップすることはありません。
SELECT GivenName, HomeEmailAddresses, DENSE_RANK() OVER (PARTITION BY GivenName ORDER BY HomeEmailAddresses) AS Rank FROM [My Contacts]
PARTITION BY 句を追加すると、各パーティションに対して個別のランクが計算されます。
SELECT GivenName, HomeEmailAddresses, DENSE_RANK() OVER (PARTITION BY GivenName ORDER BY HomeEmailAddresses) AS Rank FROM [My Contacts]
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;
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:データを昇順または降順に並べ替えます。このパラメータは必須です。