バージョン: 26.x

概要

説明

Window functions（分析関数とも呼ばれます）は、元の行を保持しながら計算を実行する特別な組み込み関数です。集約関数とは異なり、window functions は以下の特徴があります：

GROUP BY グループ化ではなく、特定のwindow範囲内でデータを処理します
結果セット内の各行に対して値を計算します
SELECT リストに追加の列を加えることができます
クエリ処理の最後に実行されます（JOIN、WHERE、GROUP BY の後）

Window functions は、トレンド分析、外れ値計算、データバケット化において、金融および科学計算で一般的に使用されます。

構文

<FUNCTION> ( [ <ARGUMENTS> ] ) OVER ( [ <windowDefinition> ] )

そして:

windowDefinition ::=

[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]

そして：

windowFrameClause ::=
{
  | { ROWS } <n> PRECEDING
  | { ROWS } CURRENT ROW
  | { ROWS } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } UNBOUNDED PRECEDING
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  | { ROWS } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
  | { ROWS } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
  | { ROWS } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}

パラメータ

<FUNCTION>

ウィンドウ関数の名前。すべての集約関数と特別なウィンドウ関数を含みます：DENSE_RANK()、FIRST_VALUE()、LAG()、LAST_VALUE()、LEAD()、RANK()、ROW_NUMBER()、NTH_VALUE()、PERCENT_RANK()、CUME_DIST()、NTILE()。

<ARGUMENTS>

オプション。ウィンドウ関数の入力引数。引数の型と数量は、使用される特定の関数に依存します。

<PARTITION_BY>

オプション。GROUP BYと同様に、指定された列でデータをグループ化し、各パーティション内で計算を実行します。

<ORDER_BY>

オプション。各パーティション内でデータをソートするために使用されます。パーティションが指定されていない場合、データセット全体をソートします。ただし、このORDER BYは、SQL文の最後に出現する一般的なORDER BYとは異なります。OVER句で指定されたソートは、そのパーティション内のデータにのみ適用されますが、SQL文の最後のORDER BYは、最終的なクエリ結果のすべての行の順序を制御します。この2つは共存できます。さらに、OVER句でORDER BYが明示的に指定されていない場合、パーティション内のデータはランダムになる可能性があり、予測不可能な最終結果につながる可能性があります。ソート列が明示的に提供されていても重複値が含まれている場合、結果は依然として不安定である可能性があります。具体的な例については、以下のケーススタディを参照してください。

<windowFrameClause>

オプション。ウィンドウフレームを定義するために使用されます。現在、2つのタイプがサポートされています：RANGEとROWS。 N PRECEDING/FOLLOWINGについて、Nは正の整数で、現在の行に対する相対的なスライディングウィンドウの範囲を表します。現在、これはROWSウィンドウでのみサポートされているため、現在の行に対する物理的なオフセットを示します。RANGEタイプには現在いくつかの制限があります：BOTH UNBOUNDED BOUNDARYまたはONE UNBOUNDED BOUNDARY AND ONE CURRENT ROWのいずれかである必要があります。フレームが指定されていない場合、デフォルトの暗黙的なフレームはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWです。

戻り値

入力式と同じデータ型を返します。

分析関数データの一意な順序付け

1. 戻り結果の不整合の問題

ウィンドウ関数のORDER BY句がデータの一意な順序付けを生成できない場合、例えばORDER BY式が重複値を生じる場合、行の順序が不確定になります。これは、これらの行の戻り順序が複数のクエリ実行間で変わる可能性があることを意味し、ウィンドウ関数から一貫性のない結果をもたらします。

以下の例は、クエリが連続した実行で異なる結果を返す方法を示しています。この不整合は主に、ORDER BY dateidがSUMウィンドウ関数に対して一意な順序付けを提供しないことから生じます。

CREATE TABLE test_window_order 
    (item_id int,
    date_time date,
    sales double)
distributed BY hash(item_id)
properties("replication_num" = 1);

INSERT INTO test_window_order VALUES
(1, '2024-07-01', 100),
(2, '2024-07-01', 100),
(3, '2024-07-01', 140);

SELECT
    item_id, date_time, sales,
    sum(sales) OVER (ORDER BY date_time ROWS BETWEEN 
        UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM
    test_window_order;

ソート列date_timeに重複する値があるため、以下の2つのクエリ結果が観測される可能性があります：

+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       1 | 2024-07-01 |   100 |  100 |
|       3 | 2024-07-01 |   140 |  240 |
|       2 | 2024-07-01 |   100 |  340 |
+---------+------------+-------+------+
3 rows in set (0.03 sec)

2. Solution

この問題に対処するには、item_idなどの一意な値の列をORDER BY句に追加して、順序の一意性を確保することができます。

SELECT
        item_id,
        date_time,
        sales,
        sum(sales) OVER (
        ORDER BY item_id,
        date_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM
        test_window_order;

これにより、一貫したクエリ出力が得られます：

+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       1 | 2024-07-01 |   100 |  100 |
|       2 | 2024-07-01 |   100 |  200 |
|       3 | 2024-07-01 |   140 |  340 |
+---------+------------+-------+------+
3 rows in set (0.03 sec)

説明​

構文​

パラメータ​

戻り値​

分析関数データの一意な順序付け​

説明

構文

パラメータ

戻り値

分析関数データの一意な順序付け