標準 SQL での分析関数の概要

分析関数は、行のグループに対して値を計算し、各行に対して 1 つの結果を返します。これは、行のグループ全体に対して 1 つの結果を返す集計関数とは異なります。

これには、評価対象の行を囲む行のウィンドウを定義する OVER 句が含まれています。各行について、選択された行のウィンドウを入力として使用して、分析関数の結果が計算され、場合によっては集計が行われます。

分析関数を使用すると、移動平均の計算、項目のランク付け、累積合計の計算、その他の分析を行うことができます。

分析関数には、ナビゲーション関数番号付け関数集計分析関数といったカテゴリがあります。

分析関数の構文

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

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

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

表記:

  • 角括弧「[ ]」はオプションの句です。
  • 丸括弧「( )」はリテラルの括弧を表します。
  • 縦線「|」は論理 OR を表します。
  • 波括弧「{ }」ではオプションのセットを囲みます。
  • 角括弧内の省略記号に先立つカンマ「[, ... ]」は、前の項目をカンマで区切ったリストに繰り返すことができることを示しています。

説明

分析関数は、行のグループに対する結果を計算します。これらの関数は、ナビゲーション関数番号付け関数集計分析関数などの分析関数として使用できます。

  • analytic_function_name: 分析オペレーションを実行する関数。番号付け関数 RANK() などを指定できます。
  • argument_list: 分析関数に固有の引数。固有の引数がある関数とない関数があります。
  • OVER: OVERの前に置かれる分析関数の構文で必要なキーワード。

  • 分析関数は、クエリ内の 2 つの場所でスカラー式のオペランドとして使用できます。
    • SELECT リスト。分析関数が SELECT リストにある場合、その引数リストと OVER 句は、同じ SELECT リストで導入されたエイリアスを参照できません。
    • ORDER BY 句。分析関数がクエリの ORDER BY 句にある場合、その引数リストは SELECT リスト エイリアスを参照できます。
  • 分析関数は、エイリアスを介して間接的であっても、引数リストまたはその OVER 句の中で別の分析関数を参照できません。
  • 分析関数が評価されるのは集計の後です。たとえば、GROUP BY 句と分析以外の集計関数が最初に評価されます。集計関数は分析関数の前に評価されるため、分析関数に対する入力オペランドとして使用できます。

戻り値

入力の行ごとに 1 つの結果。

OVER 句を定義する

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

説明

OVER 句は、分析関数を使用するテーブル内の行のグループを定義するウィンドウを参照します。クエリで定義された named_window を指定するか、新しいウィンドウ指定を定義できます。

名前付きウィンドウもウィンドウ指定も指定されていない場合、すべての入力行がすべての行のウィンドウに含まれます。

OVER 句を使用した例

これらのクエリではウィンドウの仕様が指定されています。

これらのクエリでは、名前付きウィンドウが使用されています。

ウィンドウ指定を定義する

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

説明

ウィンドウ指定を定義します。

  • named_window: WINDOWで定義された既存のウィンドウの名前。

  • PARTITION BY: 入力行を別々のパーティションに分割し、それらを対象に分析関数を個別に評価します。

    • PARTITION BY 句には複数のパーティション式を使用できます。
    • 式には、浮動小数点型、グループ化できない型、定数、分析関数を含めることはできません。
    • オプションであるこの句を使用しない場合は、入力テーブルのすべての行が単一のパーティションになります。
  • ORDER BY: パーティション内の行の順序を定義します。ほとんどの場合、この句は省略可能ですが、ナビゲーション関数では必須になる場合があります。

  • window_frame_clause: 集計分析関数の場合、現在のパーティション内のウィンドウ フレームを定義します。ウィンドウ フレームによって、ウィンドウに含めるものが決まります。この句を使用する場合、制限なしウィンドウを除き ORDER BY が必要になります。

  • ORDER BY 句も Window Frame 句も存在しない場合、ウィンドウ フレームにはそのパーティション内のすべての行が含まれます。
  • 集計分析関数の場合、ORDER BY 句が存在しても Window Frame 句が存在しない場合は、次の Window Frame 句がデフォルトで使用されます。

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    

    たとえば、次のクエリは同等です。

    SELECT book, LAST_VALUE(item)
      OVER (ORDER BY year)
    FROM Library
    
    SELECT book, LAST_VALUE(item)
      OVER (
        ORDER BY year
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM Library
    

ウィンドウ指定の中で名前付きウィンドウを使用する際のルール

ウィンドウ指定の中で名前付きウィンドウを使用する場合は、次のルールが適用されます。

  • 名前付きウィンドウの指定は、ウィンドウ指定句で新しく指定を定義することで拡張できます。
  • 重複した定義は使用できません。名前付きウィンドウに ORDER BY 句が記述されておりウィンドウ指定句が含まれる場合、エラーがスローされます。
  • 句は決められた順序で配置します。PARTITION BY を最初に配置し、ORDER BYwindow_frame_clause はその後に配置する必要があります。名前付きウィンドウを追加した場合、そのウィンドウ指定が最初に処理されます。

    --this works:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
    FROM Produce
    WINDOW item_window AS (ORDER BY purchases)
    
    --this does not work:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ORDER BY purchases) AS most_popular
    FROM Produce
    WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
    
  • ウィンドウ指定の中では名前付きウィンドウを PARTITION BY とともに指定できません。PARTITION BY が必要な場合は、名前付きウィンドウに追加します。

  • ORDER BY 句、外部クエリ、サブクエリの中では名前付きウィンドウを参照できません。

