標準 SQL での分析関数のコンセプト

分析関数のコンセプト

このトピックでは、BigQuery の分析関数の機能について説明します。BigQuery がサポートする分析関数の説明については、ナビゲーション関数番号付け関数集計分析関数のリファレンス トピックをご覧ください。

データベースでは、分析関数は行のグループ全体に対して集計値を計算する関数です。行のグループに対して単一の集計値を返す集計関数とは異なり、分析関数は入力行のグループに対して分析関数を計算することで、行ごとに単一の値を返します。

分析関数は複雑な分析オペレーションを簡潔に表す強力なメカニズムであり、分析関数を使用しなければコストの高いセルフ JOIN や SQL クエリ外での計算を行う必要が生じる評価を効率的に行えます。

分析関数は、SQL 標準や一部の商用データベースで「(分析)ウィンドウ関数」とも呼ばれています。これは、分析関数が windowwindow frame と呼ばれる行のグループに対して評価されるためです。その他の一部のデータベースでは、オンライン分析処理(OLAP)関数と呼ばれることもあります。

簡単な構文:

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

分析関数には OVER 句が必要です。この句は、分析関数の評価対象になる window frame を定義します。OVER 句には、次の 3 つのオプションの句が含まれています。BigQuery は、OVER 句のサブ句を、それらが書き込まれる順番で評価します。

  • PARTITION BY 句は、入力行を複数のパーティションに分割します。これは GROUP BY と似ていますが、実際に行を同じキーに結合する作業は行われません。
  • ORDER BY 句は、各パーティション内の順序付けを指定します。
  • window_frame_clause は、現在のパーティション内の window frame を定義します。

OVER 句は空(OVER())にすることもできます。この場合、window frame にはすべての入力行が含まれます。

分析関数は集約(GROUP BY および非分析関数)の後に評価されます。

たとえば、ある会社が各従業員の「勤続ランク」を示す、つまり勤務年数が最も長い従業員を示す、各部門のスコアボードを作成するとします。Employees テーブルには、NameStartDate、および Department という列が含まれています。

次のクエリは、部門内の各従業員のランクを計算します。

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

図 1 に概念的なコンピューティング プロセスを示します。

マークダウン イメージ 図 1: 分析関数の図

BigQuery は、OVER 句内のサブ句を、出現する順序で評価します。

  1. PARTITION BY: テーブルは最初に department ごとに 2 つのパーティションに分割されます。
  2. ORDER BY: 各パーティション内の従業員の行は startdate によって順序付けされます。
  3. フレーミング: なし。ウィンドウ フレーム句を RANK() に対して使用することはできません。これはすべての番号付け関数を対象にしているためです。
  4. RANK(): 勤続ランクは、window frame に対して行ごとに計算されます。

分析関数の構文

analytic_function_name ( [ argument_list ] )
  OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

分析関数はスカラー式またはスカラー式のオペランドとしてクエリ内の次の 2 か所のみに使用できます。

  • SELECT リスト。分析関数が SELECT リスト内で使用されると、その argument_list は同じ SELECT リストで導入された別名を参照できません。
  • ORDER BY。分析関数がクエリの ORDER BY 句で使用されると、その argument_list は SELECT リスト別名を参照できます。

また、分析関数は別名を間接的に介する場合でも、その argument_list または OVER 句内の別の分析関数を参照できません。

無効:

SELECT ROW_NUMBER() OVER () AS alias1
FROM Singers
ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

上記のクエリでは、分析関数 alias1 が分析関数 ROW_NUMBER() OVER() に解決されます。

OVER 句

構文:

OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY sort_specification_list ]
  [ window_frame_clause ]

OVER 句には、次の 3 つのコンポーネントがあります。

  • PARTITION BY
  • ORDER BY
  • window_frame_clause または window_name。これは、WINDOW 句に定義されている window_specification を参照します。

OVER 句が空(OVER())の場合、すべての入力行を含む単一のパーティションに対して分析関数が計算されます。つまり、出力行ごとに同じ結果が生成されます。

PARTITION BY 句

構文:

PARTITION BY expression [, ... ]

