カテゴリ：: 集計関数（一般）

ACCUMULATE¶

4つのユーザー定義の SQL ラムダ関数によって計算されたカスタム集計値を返します：初期化、蓄積、結合、終了。 ACCUMULATE はマップの削除集約モデルに従い、 GROUPBY、 HAVING と統合し、サブクエリは組み込み集計は同じ方法で行います。

ACCUMULATE は、組み込み集計関数がカバーしないプロトタイピングや単発の集計に特に役立ちます。パフォーマンスを重視するワークロードでは、可能な限り組み込みの集計を記述するか、組み込みの集計と結合の組み合わせが同じ結果を達成できるかどうかを検討してください。 OBJECT、 ARRAY または VARIANT を使用した ACCUMULATE 状態型には、追加のパフォーマンスオーバーヘッドがかかります。

構文¶

ACCUMULATE(
    <input_expr>,
    <initialize_lambda>,
    <accumulate_lambda>,
    <combine_lambda>,
    <terminate_lambda>
)

引数¶

input_expr: 式が非 NULL のときに1回評価される入力行。結果の値は、入力値として各ラムダに渡されます。
initialize_lambda: 署名 (value) -> <state_expr> 付きラムダ。NULL 以外の入力行に対して1回呼び出され、その行の値から初期の部分状態を生成します。
accumulate_lambda: 署名 (state, value) -> <state_expr> 付きラムダ。新しい入力値を既存の部分的な状態に組み込み、更新された状態を返します。
combine_lambda: 署名 (state1, state2) -> <state_expr> 付きラムダ。並列ワーカーによって生成された2つの部分状態をマージします。関連付けられている必要があります。
terminate_lambda: 署名 (state) -> <output_expr> 付きラムダ。最終的なマージ状態をクエリに返される結果値に変換します。

ラムダ引数名は任意です。型注釈はオプションです。詳細については、型推論をご参照ください。

戻り値¶

terminate_lambda によって生成された値を返します。データ型が terminate_lambda の戻り型と一致する。すべての入力行が NULL の場合、または入力セットが空の場合に NULL を返します。

使用上の注意¶

NULL 値の入力行は、ラムダ式が呼び出される前に警告なしでスキップされます。これは、標準的な SQL 集計動作と一致しています。すべての入力行が NULL または入力セットが空の場合、結果は NULL になります。
ACCUMULATE には永続的な形式がありません。CREATEAGGREGATEFUNCTION または同等の DDL がありません。集計を再利用するには、ビュー、 CTE またはストアドプロシージャでラップします。
ラムダ式は、宣言されたパラメーターのみを参照できます。外部クエリからの列への参照は許可されていません。外部クエリ値を含めるには、入力式に投影するか、 CTE で事前計算します。

次の例では、 column2 はラムダパラメーターではないため、コンパイルエラーが発生します。
```
-- ERROR: column2 is a column reference, not a lambda parameter.
SELECT ACCUMULATE(
    column1,
    (v INT) -> v + column2,
    ...
) FROM t;
```
以下の関数クラスはラムダ内では許可されません。

関数クラス

例

集計関数

SUM、 AVG、 COUNT

ウィンドウ関数

ROW_NUMBER() OVER (...)

非決定性関数

RANDOM()、 UUID_STRING()

関数クラス	例
集計関数	SUM、 AVG、 COUNT
ウィンドウ関数	ROW_NUMBER() OVER (...)
非決定性関数	RANDOM()、 UUID_STRING()

型推論¶

ラムダ引数の型はオプションです。省略すると、型は入力式から推測され、ラムダチェーンを介して伝播されます。

明示的な型はラムダ全体では強制されません。任意のラムダ引数に型に注釈を付けると、そのアノテーションはその位置の状態型に対して承認されます。4つのラムダにまたがる注釈が一貫性がなく、調整できない場合（例：initialize は ARRAY を返しますが accumulate は状態を INT と宣言）、コンパイルに失敗します。
すべての型の注釈を省略したり、一部の引数のみに注釈を付けたりすることができます。コンパイラは自動的に型を推測し、拡大します。明示的な注釈が一貫している限り、注釈付き引数と注釈なし引数の混在が許可されます。
状態タイプ（初期化、累積、結合の間を流れるタイプ）と出力タイプ（終了の戻りタイプ）は独立して追跡され、異なる場合があります。
入力式は、初期化ラムダの value 引数が期待するタイプに暗黙的にキャストされます。例えば、入力列が INT および value が STRING を宣言している場合、キャストは自動的に適用されます。

例¶

列の合計を計算し、既存の SUM(c1) 集計関数と同等の動作をシミュレートします。

SELECT
    ACCUMULATE(
        c1,
        (v INT)                      -> v,
        (state INT, v INT)           -> state + v,
        (state1 INT, state2 INT)     -> state1 + state2,
        (state INT)                  -> state
    ) AS total
FROM t;

グループ内のすべての値の積を計算します。

SELECT
    ACCUMULATE(
        c1,
        (v INT)                      -> v,
        (state INT, v INT)           -> state * v,
        (state1 INT, state2 INT)     -> state1 * state2,
        (state INT)                  -> state
    ) AS total
FROM t;

ARRAY を使用して平均を計算し、実行合計とカウントを追跡します。

SELECT ACCUMULATE(
    c1,
    (v INT)                        -> [v, 1],
    (state ARRAY, v INT)           -> [state[0] + v, state[1] + 1],
    (state1 ARRAY, state2 ARRAY)   -> [state1[0] + state2[0], state1[1] + state2[1]],
    (state ARRAY)                  -> state[0] / state[1]
) AS mean
FROM t;

構造化 OBJECT を状態としてを使用して平均を計算します。

SELECT ACCUMULATE(
    c1,
    (v INT) -> {'sum': v, 'count': 1}::OBJECT(sum INT, count INT),
    (state OBJECT(sum INT, count INT), v) ->
        {'sum': state:sum + v, 'count': state:count + 1}::OBJECT(sum INT, count INT),
    (state1, state2) ->
        {'sum': state1:sum + state2:sum, 'count': state1:count + state2:count}::OBJECT(sum INT, count INT),
    (state) -> state:sum / state:count
) AS mean
FROM t;

列で最小の文字列を見つけます。

SELECT ACCUMULATE(
    c1,
    (v STRING) -> v,
    (state STRING, v STRING) ->
        CASE WHEN LENGTH(v) < LENGTH(state) THEN v ELSE state END,
    (state1 STRING, state2 STRING) ->
        CASE WHEN LENGTH(state1) <= LENGTH(state2) THEN state1 ELSE state2 END,
    (state STRING) -> state
) AS shortest
FROM t;

ACCUMULATE を GROUPBY と使用します。

SELECT
    category,
    ACCUMULATE(
        amount,
        (v INT)                      -> v,
        (state INT, v INT)           -> state + v,
        (state1 INT, state2 INT)     -> state1 + state2,
        (state INT)                  -> state
    ) AS category_total
FROM orders
GROUP BY category;

ラムダ内で UDF を呼び出します。

SELECT ACCUMULATE(
    c1,
    (v NUMBER)       -> v,
    (state, v)       -> my_sum_udf(state, v),
    (state1, state2) -> my_sum_udf(state1, state2),
    (state)          -> state
) AS total
FROM t;