ウィンドウ指定を使用した例

以下のクエリでは、分析関数の中でパーティションが定義されます。

以下のクエリのウィンドウ指定には、名前付きウィンドウが含まれます。

以下のクエリでは、パーティション内の行の順序が定義されます。

Window Frame 句の定義

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

frame_between:
  {
    BETWEEN  unbounded_preceding AND frame_end_a
    | BETWEEN numeric_preceding AND frame_end_a
    | BETWEEN current_row AND frame_end_b
    | BETWEEN numeric_following AND frame_end_c

frame_start:
  { unbounded_preceding | numeric_preceding | [ current_row ] }

frame_end_a:
  { numeric_preceding | current_row | numeric_following | unbounded_following }

frame_end_b:
  { current_row | numeric_following | unbounded_following }

frame_end_c:
  { numeric_following | unbounded_following }

unbounded_preceding:
  UNBOUNDED PRECEDING

numeric_preceding:
  numeric_expression PRECEDING

unbounded_following:
  UNBOUNDED FOLLOWING

numeric_following:
  numeric_expression FOLLOWING

current_row:
  CURRENT ROW

Window Frame 句は、分析関数が評価されるパーティション内の現在の行を囲むウィンドウ フレームを定義します。集計分析関数でのみ Window Frame 句を使用できます。

  • rows_range: 物理行または論理範囲を持つウィンドウ フレームを定義する句。

    • ROWS: 現在の行からの物理オフセットに基づいてウィンドウ フレームを計算します。たとえば、現在の行の前後に 2 つの行を含めることができます。
    • RANGE: 現在の行の論理的な範囲に基づいたウィンドウ フレームを、現在の行の ORDER BY キー値に基づいて計算します。指定した範囲の値を現在の行のキー値に加算、またはキー値から除算して、window frame の開始または終了範囲の境界を定義します。範囲ベースのウィンドウ フレームの場合、ORDER BY 句の中に 1 つの式がありその式は数値型である必要があります。

      ヒント: 日付の範囲を使用する場合は、UNIX_DATE() 関数で ORDER BY を使用します。タイムスタンプ付きの範囲を使用する場合は、UNIX_SECONDS()UNIX_MILLIS()、または UNIX_MICROS() 関数を使用します。

  • frame_between: 下限と上限のウィンドウ フレームを作成します。最初の境界は下限を表します。2 つ目の境界は上限を表します。上記の構文に示すように、特定の境界の組み合わせのみを使用できます。

    • 次の境界を使用して、ウィンドウ フレームの開始を定義できます。
      • unbounded_preceding: ウィンドウ フレームがパーティションの先頭から始まります。
      • numeric_preceding または numeric_following: ウィンドウ フレームの開始は現在の行を基準とします。
      • current_row: ウィンドウ フレームが現在の行から始まります。
    • frame_end_a ... frame_end_c: ウィンドウ フレームの終了を定義します。
      • numeric_preceding または numeric_following: 現在の行を基準としてウィンドウ フレームの終了が決まります。
      • current_row: ウィンドウ フレームが現在の行で終了します。
      • unbounded_following: ウィンドウ フレームがパーティションの末尾で終了します。
  • frame_start: 下限を持つウィンドウ フレームを作成します。ウィンドウ フレームは現在の行で終了します。

    • unbounded_preceding: ウィンドウ フレームがパーティションの先頭から始まります。
    • numeric_preceding: 現在の行を基としてウィンドウ フレームの開始が決まります。
    • current_row: ウィンドウ フレームが現在の行から始まります。
  • numeric_expression: 数値型を表す式。数値式は負ではない定数またはパラメータでなければなりません。

  • 境界がパーティションの先頭または末尾を超えている場合、ウィンドウ フレームにはそのパーティション内の行のみが含まれます。
  • ナビゲーション関数番号付け関数RANK() など)では Window Frame 句を使用できません。

