WINDOW句

説明

SELECT クエリにおいて、WINDOW 句は、特定のウィンドウ仕様を持つ名前付きウィンドウを定義するために使用されます。「ウィンドウ」はFrameとも呼ばれ、OceanBaseデータベースは ROWS と RANGE の2種類のFrameセマンティクスをサポートしています。前者は物理行オフセットに基づくウィンドウであり、後者は論理値オフセットに基づくウィンドウです。

分析関数を使用する際、その後に OVER window_name を追加してウィンドウ仕様を参照します。OVER 句に適用される関数は分析関数と呼ばれます。分析関数の詳細については、関数の概要の分析関数セクションを参照してください。

構文

window_clause:
    WINDOW window_definition_list

window_definition_list:
    window_definition [, window_definition ...]

window_definition:
    window_name AS ([PARTITION BY expression_list] [ORDER BY order_by_condition_list] [win_window])

expression_list:
    expression [, expression ...]

order_by_condition_list:
    order_by_condition [, order_by_condition ...]

order_by_condition:
    expression [ASC | DESC]

win_window:
    {ROWS | RANGE} BETWEEN win_bounding AND win_bounding
    | {ROWS | RANGE} win_bounding

win_bounding:
    CURRENT ROW
    | win_interval {PRECEDING | FOLLOWING}

win_interval:
    expression
    | INTERVAL expression date_unit

date_unit:
    DAY
    | DAY_HOUR
    | DAY_MICROSECOND
    | DAY_MINUTE
    | DAY_SECOND
    | HOUR
    | HOUR_MICROSECOND
    | HOUR_MINUTE
    | HOUR_SECOND
    | MICROSECOND
    | MINUTE
    | MINUTE_MICROSECOND
    | MINUTE_SECOND
    | MONTH
    | QUARTER
    | SECOND
    | SECOND_MICROSECOND
    | WEEK
    | YEAR
    | YEAR_MONTH

パラメータの説明

window_definition

• window_name：ウィンドウの名前を指定し、定義されたウィンドウを識別するために使用されます。

• PARTITION BY expression_list：オプションです。分析関数のグループキーのリストを指定します。これにより、入力データを指定された式のリストに基づいてグループ化し、各グループに対して分析関数を適用します。これにより、分析関数内でデータセット全体ではなく、各グループごとに独立した計算を行うことができます。

  • expression：列または式を表します。

• ORDER BY order_by_condition_list：オプションです。グループ化後の結果セットのソート規則のリストを指定し、ウィンドウ内のデータのソート方法を定義します。

  • expression [ASC | DESC]：ウィンドウ定義のソート式を表します。ASC | DESC はオプションパラメータで、ソートの順序を指定するために使用されます。ASC は昇順（デフォルト）、DESC は降順を表します。

• win_window：オプションです。ウィンドウ範囲の定義を指定し、ROWS または RANGE の境界条件を含みます。詳細については、以下の win_window を参照してください。

win_window

• {ROWS | RANGE} BETWEEN win_bounding AND win_bounding：ウィンドウの境界が2つの境界値によって決定されることを表します。具体的には以下のとおりです：

  • ROWS | RANGE：Frameセマンティクスを指定します。

    • ROWS：物理ウィンドウを表し、ORDER BY 句でソートした後、先頭の N 行と末尾の N 行のデータを計算します。
    • RANGE：論理ウィンドウを表し、現在の行が対応する値の範囲を指定します。

  • win_bounding：ウィンドウ範囲の境界条件を示し、現在の行または現在の行からの間隔を指定できます。詳細については、後述の win_bounding を参照してください。

• {ROWS | RANGE} win_bounding：ウィンドウの境界が単一の境界値によって決定されることを示します。

win_bounding

• CURRENT ROW：現在の行をウィンドウの境界として使用することを示します。