PARTITION BY 句は入力行を別個のパーティションに分割します。それらに対して、分析関数が個別に評価されます。PARTITION BY 句では複数の expressions を使用できます。

expression のデータ型は Groupable で、かつパーティショニングをサポートしている必要があります。つまり、expression を次のデータ型にすることはできません

  • 浮動小数点数
  • STRUCT
  • ARRAY

このリストは、GROUP BY でサポートされていないデータ型のリストとほぼ同じですが、追加で浮動小数点数型も除外されています(BigQuery のデータ型の上部にあるデータ型プロパティ表の「Groupable」を参照)。

PARTITION BY 句がない場合、BigQuery は入力全体を単一のパーティションとして処理します。

ORDER BY 句

構文:

ORDER BY expression [ ASC | DESC ] [, ... ]

ORDER BY 句は各パーティション内の順序を定義します。ORDER BY 句がない場合、パーティション内の行の順番は変わる可能性があります。一部の分析関数には ORDER BY が必要です。これは、分析関数の各ファミリーのセクションに示されています。ORDER BY 句が存在しても、一部の関数は window frame 内の順番から影響を受けません(COUNT など)。

OVER 句内の ORDER BY 句は、次の点で標準の ORDER BYと一致します。

  • 複数の expressions が存在する可能性があります。
  • expression には、順序付けをサポートするタイプが必要です。
  • オプションの ASC/DESC 仕様を各 expression に使用できます。
  • NULL 値は可能な最小値として順序付けされます(ASC の場合は最初、DESC の場合は最後)。

データ型のサポートは、次の型が順序付けをサポートしていないという点で、標準の ORDER BYと同じです。

  • ARRAY
  • STRUCT

OVER 句に ORDER BY 句が含まれているが、window_frame_clause は含まれていない場合、ORDER BY は暗黙的に window_frame_clause を次のように定義します。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

window_frame_clauseORDER BY 句のいずれも存在しない場合、window frame はデフォルトでパーティション全体に設定されます。

Window Frame 句

構文:

{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_clause はパーティション内の現在の行の周囲に window frame を定義し、その範囲を対象に分析関数が評価されます。 window_frame_clause には、物理ウィンドウ フレーム(ROWS によって定義)と論理ウィンドウ フレーム(RANGE によって定義)の両方を使用できます。OVER 句に ORDER BY 句は含まれているが、window_frame_clause は含まれていない場合、ORDER BY は暗黙的に window_frame_clause を次のように定義します。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

window_frame_clauseORDER BY 句のいずれも存在しない場合、window frame はデフォルトでパーティション全体に設定されます。

numeric_expression は定数またはクエリ パラメータにのみすることができます。これらは両方とも負ではない値を持つ必要があります。負の値を持つ場合、BigQuery はエラーが発生します。

Window Frame 句の例:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • パーティション全体を含めます。
  • パーティション全体の総計の計算などに使用されます。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 現在の行の前、または現在の行を含む、パーティション内のすべての行を含めます。
  • 累積合計の計算などに使用されます。
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  • 現在の行の 2 つ前と 2 つ後の行の間のすべての行を含めます。
  • 移動平均の計算などに使用されます。

window_frame_specBETWEEN 句を使用する場合:

  • window_frame_boundary_start で、window_frame_boundary_end の境界よりも後で開始しない境界を指定する必要があります。これを使用すると、次のようになります。
    1. window_frame_boundary_startCURRENT ROW が含まれる場合、window_frame_boundary_endPRECEDING を含めることはできません。
    2. window_frame_boundary_startFOLLOWING が含まれる場合、window_frame_boundary_endCURRENT ROW または PRECEDING を含めることはできません。
  • window_frame_boundary_start にデフォルト値はありません。

上記以外の場合、指定された window_frame_spec 境界はウィンドウ フレームの開始点を表し、ウィンドウ フレーム境界の終了点はデフォルトで 'CURRENT ROW' に設定されます。つまり、

ROWS 10 PRECEDING

は、次と等価です。

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
ROWS

ROWS ベースのウィンドウ フレームは、現在の行からの物理オフセットに基づいて window frame を計算します。たとえば、以下のウィンドウ フレームは現在の行の周りのサイズ 5(最大)のウィンドウ フレームを定義します。

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

window_frame_clause 内の numeric_expression は現在の行からの行数として解釈され、定数の負ではない整数である必要があります。これはクエリ パラメータにすることもできます。

所定の行の window frame がパーティションの開始点または終了点を越える場合、window frame はそのパーティション内の行のみを含めます。

たとえば、列 zxy を持つ次のテーブルの場合を考えてみます。

z x y
1 5 AA
2 2 AA
3 11 AB
4 2 AA
5 8 AC
6 10 AB
7 1 AB

次の分析関数の場合を考えてみます。

SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING)