Window Frame 句を使用した例

以下のクエリでは、ROWS を使用して値を計算します。

以下のクエリでは、RANGE を使用して値を計算します。

以下のクエリでは、部分的または完全にバインドされていないウィンドウで値が計算されます。

以下のクエリでは、数値境界を持つ値が計算されます。

以下のクエリでは、現在の行を境界として値が計算されます。

名前付きウィンドウを参照する

SELECT query_expr,
  analytic_function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ ASC | DESC [, ...] ]
  [ window_frame_clause ]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

名前付きウィンドウは、分析関数を使用するテーブル内の行のグループを表します。名前付きウィンドウは WINDOWで定義され、分析関数の OVERで参照されます。OVER 句は、名前付きウィンドウを単独で使用することも、ウィンドウ指定内に埋め込むこともできます。

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

OVER 句の要件:

  • PARTITION BY: 省略可。
  • ORDER BY:
    1. PERCENTILE_CONTPERCENTILE_DISC には許可されません。
    2. FIRST_VALUELAST_VALUENTH_VALUELEADLAG については必須です。
  • window_frame_clause:
    1. PERCENTILE_CONTPERCENTILE_DISCLEADLAG には許可されません。
    2. FIRST_VALUELAST_VALUENTH_VALUE についてはオプションです。

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

番号付け関数のコンセプト

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

RANK()DENSE_RANK()ROW_NUMBER() の例:

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

+---------------------------------------------------+
| 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 を返します。

集計分析関数のコンセプト

集計関数は、一連の値に対して計算を実行する関数です。ほとんどの集計関数は分析関数の中で使用できます。これらの集計関数は、集計分析関数と呼ばれます。

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

分析関数の例

これらの例では、ハイライト表示された項目が現在の行です。太字の項目は、分析に含まれる行です。

例で使用される一般的なテーブル

以降の集計分析クエリの例では、ProduceEmployeesFarm の各テーブルが使用されます。

Produce テーブル

いくつかの例では、Produce というテーブルが参照されます。

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce

+-------------------------------------+
| item      | category   | purchases  |
+-------------------------------------+
| kale      | vegetable  | 23         |
| orange    | fruit      | 2          |
| cabbage   | vegetable  | 9          |
| apple     | fruit      | 8          |
| leek      | vegetable  | 2          |
| lettuce   | vegetable  | 10         |
+-------------------------------------+

Employees テーブル

いくつかの例では、Employees というテーブルが参照されます。