• win_interval {PRECEDING | FOLLOWING}：現在の行を計算する際に、相対位置が現在の行の前か後かを指定します。これにより、ウィンドウの開始位置と終了位置を指定します。具体的には以下のとおりです：

  • win_interval：ウィンドウの間隔サイズを定義します。固定値、未指定値、小数値、無制限、または日付単位を指定できます。

    • expression：ウィンドウの開始位置を示します。値は整数で行数のオフセットを表す場合もあれば、以下のような特定のキーワードを表す場合もあります：

      • UNBOUNDED PRECEDING：ウィンドウの開始位置が無制限であることを示し、最初の行から計算を開始します。
      • UNBOUNDED FOLLOWING：ウィンドウの終了位置が無制限であることを示し、最後の行で計算を終了します。
      • CURRENT ROW：ウィンドウの開始位置が現在の行であることを示します。

    • INTERVAL expression date_unit：時間間隔の定義を示し、式と時間単位を含みます。

      注意

      • OceanBaseデータベースの現在のバージョンでは、INTERVAL 句は RANGE とのみ使用できます。
      • ORDER BY 句を使用してウィンドウ内のデータのソート方法を定義する場合、ウィンドウ定義のソート式が日付形式であることを確認する必要があります。

      • expression：間隔式を指定するために使用します。値は任意の整数で、間隔の数を表します。
      • date_unit：間隔の単位を指定します。値は YEAR、MONTH、DAY、HOUR などが可能です。

  • PRECEDING：前の N 行を使用します。

  • FOLLOWING：次の N 行を使用します。

例

test_tbl1 テーブルから指定された列のデータを照会し、各行で異なるウィンドウ条件に基づく col5 の合計を計算します。

1. テーブル test_tbl1 を作成します。

   CREATE TABLE test_tbl1 (col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     col2 INT,
     col3 VARCHAR(50),
     col4 DATE,
     col5 INT);
   

2. テストデータを挿入します。

   INSERT INTO test_tbl1(col2, col3, col4, col5)
     VALUES(100, 'A1', '2017-01-01', 120),
           (100, 'A1', '2018-01-01', 20),
           (100, 'A1', '2019-01-01', 100),
           (100, 'A1', '2020-01-01', 40),
           (100, 'B1', '2021-01-01', 80),
           (100, 'B1', '2022-01-01', 60),
           (200, 'B1', '2017-01-01', 70),
           (200, 'B1', '2018-01-01', 50),
           (200, 'C1', '2019-01-01', 90),
           (200, 'C1', '2020-01-01', 30),
           (200, 'C1', '2021-01-01', 110),
           (200, 'C1', '2022-01-01', 10);
   

