ウィンドウ句

説明

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