PARTITION BY 句は y 値に基づいてテーブルを 3 つのパーティションに分割し、ORDER BYz 値によって各パーティション内の行を順序付けします。

パーティション 1/3:

z x y
1 5 AA
2 2 AA
4 2 AA

パーティション 2/3:

z x y
3 11 AB
6 10 AB
7 1 AB

パーティション 3/3:

z x y
5 8 AC

以下のテーブルでは、太字は現在評価されている行を示し、色付きのセルはその行の window frame 内のすべての行を示します。

  • y = AA パーティション内の最初の行については、window frame には 2 行しか含まれません。これは、window_frame_spec によってウィンドウのサイズが 3 であると示されていても、先行する行がないからです。最初の行については、分析関数の結果は 7 になります。
z x y
1 5 AA
2 2 AA
4 2 AA
  • パーティション内の 2 番目の行については、window frame には 3 つすべての行が含まれます。2 番目の行については、分析関数の結果は 9 になります。
z x y
1 5 AA
2 2 AA
4 2 AA
  • パーティション内の最後の行については、後続の行がないため、window frame には 2 行しか含まれません。3 番目の行については、分析関数の結果は 4 になります。
z x y
1 5 AA
2 2 AA
4 2 AA
RANGE

RANGE ベースのウィンドウ フレームは、現在の行の window frame キー値に基づき、現在の行の周りの行の論理範囲に基づいて ORDER BY を計算します。指定した範囲の値を現在の行のキー値に加算、またはキー値から除算して、window frame の開始または終了範囲の境界を定義します。

ORDER BY 句は、ウィンドウが次の状態である場合を除き、指定する必要があります。

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

window_frame_clause 内の numeric_expression は、ORDER BY キーの現在行の値からのオフセットとして解釈されます。numeric_expression には数値型が必要です。DATE および TIMESTAMP は現在サポートされていません。また、numeric_expression は負ではない定数の整数またはパラメータにする必要があります。

RANGE ベースのウィンドウ フレームには、ORDER BY 句に最大で 1 つの expression が存在する可能性があり、expression には数値型が必要です。

単一のパーティションを持つ、RANGE ベースのウィンドウ フレームの例を以下に示します。