3. test_tbl1 テーブルから col1、col3、col5 列を選択し、分析関数を使用して異なるウィンドウ条件に基づく col5 の合計を計算します。最後に col1 の値で昇順に結果を出力します。ウィンドウ条件には、異なる PARTITION BY と ORDER BY 句、および ROWS と RANGE 句が含まれます。具体的には以下のとおりです：

   1. my_window_1 には特定の条件がないため、テーブル全体が1つのウィンドウと見なされます。sum1 はテーブル全体の col5 の合計を計算します。
   2. my_window_2 は col3 に基づいてパーティション化されます。sum2 は各 col3 パーティション内の col5 の合計を計算します。
   3. my_window_3 は col5 の値でソートされます。sum3 は各行の前（現在の行を含む）の col5 の合計を計算します。
   4. my_window_4はcol3パーティション内で、col5の順序に従い、行を未指定に設定します。sum4は各col3パーティション内でcol5に従ってソートされたcol5の合計を計算します。
   5. my_window_5はcol3パーティション内で、col5の順序に従い、行を無限大に設定します。sum5は各col3パーティション内のcol5の合計を計算します。
   6. my_window_6はcol3パーティション内で、col5の順序に従い、現在の行をそのまま設定します。sum6は各col3パーティション内の現在の行のcol5の合計を計算します。
   7. my_window_7はcol3パーティション内で、col5の順序に従い、現在の行の1行前の行を設定します。sum7は各col3パーティション内の現在の行とその前の行のcol5の合計を計算します。
   8. my_window_8はcol3パーティション内で、col5の順序に従い、前の1行と次の1行を設定します。sum9は各col3パーティション内の最初の1行から最後の1行までのcol5の合計を計算します。
   9. my_window_9はcol3パーティション内で、col4の順序に従い、1年前と1年後の期間を設定します。sum10は各col3パーティション内の1年前から1年後までの期間のcol5の合計を計算します。

   SELECT col1, col3, col4, col5,
         SUM(col5) OVER my_window_1 AS sum1,
         SUM(col5) OVER my_window_2 AS sum2,
         SUM(col5) OVER my_window_3 AS sum3,
         SUM(col5) OVER my_window_4 AS sum4,
         SUM(col5) OVER my_window_5 AS sum5,
         SUM(col5) OVER my_window_6 AS sum6,
         SUM(col5) OVER my_window_7 AS sum7,
         SUM(col5) OVER my_window_8 AS sum8,
         SUM(col5) OVER my_window_9 AS sum9
     FROM test_tbl1
     WINDOW
       my_window_1 AS (),
       my_window_2 AS (PARTITION BY col3),
       my_window_3 AS (ORDER BY col5),
       my_window_4 AS (PARTITION BY col3 ORDER BY col5),
       my_window_5 AS (PARTITION BY col3 ORDER BY col5 ROWS UNBOUNDED PRECEDING),
       my_window_6 AS (PARTITION BY col3 ORDER BY col5 ROWS CURRENT ROW),
       my_window_7 AS (PARTITION BY col3 ORDER BY col5 ROWS 1 PRECEDING),
       my_window_8 AS (PARTITION BY col3 ORDER BY col5 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
       my_window_9 AS (PARTITION BY col3 ORDER BY col4 RANGE BETWEEN INTERVAL 1 YEAR PRECEDING AND INTERVAL 1 YEAR FOLLOWING)
     ORDER BY col1;
   

   実行結果は次のとおりです：

   +------+------+------------+------+------+------+------+------+------+------+------+------+------+
   | col1 | col3 | col4       | col5 | sum1 | sum2 | sum3 | sum4 | sum5 | sum6 | sum7 | sum8 | sum9 |
   +------+------+------------+------+------+------+------+------+------+------+------+------+------+
   |    1 | A1   | 2017-01-01 |   10 |  780 |  100 |   10 |   10 |   10 |   10 |   10 |   30 |   30 |
   |    2 | A1   | 2018-01-01 |   20 |  780 |  100 |   30 |   30 |   30 |   20 |   30 |   60 |   60 |
   |    3 | A1   | 2019-01-01 |   30 |  780 |  100 |   60 |   60 |   60 |   30 |   50 |   90 |   90 |
   |    4 | A1   | 2020-01-01 |   40 |  780 |  100 |  100 |  100 |  100 |   40 |   70 |   70 |   70 |
   |    5 | B1   | 2021-01-01 |   50 |  780 |  260 |  150 |   50 |   50 |   50 |   50 |  110 |  110 |
   |    6 | B1   | 2022-01-01 |   60 |  780 |  260 |  210 |  110 |  110 |   60 |  110 |  180 |  110 |
   |    7 | B1   | 2017-01-01 |   70 |  780 |  260 |  280 |  180 |  180 |   70 |  130 |  210 |  150 |
   |    8 | B1   | 2018-01-01 |   80 |  780 |  260 |  360 |  260 |  260 |   80 |  150 |  150 |  150 |
   |    9 | C1   | 2019-01-01 |   90 |  780 |  420 |  450 |   90 |   90 |   90 |   90 |  190 |  190 |
   |   10 | C1   | 2020-01-01 |  100 |  780 |  420 |  550 |  190 |  190 |  100 |  190 |  300 |  300 |
   |   11 | C1   | 2021-01-01 |  110 |  780 |  420 |  660 |  300 |  300 |  110 |  210 |  330 |  330 |
   |   12 | C1   | 2022-01-01 |  120 |  780 |  420 |  780 |  420 |  420 |  120 |  230 |  230 |  230 |
   +------+------+------------+------+------+------+------+------+------+------+------+------+------+
   12 rows in set
   

関連ドキュメント

SELECT