WITH Employees AS
 (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
  UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
  UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
  UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
  UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
  UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees

+-------------------------------------+
| name      | department | start_date |
+-------------------------------------+
| Isabella  | 2          | 1997-09-28 |
| Anthony   | 1          | 1995-11-29 |
| Daniel    | 2          | 2004-06-24 |
| Andrew    | 1          | 1999-01-23 |
| Jacob     | 1          | 1990-07-11 |
| Jose      | 2          | 2013-03-17 |
+-------------------------------------+

Farm テーブル

いくつかの例では、Farm というテーブルが参照されます。

WITH Farm AS
 (SELECT 'cat' as animal, 23 as population, 'mammal' as category
  UNION ALL SELECT 'duck', 3, 'bird'
  UNION ALL SELECT 'dog', 2, 'mammal'
  UNION ALL SELECT 'goose', 1, 'bird'
  UNION ALL SELECT 'ox', 2, 'mammal'
  UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm

+-------------------------------------+
| animal    | category   | population |
+-------------------------------------+
| cat       | mammal     | 23         |
| duck      | bird       | 3          |
| dog       | mammal     | 2          |
| goose     | bird       | 1          |
| ox        | mammal     | 2          |
| goat      | mammal     | 2          |
+-------------------------------------+

総計を計算する

これにより、Produce テーブル内のすべてのアイテムの総計が計算されます。

  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 54
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 54
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 54
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 54
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 54
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 54
SELECT item, purchases, category, SUM(purchases)
  OVER () AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 54              |
| leek      | 2          | vegetable  | 54              |
| apple     | 8          | fruit      | 54              |
| cabbage   | 9          | vegetable  | 54              |
| lettuce   | 10         | vegetable  | 54              |
| kale      | 23         | vegetable  | 54              |
+-------------------------------------------------------+

小計を計算する

これにより、Produce テーブルの各カテゴリの小計が計算されます。

  • 果物
    • (orange, apple) = 合計購入数 10
    • (orange, apple) = 合計購入数 10
  • 野菜
    • (leek, cabbage, lettuce, kale) = 合計購入数 44
    • (leek, cabbage, lettuce, kale) = 合計購入数 44
    • (leek, cabbage, lettuce, kale) = 合計購入数 44
    • (leek, cabbage, lettuce, kale) = 合計購入数 44
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 10              |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 44              |
| cabbage   | 9          | vegetable  | 44              |
| lettuce   | 10         | vegetable  | 44              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

累計を計算する

これにより、Produce テーブルの各カテゴリの累計が計算されます。合計は、ORDER BY 句を使用して定義された順序に対して計算されます。

  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 2
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 10
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 2
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 11
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 21
  • (orange, apple, leek, cabbage, lettuce, kale) = 合計購入数 44
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

これは上記の例と同じです。読みやすさのために CURRENT ROW を境界として追加する場合以外は、これを追加する必要はありません。

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS UNBOUNDED PRECEDING
  ) AS total_purchases
FROM Produce

この例では、Produce テーブル内のすべてのアイテムがパーティションに含まれています。前の行のみが分析されます。分析は、パーティション内の現在の行の 2 つ前に開始します。

  • (orange, leek, apple, cabbage, lettuce, kale) = NULL
  • (orange, leek, apple, cabbage, lettuce, kale) = NULL
  • (orange, leek, apple, cabbage, lettuce, kale) = 2
  • (orange, leek, apple, cabbage, lettuce, kale) = 4
  • (orange, leek, apple, cabbage, lettuce, kale) = 12
  • (orange, leek, apple, cabbage, lettuce, kale) = 21
SELECT item, purchases, category, SUM(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) AS total_purchases
FROM Produce;

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | NULL            |
| leek      | 2          | vegetable  | NULL            |
| apple     | 8          | fruit      | 2               |
| cabbage   | 9          | vegetable  | 4               |
| lettuce   | 10         | vegetable  | 12              |
| kale      | 23         | vegetable  | 21              |
+-------------------------------------------------------+

移動平均を計算する

これにより、Produce テーブルの移動平均が計算されます。下限は現在の行の 1 行前です。上限は現在の行の 1 行後です。

  • (orange, leek, apple, cabbage, lettuce, kale) = 平均購入数 2
  • (orange, leek, apple, cabbage, lettuce, kale) = 平均購入数 4
  • (orange, leek, apple, cabbage, lettuce, kale) = 平均購入数 6.3333
  • (orange, leek, apple, cabbage, lettuce, kale) = 平均購入数 9
  • (orange, leek, apple, cabbage, lettuce, kale) = 平均購入数 14
  • (orange, leek, apple, cabbage, lettuce, kale) = 平均購入数 16.5
SELECT item, purchases, category, AVG(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | avg_purchases   |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| leek      | 2          | vegetable  | 4               |
| apple     | 8          | fruit      | 6.33333         |
| cabbage   | 9          | vegetable  | 9               |
| lettuce   | 10         | vegetable  | 14              |
| kale      | 23         | vegetable  | 16.5            |
+-------------------------------------------------------+

範囲内のアイテム数を計算する

この例では、Farm テーブル内の類似したポピュレーション カウントの動物数を取得します。

  • (goose, dog, ox, goat, duck, cat) = 数が 0~2 の範囲の動物は 4 種類。
  • (goose, dog, ox, goat, duck, cat) = 数が 1~3 の範囲の動物は 5 種類。
  • (goose, dog, ox, goat, duck, cat) = 数が 1~3 の範囲の動物は 5 種類。
  • (goose, dog, ox, goat, duck, cat) = 数が 1~3 の範囲の動物は 5 種類。
  • (goose, dog, ox, goat, duck, cat) = 数が 2~4 の範囲の動物は 4 種類。
  • (goose, dog, ox, goat, duck, cat) = 数が 22~24 の範囲の動物は 1 種類。
SELECT animal, population, category, COUNT(*)
  OVER (
    ORDER BY population
    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS similar_population
FROM Farm;

+----------------------------------------------------------+
| animal    | population | category   | similar_population |
+----------------------------------------------------------+
| goose     | 1          | bird       | 4                  |
| dog       | 2          | mammal     | 5                  |
| ox        | 2          | mammal     | 5                  |
| goat      | 2          | mammal     | 5                  |
| duck      | 3          | bird       | 4                  |
| cat       | 23         | mammal     | 1                  |
+----------------------------------------------------------+

この例では、各カテゴリで最も人気のあるアイテムを取得します。各パーティションでウィンドウ内の行をどのように分割および順序付けするかを定義します。Produce テーブルが参照されます。

  • 果物
    • (orange, apple) = apple が最も人気のあるアイテム
    • (orange, apple) = apple が最も人気のあるアイテム
  • 野菜
    • (leek, cabbage, lettuce, kale) = kale が最も人気のあるアイテム
    • (leek, cabbage, lettuce, kale) = kale が最も人気のあるアイテム
    • (leek, cabbage, lettuce, kale) = kale が最も人気のあるアイテム
    • (leek, cabbage, lettuce, kale) = kale が最も人気のあるアイテム
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | kale         |
| cabbage   | 9          | vegetable  | kale         |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

範囲の最後の値を取得する

この例では、Produce テーブルを使用して、特定のウィンドウ フレームで最も人気のあるアイテムを取得します。ウィンドウ フレームは一度に 3 行までを分析します。野菜の most_popular 列を確認してください。特定のカテゴリで最も人気のあるアイテムを取得するのではなく、そのカテゴリの特定の範囲で最も人気のあるアイテムが取得されます。

  • 果物
    • (orange, apple) = apple が最も人気のあるアイテム
    • (orange, apple) = apple が最も人気のあるアイテム
  • 野菜
    • (leek, cabbage, lettuce, kale) = leek が最も人気のあるアイテム
    • (leek, cabbage, lettuce, kale) = lettuce が最も人気のあるアイテム
    • (leek, cabbage, lettuce, kale) = kale が最も人気のあるアイテム
    • (leek, cabbage, lettuce, kale) = kale が最も人気のあるアイテム
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | cabbage      |
| cabbage   | 9          | vegetable  | lettuce      |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

この例では、上記と同じ結果が返されますが、item_window という名前のウィンドウが含まれています。一部のウィンドウ指定は OVER 句で直接定義され、一部は名前付きウィンドウで定義されます。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases)

ランクを計算する

この例では、部署内の各従業員のランクを、開始日に基づいて計算します。ウィンドウ指定は OVER 句で直接定義されます。Employees テーブルが参照されます。

  • 部署 1
    • (Jacob, Anthony, Andrew) = Jacob にランク 1 を割り当てる
    • (Jacob, Anthony, Andrew) = Anthony にランク 2 を割り当てる
    • (Jacob, Anthony, Andrew) = Andrew にランク 3 を割り当てる
  • 部署 2
    • (Isabella, Daniel, Jose) = Isabella にランク 1 を割り当てる
    • (Isabella, Daniel, Jose) = Daniel にランク 2 を割り当てる
    • (Isabella, Daniel, Jose) = Jose にランク 3 を割り当てる
SELECT name, department, start_date,
  RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;

+--------------------------------------------+
| name      | department | start_date | rank |
+--------------------------------------------+
| Jacob     | 1          | 1990-07-11 | 1    |
| Anthony   | 1          | 1995-11-29 | 2    |
| Andrew    | 1          | 1999-01-23 | 3    |
| Isabella  | 2          | 1997-09-28 | 1    |
| Daniel    | 2          | 2004-06-24 | 2    |
| Jose      | 2          | 2013-03-17 | 3    |
+--------------------------------------------+

Window Frame 句で名前付きウィンドウを使用する

名前付きウィンドウと Window Frame 句で一部のロジックを定義できます。このロジックは結合されています。Produce テーブルを使用した例を次に示します。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

+-------------------------------------------------------+
| item      | purchases  | category   | most_popular    |
+-------------------------------------------------------+
| orange    | 2          | fruit      | apple           |
| apple     | 8          | fruit      | apple           |
| leek      | 2          | vegetable  | lettuce         |
| cabbage   | 9          | vegetable  | kale            |
| lettuce   | 10         | vegetable  | kale            |
| kale      | 23         | vegetable  | kale            |
+-------------------------------------------------------+

次の例でも以前の結果を確認できます。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  item_window AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  item_window AS (b)

次の例では、Window Frame 句が 2 回定義されているため、エラーが発生します。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS most_popular
FROM Produce
WINDOW item_window AS (
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)