集約関数コンビネータ - ClickHouse Documentation

集約関数の名前には接尾辞を付加できます。これにより、集約関数の動作が変わります。

-If

接尾辞 -If は、任意の集約関数名の末尾に追加できます。この場合、集約関数は追加の引数として条件 (Uint8 型) を受け取ります。集約関数が処理するのは、その条件を満たす行だけです。条件が一度も満たされなかった場合は、デフォルト値 (通常は 0 または空文字列) を返します。例: sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) などです。条件付き集約関数を使うと、サブクエリや JOIN を使わずに、複数の条件に対する集約を一度に計算できます。たとえば、条件付き集約関数はセグメント比較機能の実装に使用できます。

-Array

-Array 接尾辞は、任意の集約関数に追加できます。この場合、集約関数は ‘T’ 型の引数ではなく、‘Array(T)’ 型 (配列) の引数を受け取ります。集約関数が複数の引数を受け取る場合、それらは同じ長さの配列である必要があります。配列を処理する場合、集約関数はすべての配列要素に対して、元の集約関数と同様に動作します。例 1: sumArray(arr) - すべての ‘arr’ 配列の全要素を合計します。この例は、sum(arraySum(arr)) とよりシンプルに書くこともできます。例 2: uniqArray(arr) – すべての ‘arr’ 配列に含まれる重複しない要素数を数えます。これは uniq(arrayJoin(arr)) とすればより簡単に実現できますが、クエリに ‘arrayJoin’ を追加できるとは限りません。 -If と -Array は組み合わせて使用できます。ただし、‘Array’ を先に、その後に ‘If’ を付ける必要があります。例: uniqArrayIf(arr, cond), quantilesTimingArrayIf(level1, level2)(arr, cond)。この順序では、‘cond’ 引数は配列になりません。

-Map

-Map 接尾辞は、任意の集約関数に追加できます。これにより、Map 型を引数として受け取り、指定した集約関数を使って map の各キーの値を個別に集約する集約関数が作成されます。結果も Map 型になります。例

CREATE TABLE map_map(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO map_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10]));

SELECT
    timeslot,
    sumMap(status),
    avgMap(status),
    minMap(status)
FROM map_map
GROUP BY timeslot;

┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐
│ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │
│ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20}        │ {'d':10,'e':10,'f':10,'g':10}        │ {'d':10,'e':10,'f':10,'g':10}        │
└─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘

-SimpleState

このコンビネータを適用すると、集約関数は同じ値を返しますが、型が異なります。これは SimpleAggregateFunction(…) であり、AggregatingMergeTree テーブルで使用できるようにテーブルに格納できます。構文

<aggFunction>SimpleState(x)

引数

x — 集約関数のパラメータ。

戻り値 SimpleAggregateFunction(...) 型の集約関数の値。例

Query

WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);

Response

┌─toTypeName(c)────────────────────────┬─c─┐
│ SimpleAggregateFunction(any, UInt64) │ 0 │
└──────────────────────────────────────┴───┘

-State

このコンビネータを適用すると、集約関数は結果の値 (たとえば uniq 関数における一意な値の数) ではなく、aggregation の中間 state を返します (uniq の場合、これは一意な値の数を計算するための hash table です) 。これは AggregateFunction(...) で、後続の処理に使用したり、あとで aggregation を完了できるように table に保存したりできます。

-MapState は、同じデータに対しても不変ではない点に注意してください。これは、中間 state 内のデータの順序が変わる可能性があるためです。ただし、このデータのインジェストには影響しません。

これらの state を扱うには、次を使用します。

AggregatingMergeTree table engine。
finalizeAggregation 関数。
runningAccumulate 関数。
-Merge コンビネータ。
-MergeState コンビネータ。

-Merge

このコンビネータを適用すると、集約関数は中間的な集約状態を引数として受け取り、それらの状態を結合して集約を完了し、結果の値を返します。

-MergeState

-Merge コンビネータと同じ方法で、中間の集約状態をマージします。ただし、結果の値は返さず、-State コンビネータと同様に中間の集約状態を返します。

-ForEach

テーブル向けの集約関数を、対応する配列要素ごとに集約し、結果を配列で返す配列向けの集約関数に変換します。たとえば、配列 [1, 2]、[3, 4, 5]、[6, 7] に対する sumForEach は、対応する配列要素同士を加算した結果として [10, 13, 5] を返します。

-Distinct

引数の一意な組み合わせごとに、集計は1回だけ行われます。重複する値は無視されます。例: sum(DISTINCT x) (または sumDistinct(x)) 、groupArray(DISTINCT x) (または groupArrayDistinct(x)) 、corrStable(DISTINCT x, y) (または corrStableDistinct(x, y)) などです。

-OrDefault

