メインコンテンツまでスキップ
メインコンテンツまでスキップ

GROUP BY句

GROUP BY句は、SELECTクエリを集計モードに切り替え、以下のように動作します：

  • GROUP BY句には、式のリスト（または長さ1のリストと見なされる単一の式）が含まれます。このリストは「グルーピングキー」として機能し、各個別の式は「キー式」と呼ばれます。
  • SELECT、HAVING、およびORDER BY句内のすべての式は、キー式に基づいて計算されるか、非キー式に対して集計関数に基づいて計算される必要があります（平凡なカラムを含む）。言い換えれば、テーブルから選択された各カラムは、キー式のいずれか、あるいは集計関数の中で使用されなければなりませんが、その両方ではありません。
  • SELECTクエリの集計結果は、ソーステーブル内の「グルーピングキー」のユニークな値の数と同じだけの行を含みます。通常、これは行の数を大幅に減少させますが、必ずしもそうではありません：すべての「グルーピングキー」値が異なる場合、行の数は同じままです。

テーブル内のデータをカラム名の代わりにカラム番号でグループ化したい場合は、設定enable_positional_argumentsを有効にします。

注記

テーブルに対して集計を実行する追加の方法があります。クエリが集計関数内のテーブルカラムのみを含む場合、GROUP BY句は省略でき、キーの空集合に基づく集計が仮定されます。このようなクエリは常に正確に1行を返します。

NULL処理​

グループ化のために、ClickHouseはNULLを値として解釈し、NULL==NULLと見なします。これは、他のほとんどの文脈でのNULL処理とは異なります。

これが何を意味するのかを示す例があります。

次のようなテーブルがあると仮定します：

クエリSELECT sum(x), y FROM t_null_big GROUP BY yの結果は次のようになります：

y = NULLの場合のGROUP BYが、あたかもNULLがこの値であるかのようにxを合計したことがわかります。

複数のキーをGROUP BYに渡すと、結果は選択のすべての組み合わせを返します。あたかもNULLが特定の値であるかのように。

ROLLUP修飾子​

ROLLUP修飾子は、GROUP BYリスト内のキー式の順序に基づいて小計を計算するために使用されます。小計行は結果テーブルの後に追加されます。

小計は逆の順序で計算されます：最初にリストの最後のキー式の小計が計算され、その後、前のキー式、そして最初のキー式まで遡ります。

小計行では、すでに「グループ化された」キー式の値が0または空行に設定されます。

注記

HAVING句は小計結果に影響を与える可能性があることに注意してください。

例

テーブルtを考えます：

クエリ：

GROUP BYセクションに3つのキー式があるため、結果は右から左に「ロールアップ」された小計を含む4つのテーブルを含みます：

  • GROUP BY year, month, day;
  • GROUP BY year, month（dayカラムは0で埋められます）；
  • GROUP BY year（この時点でmonthとdayのカラムは両方とも0で埋められます）；
  • そしてトータル（すべての三つのキー式カラムが0です）。

同じクエリは、WITHキーワードを使用して書くこともできます。

参照も

CUBE修飾子​

CUBE修飾子は、GROUP BYリスト内のすべてのキー式の組み合わせに対して小計を計算するために使用されます。小計行は結果テーブルの後に追加されます。

小計行では、すべての「グループ化された」キー式の値が0または空行に設定されます。

注記

HAVING句は小計結果に影響を与える可能性があることに注意してください。

例

テーブルtを考えます：

クエリ：

GROUP BYセクションに3つのキー式があるため、結果はすべてのキー式の組み合わせに対する小計を含む8つのテーブルを含みます：

  • GROUP BY year, month, day
  • GROUP BY year, month
  • GROUP BY year, day
  • GROUP BY year
  • GROUP BY month, day
  • GROUP BY month
  • GROUP BY day
  • そしてトータル。

GROUP BYから除外されたカラムは0で埋められます。

同じクエリは、WITHキーワードを使って書くこともできます。

参照も

WITH TOTALS修飾子​

WITH TOTALS修飾子が指定されると、別の行が計算されます。この行は、デフォルト値（ゼロまたは空行）を含むキー列と、すべての行に対して計算された集計関数の列（「合計」値）を持ちます。

