ウィンドウ関数

ウィンドウ関数では、隣接したグループから集計されたデータを使用して 1 つのグループのデータを計算できます。ウィンドウを使用しても、クエリから返される行数に変更はありません。ウィンドウでは、グループ内ではなく複数グループにわたる集計を行い、集計基準として有効な数値射影を受け入れます。

集計関数を使用するウィンドウでは次の構文を使用します。

順位付け関数を使用している場合は次の構文を使用します。

この場合、次のようになります。

windowfunction

ウィンドウをサポートする集計関数。現在サポートされている関数は、avg、sum、min、max、count、median、percentile_disc、percentile_cont です。

rankfunction

パーティション内の各行の順位値を返します。サポートされている順位付け関数は rank()、dense_rank()、cume_dist()、および row_number() です。例については、「順位付け関数」セクションを参照してください。

projection expression

指定した列の値から射影を生成するために使用される式。

row range

次の構文を使用して行の範囲を指定します。

範囲	意味
[.. 0]	リセットグループの開始行から現在の行まで。
[0 ..]	リセットグループの現在の行から最後の行まで。
[-2 .. 0]	現在の行の 2 行前から。ウィンドウの対象は 3 行です。
[0 .. 2]	現在の行から現在の行の 2 行後まで。ウィンドウの対象は 3 行です。
[-1 .. -1]	現在の行の 1 行前。ウィンドウには 1 行が含まれます。
[.. -2]	リセットグループの開始行から現在の行の 2 行前まで。
[..]	リセットグループ全体を集計します。

reset groups

値が変更されたときにウィンドウ集計をリセットする列。all のリセットグループは、ウィンドウ集計ですべての列をリセットすることを示します。

order clause

並び替えに使用する列を指定します。このアクションにより、ウィンドウ関数が評価される前に、行が並び替えられます。

order 句は、行の範囲が [..] でウィンドウ関数が sum、avg、min、または max の式には使用できません。たとえば、sum(sum(Sales)) over([..] partition by Year order by Quarter) は無効です。

メモ

label

出力列の名前。

メモ

グループ化されたクエリ

ウィンドウ関数はグループ化されたクエリでのみ有効です。次の例は無効です。

複数のリセットと複数の並び替え

複数のリセットと複数の並び替えは有効です。次に例を示します。

共通グループ

ウィンドウ関数は共通グループクエリで使用できます。次に例を示します。

sum(sum(a[Sales])) over([-2 .. 0] partition by (a[Year], a[Quarter]) order by (a[Year] asc, sum(a[Sales]) desc))

各ウィンドウ関数は 1 つの共通グループストリームでのみ使用できます。次の例は無効です。

メモ

関数の使用についての詳細は、「集計関数」を参照してください。

例 - 上位 5 人の営業担当を動的に表示する

作成されたグラフには、各国の上位 5 人の営業担当と、各営業担当のランキングが表示されます。

例

累計値 (リセットなし)

次のクエリは、四半期ごとの売上合計の累計値を計算します。「partition by all」は、合計がどの列でもリセットされないことを示します。

年ごとの累計値

累計値は毎年リセットされます。

移動 3 四半期の最小売上 (移動最小)

最後の 2 行から現在の行までの範囲の移動最小値を調べます。

パーセント合計

次のクエリは、その年の四半期売上のパーセントを計算します。行の範囲 [..] は、パーセントを計算する数式で使用される、各年の小計を計算します。

年間の差異

次のクエリは、前四半期と比較した売上の成長を計算します。[-1 .. -1] は、その行の四半期の前の四半期を示します。結果表の空白スペースは null 値を表します。

順位付け関数

rank()

並び替えに基づいて順位を割り当てます。値が同じ場合は同じ順位を繰り返し、次の一致しない値で繰り返した数だけ順位をスキップします。

dense_rank()

rank() と同じですが、前の繰り返し数に基づいて値をスキップしません。

cume_dist()

リセットグループ内のデータの累積分布 (相対位置) を計算します。

row_number()

リセットグループ内のすべての行に 1 ずつ増分する番号を割り当てます。

例

次の表には、上記のコードで rank() を dense_rank()、cume_dist()、および row_number() 関数に置き換えた場合の結果列も示されています。

次のクエリは、年内で実績の高い上位 3 四半期を表示します。

次のクエリは、95 パーセントを表示します。

関数の使用についての詳細は、「集計関数」を参照してください。