SQLのGROUP BYの組み合わせに全てをいれる

Question

###前提・実現したいこと
SQL のGROUP BYで複数カラムを指定したときにそのカラムの項目の組み合わせすべてで集計できますが、その組み合わせに全ての集計結果を含めたいです。
わかりづらいので例をあげます。

clumn1の項目としてA,Bがあって、
clumn2の項目としてC,Dがあるとします。

SELECT
  clumn1,
  clumn2,
  COUNT(*) cnt
FROM
  table1 t 
GROUP BY
  clumn1,
  clumn2

このSQLだとclumn1とclumn2の組み合わせが
A,C
A,D
B,C
B,D
の4パターンあり、そのそれぞれのカウント数が出てくるかと思います。

ここで、
A,C
A,D
B,C
B,D
A,CD
B,CD
の下の２つのように(A,C)と(A,D)の合算値を出す項目みたいなものを出したいのですが、何か良い方法はありませんでしょうか？

ちなみにclumn2の項目C,Dは今は2つですが、実際はいくつあるかわかりませんし、カラムも2つでなくもっとたくさんあります。

よろしくお願いします。
###補足情報(言語/FW/ツール等のバージョンなど)
prestoでやってますのでできればSQL標準のものであればありがたいですが、MySQLとかPostgreSQLとかそういうのでもわかれば教えて下さい。

Answer 1

DBによってはRollUpが使えます。

SELECT clumn1,clumn2,COUNT(*) cnt
FROM table1 t
GROUP BY clumn1,RollUp(clumn2)

のような感じでいけるかも

Comments

[txmy]

ありがとうございます！
RollUpというものを初めて知りました。
RollUpをキーワードにして色々検索したらhiveやprestoでも同じようなことが実現できました。
愚直に何万行も組み合わせ分のコードを生成しててメモリ落ちとかに悩まされていたんですが、おかげさまですっきりとしたコードがかけました。
本当に助かりました。
ありがとうございました。

Answer 2

複数種類の集計を1つのＳＱＬで実行したいとの質問と受け取りました。

レポートとしては、そのような形式がいいかもしれませんが、私はＳＱＬはレポートを作るには向かないと考えています。一旦集計結果をＥｘｃｅｌに取り込みピポットテーブルで臨む形にするか、Ａｃｃｅｓｓで本格的にレポート形式にするのが良いと思います。

というのも、この方向ではどんどんＳＱＬにいろいろな要件（例えば、(A, C), (A, D), (A, CD), (B ...の順で表示してほしいなど）が含まれてくる結果になるような気がしてならないのです。まあ、そうでないときもありますが・・・

Comments

[txmy]

回答ありがとうございます。
確かにそうなんですが、今回はビッグデータを扱うものでありまして、ExcelやAccessでは対応できず、またデータを可視化するためのBIツールとの兼ね合いで、処理の結果全パターンを一つのテーブルとして持っておく必要があるのです。

実際にはprestoやHiveなどを利用してSQLを動かします。

多少重い処理でもマシンスペックである程度は押しきれるのですが、単純に全パターンのSELECT文を書いてUNIONしていくと2^{カラム数}個のSELECT文を書くことになるのであまり現実的ではなさそうです。

GROUP BYでそのようなオプションがあったりしないか、特別な書き方がないか、探してみたのですが、見つけることができず質問した次第です。

[iwamoto_takaaki]

＞多少重い処理でもマシンスペックである程度は押しきれるのですが、単純に全パターンのSELECT文を書いてUNIONしていくと2^{カラム数}個のSELECT文を書くことになるのであまり現実的ではなさそうです。

そうですね・・・ちょっとぞっとするようなサイズですね。もし私がかけと言われたらＳＱＬを作るプログラム作ります。

一時テーブルに集計結果をインサートする方法はどうでしょうか？
発行するｓｑｌはunionしないので、とりあえず字数制限にはかからないし、ｓｑｌが複雑化するのもある程度抑えられると思います。

＞GROUP BYでそのようなオプションがあったりしないか、特別な書き方がないか、探してみたのですが、見つけることができず質問した次第です。

私には思いつかないです。レポートを作るような機能はＳＱＬにはほとんどないので、ないだろうなと思います。環境が許せば、集計はプログラムで作成します。

Answer 3

「A,CD」「B,CD」 これはつまるところclumn1のみでグループした際のA,Bのレコード数のことだと思います。
このケースのみならclumn1でグループしたサブクエリをUNIONすれば可能かと思います。
(動作は確認してないです。)

SELECT clumn1,clumn2,COUNT(*) cnt
FROM table1
GROUP BY clumn1,clumn2

UNION ALL

SELECT clumn1, 'CD' clumn2, COUNT(*) cnt
FROM table1
GROUP BY clumn1

でも仮にclumn3まであってE,Fだった場合、「A(CDEF)」「A,C(EF)」「A,D(EF)」「B(CDEF)」「B,C(EF)」「B,D(EF)」の全てのケースの合計もほしいのでしょうか？またそのように動的に変化するカラムや項目に自動対応する汎用的なSQLが必要なのでしょうか？
これらの条件次第で難易度は変わると思います。

Comments

[txmy]

ありがとうございます。
カラムは大体6~10個あって全パターン必要なのでSELECTを全通り分書くのは難しいのです。。。

SQLだけでそのように汎用的に書くことって可能なんでしょうか？