この追加の行は、JSON*、TabSeparated*、およびPretty*フォーマットで、他の行とは別に生成されます：

  • XMLおよびJSON*フォーマットでは、この行は別の「合計」フィールドとして出力されます。
  • TabSeparated*、CSV*およびVerticalフォーマットでは、行は主要結果の後に、空行の前に来る。
  • Pretty*フォーマットでは、この行は主要結果の後に別のテーブルとして出力されます。
  • Templateフォーマットでは、指定されたテンプレートに従って出力されます。
  • 他のフォーマットでは利用できません。
注記

合計はSELECTクエリの結果に出力され、INSERT INTO ... SELECTでは出力されません。

WITH TOTALSは、HAVINGが存在する場合、異なる方法で実行できます。動作はtotals_mode設定に依存します。

合計処理の設定​

デフォルトでは、totals_mode = 'before_having'です。この場合、「合計」は、HAVINGを通過しない行を含め、すべての行に対して計算されます。

他の代替手段には、max_rows_to_group_byの設定によって異なる動作をするため、HAVINGを通過する行のみを「合計」に含めるものが含まれます。

after_having_exclusive - max_rows_to_group_byを通過しなかった行を含めません。言い換えれば、「合計」は、max_rows_to_group_byが省略された場合と同じか、それより少ない行数になります。

after_having_inclusive - max_rows_to_group_byを通過しなかったすべての行を「合計」に含めます。言い換えれば、「合計」は、max_rows_to_group_byが省略された場合と同じか、それより多い行数になります。

after_having_auto - HAVINGを通過した行の数をカウントします。それが一定の量（デフォルトでは50％以上）である場合、「合計」には、max_rows_to_group_byを通過しなかったすべての行が含まれます。そうでなければ、含まれません。

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句のサブクエリを含みます（この場合、該当する合計値が結合されます）。

GROUP BY ALL​

GROUP BY ALLは、集計関数でないすべてのSELECTされた式をリストアップすることと等価です。

例えば：

これは次のように同じです：

集計関数と他のフィールドの両方を引数に持つ関数がある特別なケースの場合、GROUP BYのキーには、そこから抽出できる最大の非集計フィールドが含まれます。

例えば：

これは次のように同じです：

例​

例：

MySQLとは異なり（SQL標準に準拠）、キーや集計関数に含まれていないカラムの値を取得することはできません（定数式を除く）。これを回避するために、最初に遭遇した値を取得するために、'any'集計関数または'min/max'を使用できます。

例：

異なるキー値が見つかるたびに、GROUP BYは一連の集計関数値を計算します。

GROUPING SETS修飾子​

これは最も一般的な修飾子です。この修飾子は、いくつかの集計キーセット（グルーピングセット）を手動で指定できるようにします。 集計は各グルーピングセットごとに個別に行われ、その後、すべての結果が結合されます。 カラムがグルーピングセットに存在しない場合、デフォルト値で埋められます。

言い換えれば、上記で説明した修飾子はGROUPING SETSを介して表現できます。 ROLLUP、CUBEおよびGROUPING SETS修飾子を持つクエリは、文法的には同じですが、異なる動作をする場合があります。 GROUPING SETSがすべてを並行して実行しようとするのに対し、ROLLUPおよびCUBEは集計の最終的なマージをシングルスレッドで実行します。

ソースカラムにデフォルト値が含まれている場合、その行がそれらのカラムをキーとして使用する集計の一部であるかどうかを区別するのが難しい場合があります。 この問題を解決するためにGROUPING関数を使用しなければなりません。

例

次の2つのクエリは同等です。

参照も

実装の詳細​

集計は列指向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）に設定することをお勧めします。これは、集計にはデータの読み取りと中間データの形成（1）および中間データのマージ（2）の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の最大1/256 * スレッド数が消費されます。

外部集計が有効な場合、もし max_bytes_before_external_group_by より少ないデータ（つまり、データがフラッシュされなかった）の場合、クエリは外部集計なしで実行したときと同じように速く実行されます。もし一時データがダンプされた場合、実行時間は数倍長く（約3倍）なります。

GROUP BYの後にORDER BYとLIMITがある場合、使用されるRAMの量はLIMIT内のデータ量に依存し、全テーブルのものではなくなります。しかし、ORDER BYがLIMITを持たない場合は、外部ソートを有効にすることを忘れないでください（max_bytes_before_external_sort）。