集約関数の動作を変更します。集約関数に入力値がない場合、このコンビネータを使用すると、戻り値のデータ型に対応するデフォルト値を返します。これは、空の入力データを受け取れる集約関数に適用されます。 -OrDefault はほかのコンビネータと併用できます。構文

<aggFunction>OrDefault(x)

引数

x — 集約関数のパラメータです。

戻り値 集約する対象がない場合は、集約関数の戻り値型のデフォルト値を返します。型は、使用する集約関数によって異なります。例

Query

SELECT avg(number), avgOrDefault(number) FROM numbers(0)

Response

┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘

また、-OrDefault は他のコンビネータと組み合わせて使うこともできます。これは、集約関数が空の入力を受け付けない場合に便利です。

Query

SELECT avgOrDefaultIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Response

┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                              0.00 │
└───────────────────────────────────┘

-OrNull

集約関数の動作を変更します。このコンビネータは、集約関数の結果を Nullable データ型に変換します。集約関数に計算する値がない場合は、NULL を返します。 -OrNull は他のコンビネータと組み合わせて使用できます。構文

<aggFunction>OrNull(x)

引数

x — 集約関数のパラメータ。

戻り値

Nullable データ型に変換された集約関数の結果。
集約する値がない場合は NULL。

型: Nullable(aggregate function return type)。例集約関数の末尾に -orNull を追加します。

Query

SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10

Response

┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘

また、-OrNull はほかのコンビネータと組み合わせて使用することもできます。これは、aggregate function が空の入力を受け付けない場合に便利です。

Query

SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Response

┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

-Resample

データをグループに分け、それぞれのグループごとに個別に集約できます。グループは、1つのカラムの値をインターバルごとに分割して作成されます。

<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)

引数

start — resampling_key の値に対して必要な全インターバルの開始値。
stop — resampling_key の値に対して必要な全インターバルの終了値。全インターバルには stop の値は含まれません [start, stop)。
step — 全インターバルを部分インターバルに分割する際のステップ。aggFunction は各部分インターバルに対して独立して実行されます。
resampling_key — その値を使用してデータをインターバルに分割するカラム。
aggFunction_params — aggFunction のパラメーター。

戻り値

各部分インターバルに対する aggFunction の結果の Array。

例次のデータを持つ people テーブルについて考えます。

┌─name───┬─age─┬─wage─┐
│ John   │  16 │   10 │
│ Alice  │  30 │   15 │
│ Mary   │  35 │    8 │
│ Evelyn │  48 │ 11.5 │
│ David  │  62 │  9.9 │
│ Brian  │  60 │   16 │
└────────┴─────┴──────┘

[30,60) と [60,75) の区間に入る年齢の人の名前を取得してみましょう。年齢は整数で表しているため、対象となる年齢は [30, 59] および [60,74] の区間になります。名前を配列に集約するには、groupArray 集約関数を使用します。この関数は 1 つの引数を取ります。今回の場合は name カラムです。groupArrayResample 関数では、年齢ごとに名前を集約するために age カラムを使用する必要があります。必要な区間を定義するには、30, 75, 30 という引数を groupArrayResample 関数に渡します。

SELECT groupArrayResample(30, 75, 30)(name, age) FROM people

┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────┘

結果を見てみましょう。 John は若すぎるため、サンプルには含まれません。ほかの人たちは、指定した年齢インターバルに応じて振り分けられます。それでは、指定した年齢インターバルごとの総人数と平均賃金を求めてみましょう。

SELECT
    countResample(30, 75, 30)(name, age) AS amount,
    avgResample(30, 75, 30)(wage, age) AS avg_wage
FROM people

┌─amount─┬─avg_wage──────────────────┐
│ [3,2]  │ [11.5,12.949999809265137] │
└────────┴───────────────────────────┘

-ArgMin

接尾辞 -ArgMin は、任意の集約関数名の末尾に追加できます。この場合、集約関数は追加の引数を受け取り、その引数には比較可能な任意の式を指定する必要があります。集約関数は、指定された追加の式に対して最小値を持つ行だけを処理します。例: sumArgMin(column, expr), countArgMin(expr), avgArgMin(x, expr) などです。

-ArgMax

接尾辞 -ArgMin に似ていますが、指定した追加の式で最大値を持つ行のみを処理します。

ブログ: ClickHouseで集約コンビネータを使う

​-If

​-Array

​-Map

​-SimpleState

​-State

​-Merge

​-MergeState

​-ForEach

​-Distinct

​-OrDefault

​-OrNull

​-Resample

​-ArgMin

​-ArgMax

​関連コンテンツ

-If

-Array

-Map

-SimpleState

-State

-Merge

-MergeState

-ForEach

-Distinct

-OrDefault

-OrNull

-Resample

-ArgMin

-ArgMax

関連コンテンツ