SELECT x, COUNT(*) OVER ( ORDER BY x
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS count_x
FROM T;

以下のテーブルでは、太字は現在評価されている行を示し、色付きのセルはその行の window frame 内のすべての行を示します。

  • 行 1 では x = 5 であるため、COUNT(*) には 3 <=x <= 7 の行のみが含まれます。
x count_x
5 1
2
11
2
8
10
1
  • 行 2 では x = 2 であるため、COUNT(*) には 0 <= x <= 4 の行のみが含まれます。
x count_x
5 1
2 3
11
2
8
10
1
  • 行 3 では x = 11 であるため、COUNT(*) には 9 <= x <= 13 の行のみが含まれます。
x count_x
5 1
2 3
11 2
2
8
10
1

WINDOW 句

構文:

WINDOW window_definition [, ...]
window_definition: window_name AS ( window_specification )

WINDOW 句は名前付きウィンドウのリストを定義します。その window_nameSELECT リストの分析関数で参照できます。これは、複数の分析関数に同じ window_frame_clause を使用する場合に便利です。

WINDOW 句は、クエリ構文に示されているように、SELECT 句の最後にのみ使用できます。

名前付きウィンドウ

WINDOW 句の定義後は、分析関数で名前付きのウィンドウを使用できるようになりますが、SELECT リストの場合に限り、ORDER BY 句で名前付きウィンドウを使用できません。名前付きウィンドウは単独で使用することも、OVER 句に埋め込むことができます。名前付きウィンドウは SELECT リストのエイリアスを参照できます。

例:

SELECT SUM(x) OVER window_name FROM ...
SELECT SUM(x) OVER (
  window_name
  PARTITION BY...
  ORDER BY...
  window_frame_clause)
FROM ...

OVER 句に埋め込んだ場合、window_name に関連付けられた window_specification は、同じ OVER 句内にある PARTITION BYORDER BY、および window_frame_clause と互換性を持つ必要があります。

名前付きウィンドウには次の規則が適用されます。

  • SELECT リストでのみ名前付きウィンドウを参照できます。つまり、ORDER BY 句、外側のクエリ、またはサブクエリでそれらを参照することはできません。
  • ウィンドウ W1(名前付きまたは名前なし)は、次の規則に従って名前付きウィンドウ NW2 を参照できます。
    1. W1 が名前付きウィンドウである場合、参照される名前付きウィンドウ NW2 は同じ WINDOW 句内で W1 よりも前にある必要があります。
    2. W1 に PARTITION BY 句を含めることはできません。
    3. W1 と NW2 の両方に ORDER BY 句が含まれる状態を true にすることはできません。
    4. NW2 に window_frame_clause を含めることはできません。
  • (名前付きまたは名前なし)ウィンドウ W1 が名前付きウィンドウ NW2 を参照する場合、結果となるウィンドウの指定は次を使用して定義されます。
    1. 存在する場合、NW2 からの PARTITION BY
    2. W1 または NW2 からの ORDER BY。いずれかが指定されている場合、両方に ORDER BY 句を使用することはできません。
    3. 存在する場合、W1 からの window_frame_clause

このトピックでは、分析ナビゲーション関数の機能について説明します。BigQuery がサポートする分析ナビゲーション関数の説明については、ナビゲーション関数の関数リファレンスをご覧ください。

一般に、ナビゲーション関数は現在の行からウィンドウ フレーム内の別の行に対して、いくつかの value_expression を計算します。OVER 句の構文は、ナビゲーション関数によって異なります。

OVER 句の要件:

  • PARTITION BY: 省略可。
  • ORDER BY:
    1. PERCENTILE_CONTPERCENTILE_DISC には許可されません。
    2. FIRST_VALUELAST_VALUENTH_VALUELEAD および LAG については必須です。
  • window_frame_clause:
    1. PERCENTILE_CONTPERCENTILE_DISCLEAD、および LAG には許可されません。
    2. FIRST_VALUELAST_VALUE、および NTH_VALUE についてはオプションです。

すべてのナビゲーション関数について、結果のデータ型は value_expression と同じ型になります。

番号付け関数

このトピックでは、分析番号付け関数の機能について説明します。BigQuery がサポートする分析番号付け関数の説明については、番号付け関数リファレンスをご覧ください。

番号付け関数は、指定されたウィンドウ内の行の位置に基づいて、各行に整数値を割り当てます。

RANK()DENSE_RANK()、および ROW_NUMBER() の例:

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...
x rank dense_rank row_num
1 1 1 1
2 2 2 2
2 2 2 3
5 4 3 4
8 5 4 5
10 6 5 6
10 6 5 7
  • RANK(): x=5 の場合、rank は 4 を返します。これは、RANK() が前のウィンドウ順序付けグループ内のピアの数によって増加するからです。
  • DENSE_RANK(): x=5 の場合、dense_rank は 3 を返します。これは、DENSE_RANK() は値をスキップせずに常に 1 だけ増加するからです。
  • ROW_NUMBER(): x=5 の場合、row_num は 4 を返します。

集計分析関数

BigQuery は、特定の集計関数を分析関数としてサポートしています。

これらの関数を使用して、OVER 句は集計関数呼び出しに単純に追加されますが、その他の点では関数呼び出し構文は変更されません。それらの集計関数と対応するものと同様に、これらの分析関数は集約を実行しますが、具体的に各行の関連するウィンドウ フレームを対象に実行します。これらの分析関数の結果データ型は、それらの集計関数に対応するものと同じです。

BigQuery がサポートする集計分析関数の説明については、集計分析関数の関数リファレンスをご覧ください。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。