Set up docs MCPGROUP BY 句は、SELECT クエリを集約モードに切り替えます。動作は次のとおりです。
GROUP BY句には、式のリスト (または単一の式。この場合は長さ 1 のリストと見なされます) が含まれます。このリストは "グループ化キー"として機能し、個々の式はそれぞれ "キー式"と呼ばれます。- SELECT、HAVING、および ORDER BY 句内のすべての式は、キー式に基づいて計算されるか、非キー式 (通常のカラムを含む) に対する 集約関数 に基づいて計算される必要があります。言い換えると、テーブルから選択される各カラムは、キー式で使用するか、集約関数の内部で使用するかのいずれかでなければならず、両方で使用することはできません。
SELECTクエリを集約した結果には、ソーステーブル内の "グループ化キー"の一意な値ごとに 1 行が含まれます。通常、これにより行数は大幅に減少し、多くの場合は桁違いに少なくなりますが、必ずしもそうとは限りません。すべての "グループ化キー"の値が相互に異なっている場合、行数は変わりません。
カラム名ではなくカラム番号でテーブル内のデータをグループ化したい場合は、設定 enable_positional_arguments を有効にします。
NULL の扱い
グループ化では、ClickHouse は NULL を 1 つの値として解釈し、NULL==NULL とみなします。これは、他のほとんどの文脈における NULL の扱いとは異なります。
これが何を意味するのかを示す例を見てみましょう。
次のテーブルがあるとします。
┌─x─┬────y─┐
│ 1 │ 2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │ 2 │
│ 3 │ 3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘クエリ SELECT sum(x), y FROM t_null_big GROUP BY y の結果は次のとおりです。
┌─sum(x)─┬────y─┐
│ 4 │ 2 │
│ 3 │ 3 │
│ 5 │ ᴺᵁᴸᴸ │
└────────┴──────┘y = NULL に対する GROUP BY では、NULL を実際の値であるかのように扱って x が合計されていることがわかります。
GROUP BY に複数のキーを渡すと、NULL を特定の値であるかのように扱って、選択した値のすべての組み合わせが結果として返されます。
ロールアップ修飾子
ROLLUP 修飾子は、GROUP BY リスト内の順序に基づいてキー式の小計を計算するために使用されます。小計の行は、結果テーブルの後に追加されます。
小計は逆順で計算されます。まずリスト内の最後のキー式の小計が計算され、次にその前のキー式、というように、最初のキー式まで順に計算されます。
小計の行では、すでに "grouped" されているキー式の値は 0 または空文字列に設定されます。
例
テーブル t を考えます:
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴───────┴─────┘SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day);GROUP BY 句には 3 つのキー式があるため、結果には右から左へ「ロールアップ」された小計を含む 4 つのテーブルが含まれます。
GROUP BY year, month, day;GROUP BY year, month(dayカラムは 0 で埋められます) ;GROUP BY year(この場合、monthとdayのカラムはどちらも 0 で埋められます) ;- および totals (3 つのキー式のカラムはすべて 0 になります) 。
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴───────┴─────┴─────────┘同じクエリは、WITH キーワードを使って記述することもできます。
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;関連項目
- SQL標準との互換性については、group_by_use_nulls 設定を参照してください。
CUBE 修飾子
CUBE 修飾子は、GROUP BY リスト内のキー式のあらゆる組み合わせに対する小計を計算するために使用されます。小計の行は、結果テーブルの後に追加されます。
小計の行では、"グループ化された"すべてのキー式の値が 0 または空文字列に設定されます。
例
テーブル t について考えます:
┌─year─┬─month─┬─day─┐
│ 2019 │ 1 │ 5 │
│ 2019 │ 1 │ 15 │
│ 2020 │ 1 │ 5 │
│ 2020 │ 1 │ 15 │
│ 2020 │ 10 │ 5 │
│ 2020 │ 10 │ 15 │
└──────┴───────┴─────┘SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);GROUP BY 句には 3 つのキー式があるため、結果には、キー式のすべての組み合わせに対する小計を含む 8 つのテーブルが含まれます。
GROUP BY year, month, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- および totals。
GROUP BY に含まれないカラムは、ゼロで埋められます。
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 10 │ 15 │ 1 │
│ 2020 │ 1 │ 5 │ 1 │
│ 2019 │ 1 │ 5 │ 1 │
│ 2020 │ 1 │ 15 │ 1 │
│ 2019 │ 1 │ 15 │ 1 │
│ 2020 │ 10 │ 5 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 1 │ 0 │ 2 │
│ 2020 │ 1 │ 0 │ 2 │
│ 2020 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │ 0 │ 5 │ 2 │
│ 2019 │ 0 │ 5 │ 1 │
│ 2020 │ 0 │ 15 │ 2 │
│ 2019 │ 0 │ 15 │ 1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │ 0 │ 0 │ 2 │
│ 2020 │ 0 │ 0 │ 4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 1 │ 5 │ 2 │
│ 0 │ 10 │ 15 │ 1 │
│ 0 │ 10 │ 5 │ 1 │
│ 0 │ 1 │ 15 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 1 │ 0 │ 4 │
│ 0 │ 10 │ 0 │ 2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 5 │ 3 │
│ 0 │ 0 │ 15 │ 3 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 0 │ 0 │ 0 │ 6 │
└──────┴───────┴─────┴─────────┘同じクエリは、WITHキーワードを使って記述することもできます。
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;関連項目
- SQL 標準との互換性については、group_by_use_nulls 設定を参照してください。
WITH TOTALS 修飾子
WITH TOTALS 修飾子を指定すると、追加でもう 1 行が計算されます。この行では、キーカラムにはデフォルト値 (ゼロまたは空文字列) が入り、集約関数のカラムにはすべての行を対象に計算された値 (「total」値) が入ります。
この追加の行は、JSON*、TabSeparated*、Pretty* フォーマットでのみ、他の行とは別に出力されます。
XMLおよびJSON*フォーマットでは、この行は別個のtotalsフィールドとして出力されます。TabSeparated*、CSV*、Verticalフォーマットでは、この行はメインの結果の後に、空行を 1 行挟んで出力されます (他のデータの後) 。Pretty*フォーマットでは、この行はメインの結果の後に別のテーブルとして出力されます。Templateフォーマットでは、この行は指定されたテンプレートに従って出力されます。- その他のフォーマットでは利用できません。
HAVING がある場合、WITH TOTALS の処理方法は複数あります。動作は totals_mode 設定に依存します。
totals の処理設定
デフォルトでは、totals_mode = 'before_having' です。この場合、HAVING と max_rows_to_group_by を通過しないものも含め、すべての行に対して 'totals' が計算されます。
その他の選択肢では、'totals' には HAVING を通過した行だけが含まれ、max_rows_to_group_by および group_by_overflow_mode = 'any' の設定に対する動作も異なります。
after_having_exclusive – max_rows_to_group_by を通過しなかった行は含めません。言い換えると、max_rows_to_group_by を省略した場合と比べて、'totals' の行数はそれ以下または同じになります。
after_having_inclusive – max_rows_to_group_by を通過しなかったすべての行を 'totals' に含めます。言い換えると、max_rows_to_group_by を省略した場合と比べて、'totals' の行数はそれ以上または同じになります。
after_having_auto – HAVING を通過した行数を数えます。それが一定の割合 (デフォルトでは 50%) を超える場合は、max_rows_to_group_by を通過しなかったすべての行を 'totals' に含めます。そうでない場合は含めません。
totals_auto_threshold – デフォルト値は 0.5 です。after_having_auto の係数です。
max_rows_to_group_by と group_by_overflow_mode = 'any' を使用しない場合、after_having の各バリエーションはすべて同じなので、どれを使っても構いません (たとえば after_having_auto) 。
WITH TOTALS は、JOIN 句内のサブクエリを含むサブクエリでも使用できます (この場合、対応する totals の値が結合されます) 。
GROUP BY ALL
GROUP BY ALL は、集約関数ではない SELECT 対象のすべての式を列挙するのと同じです。
たとえば:
SELECT
a * 2,
b,
count(c),
FROM t
GROUP BY ALLと同じです
SELECT
a * 2,
b,
count(c),
FROM t
GROUP BY a * 2, b特別なケースとして、ある関数の引数に集約関数とその他のフィールドの両方が含まれている場合、GROUP BY キーには、その関数から抽出できる非集約フィールドの最大集合が含まれます。
例えば:
SELECT
substring(a, 4, 2),
substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY ALLと同じです
SELECT
substring(a, 4, 2),
substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY substring(a, 4, 2), substring(a, 1, 2)例
例:
SELECT
count(),
median(FetchTiming > 60 ? 60 : FetchTiming),
count() - sum(Refresh)
FROM hitsMySQLとは異なり (標準SQLに準拠しているため) 、キーや集約関数に含まれていないカラムの値は取得できません (定数式を除く) 。これを回避するには、'any' 集約関数 (最初に見つかった値を取得) または 'min/max' を使用できます。
例:
SELECT
domainWithoutWWW(URL) AS domain,
count(),
any(Title) AS title -- 各ドメインで最初に出現したページヘッダーを取得する。
FROM hits
GROUP BY domain異なるキー値ごとに、GROUP BY は一連の集約関数の値を計算します。
GROUPING SETS 修飾子
これは最も一般的な修飾子です。 この修飾子では、複数の集約キーのセット (grouping sets) を手動で指定できます。 集約は各 grouping set ごとに個別に実行され、その後、すべての結果が結合されます。 あるカラムが grouping set に含まれていない場合、そのカラムはデフォルト値で補われます。
つまり、上述の修飾子は GROUPING SETS で表現できます。
ROLLUP、CUBE、GROUPING SETS 修飾子を使ったクエリは構文上は同等ですが、実行時の動作が異なる場合があります。
GROUPING SETS はすべてを並列に実行しようとするのに対し、ROLLUP と CUBE は集約結果の最終マージを単一スレッドで実行します。
ソースカラムにデフォルト値が含まれている場合、その行がそれらのカラムをキーとして使用する集約の一部なのかどうかを判別しにくいことがあります。
この問題を解決するには、GROUPING 関数を使用する必要があります。
例
次の 2 つのクエリは同等です。
-- Query 1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;
-- Query 2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
(year, month, day),
(year, month),
(year),
()
);関連項目
- SQL 標準との互換性を確保するための group_by_use_nulls 設定。
実装の詳細
集約は列指向DBMSの最も重要な機能の1つであり、その実装は ClickHouse の中でも特に重点的に最適化されている部分の1つです。デフォルトでは、集約はハッシュテーブルを用いてメモリ内で実行されます。これには40種類以上の特殊化があり、「グループ化キー」のデータ型に応じて自動的に選択されます。
テーブルのソートキーに応じた GROUP BY の最適化
テーブルが何らかのキーでソートされており、GROUP BY 式にソートキーの少なくともプレフィックス、または単射関数が含まれている場合、集約をより効率的に実行できます。この場合、テーブルから新しいキーを読み込むと、集約の中間結果を確定してクライアントに送信できます。この動作は optimize_aggregation_in_order 設定で有効になります。この最適化により、集約中のメモリ使用量は削減されますが、場合によってはクエリの実行が遅くなることがあります。
外部メモリでの GROUP BY
GROUP BY 実行時のメモリ使用量を抑えるために、一時データをディスクにダンプするよう有効化できます。
max_bytes_before_external_group_by 設定は、GROUP BY の一時データをファイルシステムにダンプする RAM 使用量のしきい値を決定します。0 (デフォルト) に設定すると無効になります。
あるいは、max_bytes_ratio_before_external_group_by を設定することもできます。これにより、クエリのメモリ使用量が一定のしきい値に達した場合にのみ、外部メモリでの GROUP BY を使用できます。
max_bytes_before_external_group_by を使用する場合は、max_memory_usage をその約 2 倍に設定することを推奨します (または max_bytes_ratio_before_external_group_by=0.5) 。これは、集約には 2 つの段階があるためです。1 つ目はデータの読み込みと中間データの生成、2 つ目は中間データのマージです。ファイルシステムへのデータのダンプが発生するのは、段階 1 の間だけです。一時データがダンプされなかった場合、段階 2 では段階 1 と同程度のメモリが必要になる可能性があります。
たとえば、max_memory_usage を 10000000000 に設定し、外部集約を使用したい場合は、max_bytes_before_external_group_by を 10000000000、max_memory_usage を 20000000000 に設定するのが合理的です。外部集約がトリガーされた場合 (一時データのダンプが少なくとも 1 回発生していれば) 、RAM の最大消費量は max_bytes_before_external_group_by をわずかに上回る程度にとどまります。
分散クエリ処理では、外部集約はリモートサーバーで実行されます。リクエスト元のサーバーが使用する RAM を少量に抑えるには、distributed_aggregation_memory_efficient を 1 に設定します。
ディスクに書き出されたデータをマージする場合や、distributed_aggregation_memory_efficient 設定が有効なときにリモートサーバーからの結果をマージする場合に消費される RAM は、合計 RAM 量のうち最大で 1/256 * the_number_of_threads です。
外部集約が有効でも、データ量が max_bytes_before_external_group_by 未満であれば (つまりデータがディスクに書き出されなければ) 、クエリは外部集約を使わない場合と同じ速度で実行されます。一時データが書き出された場合、実行時間は数倍長くなります (およそ 3 倍) 。
GROUP BY の後に LIMIT 付きの ORDER BY がある場合、使用される RAM 量はテーブル全体ではなく LIMIT のデータ量に依存します。ただし、ORDER BY に LIMIT がない場合は、外部ソート (max_bytes_before_external_sort) を有効にすることを忘れないでください。