集約関数コンビネータ

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

接尾辞 -If は、任意の集約関数名の末尾に追加できます。この場合、集約関数は追加の引数として条件 (Uint8 型) を受け取ります。集約関数が処理するのは、その条件を満たす行だけです。条件が一度も満たされなかった場合は、デフォルト値 (通常は 0 または空文字列) を返します。

例: sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) などです。

条件付き集約関数を使うと、サブクエリや JOIN を使わずに、複数の条件に対する集約を一度に計算できます。たとえば、条件付き集約関数はセグメント比較機能の実装に使用できます。

-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 型になります。

例

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;

┌

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

構文

<aggFunction>SimpleState(x)

引数

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

戻り値

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

例

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

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

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

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

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

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

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

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

-Tuple 接尾辞は、任意の集約関数に追加できます。結合後の関数は、元の集約関数の各引数に対応して、Tuple 型の引数を1つ受け取ります。すべてのタプルは、同じ数の要素を持っている必要があります。集約は各要素の位置ごとに独立して適用され、各 Tuple から対応する要素を受け取り、結果として Tuple を返します。

最初の入力 Tuple に明示的な要素名がある場合、それらは結果でも保持されます。

NULL 値を自前で処理する集約関数 (anyRespectNulls、anyLastRespectNulls、RESPECT NULLS 修飾子) は、引数として Nullable(Tuple(...)) 型をサポートしていません。代わりに、要素を Nullable にしてください。

構文

<aggFunction>Tuple(tuple1[, tuple2, ...])

引数

• tuple1[, tuple2, ...] — Tuple 型のカラム。基になる集約関数の各引数に対応して 1 つずつ指定し、すべて同じ数の要素を持っている必要があります。各要素は、それぞれの引数位置で基になる集約関数がサポートする型でなければなりません。

戻り値

• 各要素に対して個別に集約関数を適用した結果を含む Tuple。

型: Tuple(aggFunction(element1), aggFunction(element2), ...).

例

クエリ:

SELECT sumTuple(t) FROM
(
    SELECT tuple(toInt64(1), toFloat64(2.5)) AS t
    UNION ALL
    SELECT tuple(toInt64(3), toFloat64(4.5))
    UNION ALL
    SELECT tuple(toInt64(5), toFloat64(6.5))
);

結果:

┌─sumTuple(t)─┐
│ (9,13.5)    │
└─────────────┘

GROUP BY と併用する場合:

SELECT
    k,
    avgTuple(t)
FROM
(
    SELECT
        number % 2 AS k,
        tuple(toInt64(number), toFloat64(number) * 1.5) AS t
    FROM numbers(6)
)
GROUP BY k
ORDER BY k;

┌─k─┬─avgTuple(t)─┐
│ 0 │ (2,3)       │
│ 1 │ (3,4.5)     │
└───┴─────────────┘

複数の引数を取る集約関数と併用する場合、各 Tuple は基になる関数に1つの引数を渡し、elements は位置ごとに対応付けられます。

corrTuple((a1, a2), (b1, b2)) = (corr(a1, b1), corr(a2, b2))

SELECT corrTuple((a1, a2), (b1, b2))
FROM
(
    SELECT
        toFloat64(number) AS a1,
        toFloat64(number * 2) AS a2,
        toFloat64(100 - number) AS b1,
        toFloat64(number * 3) AS b2
    FROM numbers(10)
);

┌─corrTuple((a1, a2), (b1, b2))─┐
│ (-1,1)                        │
└───────────────────────────────┘

a1 と b1 は逆相関しており、a2 と b2 は比例しているため、結果は (-1, 1) になります。

-Tuple は、-If などの他の集約関数コンビネータと組み合わせることができます。例: sumTupleIf(tuple_column, cond)。

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

集約関数の動作を変更します。

集約関数に入力値がない場合、このコンビネータを使用すると、戻り値のデータ型に対応するデフォルト値を返します。これは、空の入力データを受け取れる集約関数に適用されます。

-OrDefault はほかのコンビネータと併用できます。

構文

<aggFunction>OrDefault(x)

引数

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

戻り値

集約する対象がない場合は、集約関数の戻り値型のデフォルト値を返します。

型は、使用する集約関数によって異なります。

例

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

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

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

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

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

集約関数の動作を変更します。

このコンビネータは、集約関数の結果を Nullable データ型に変換します。集約関数に計算する値がない場合は、NULL を返します。

-OrNull は他のコンビネータと組み合わせて使用できます。

構文

<aggFunction>OrNull(x)

引数

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

戻り値

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

型: Nullable(aggregate function return type)。

例

集約関数の末尾に -orNull を追加します。

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

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

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

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

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

データをグループに分け、それぞれのグループごとに個別に集約できます。グループは、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 は、任意の集約関数名の末尾に追加できます。この場合、集約関数は追加の引数を受け取り、その引数には比較可能な任意の式を指定する必要があります。集約関数は、指定された追加の式に対して最小値を持つ行だけを処理します。

例: sumArgMin(column, expr), countArgMin(expr), avgArgMin(x, expr) などです。

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

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