Window - CData

ウィンドウ関数のサポートは、ドライバーの試験的な機能です。この機能は、SQL-92 準拠というドライバーのコア範囲を超えるものです。そのため、ウィンドウ関数のパフォーマンスは最適ではない場合があります。

ウィンドウ関数の句

OVER

OVER 句は、ウィンドウ関数が実行されるウィンドウを定義します。

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

ここで指定するのは、サポートされている任意のウィンドウ関数句と、ウィンドウを定義するロジックを指定する 1 つ以上の句です。

PARTITION BY

PARTITION BY 句は、ウィンドウをパーティションと呼ばれるサブウィンドウに細分化します。PARTITION BY 句で指定されたカラムの各一意の値ごとに、その値を持つすべてのレコードがまとめて 1 つのパーティションを形成します。 SELECT A, B, OVER (PARTITION BY A ORDER BY B) From Lead ここで指定するのはサポートされている任意のウィンドウ関数句です。

算術関数

これらのウィンドウ関数は、ウィンドウ内のレコードに対して数学的演算を実行します。

COUNT

各パーティション内のレコード数を計算します。計算されたカラムのデータ型は「int」です。構文

COUNT()

例各パーティション内で、すべてのレコードはそのパーティション内のレコードの総数を表示します。

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

COUNT_BIG

各パーティション内のレコード数を計算します。計算されたカラムのデータ型は「bigint」です。構文

COUNT_BIG()

例各パーティション内で、すべてのレコードはそのパーティション内のレコードの総数を表示します。

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

MIN

パーティションごとの数値カラムの最小値を計算します。構文

MIN(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の最小値を表示します。

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

MAX

パーティションごとの数値カラムの最大値を計算します。構文

MAX(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の最大値を表示します。

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

SUM

パーティションごとの数値カラムの合計を計算します。構文

SUM(numeric_column)

Exaqmple(s) 各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の合計を表示します。

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

AVG

パーティションごとの数値カラムの平均値を計算します。構文

AVG(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の平均値を表示します。

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

MEDIAN

パーティションごとの数値カラムの中央値を計算します。構文

MEDIAN(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の中央値を表示します。

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

STDEV

パーティションごとの数値カラムの標準偏差を計算します。構文

STDEV(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の標準偏差を表示します。

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

STDEVP

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

STDEVP(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の母標準偏差を表示します。

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

VAR

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

VAR(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の統計的標準分散を表示します。

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

VARP

パーティションごとの数値カラムの母分散を計算します。構文

VARP(numeric_column)

例各パーティション内で、すべてのレコードはそのパーティション内のレコードにおける numeric_column の母分散を表示します。

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

ランキング関数

これらのウィンドウ関数は、ウィンドウおよびそのパーティション内に含まれるレコードをランク付けします。

RANK

必須の ORDER BY 句で指定されたカラムの値に基づいて、ウィンドウ内の各レコードにランク番号を割り当てます。ランク付け対象のカラムで 2 つ以上のレコードが同じ値を持つ場合、それらはすべて同じランク番号を受け取り、ランクカウンターは内部的に増加して、ORDER BY 句のカラムで重複する値を持つレコードごとにランク番号が 1 つずつスキップされます。構文

RANK()

例

SELECT Id, FullName, RANK() OVER (ORDER BY FullName) AS Rank FROM Lead

PARTITION BY 句を追加すると、パーティションごとに別々のランクのセットが計算されます。

SELECT Id, FullName, RANK() OVER (PARTITION BY Id ORDER BY FullName) AS Rank FROM Lead

DENSE_RANK

RANK() 関数と同様に動作しますが、ランク付け対象のカラムで重複する値を持つレコードごとに内部のランクカウンターを増加させません。つまり、ORDER BY 句のカラムで同一の値を持つレコードは同じランク番号を共有しますが、この関数はランク番号をスキップすることはありません。構文

DENSE_RANK()

例

SELECT Id, FullName, DENSE_RANK() OVER (PARTITION BY Id ORDER BY FullName) AS Rank FROM Lead

PARTITION BY 句を追加すると、パーティションごとに別々のランクのセットが計算されます。

SELECT Id, FullName, DENSE_RANK() OVER (PARTITION BY Id ORDER BY FullName) AS Rank FROM Lead

ROW_NUMBER

各レコードに対して行番号を計算します。OVER 句内の ORDER BY 句が必須です。構文

ROW_NUMBER()

例

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(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

パラメータ

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

行数がバケット数で割り切れない場合、NTILE() 関数は 1 つ違いの 2 種類のサイズのグループを返します。大きいグループは、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;

This statement uses the NTILE() function to divide ten rows into three groups:
SELECT
    v,
    NTILE (3) OVER (
        ORDER BY v
    ) buckets
FROM
    sales.ntile_demo;

分析関数

これらのウィンドウ関数は、ウィンドウ内のレコードに対して分析的演算を実行します。

PERCENT_RANK

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

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

パラメータ

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

​ウィンドウ関数の句

​OVER

​PARTITION BY

​算術関数

​COUNT

​COUNT_BIG

​MIN

​MAX

​SUM

​AVG

​MEDIAN

​STDEV

​STDEVP

​VAR

​VARP

​ランキング関数

​RANK

​DENSE_RANK

​ROW_NUMBER

​NTILE

​分析関数

​PERCENT_RANK

ウィンドウ関数の句

OVER

PARTITION BY

算術関数

COUNT

COUNT_BIG

MIN

MAX

SUM

AVG

MEDIAN

STDEV

STDEVP

VAR

VARP

ランキング関数

RANK

DENSE_RANK

ROW_NUMBER

NTILE

分析関数

PERCENT_RANK