表結(jié)構(gòu)
CREATE TABLE test (f1 string,
f2 string,
f3 string,
cnt int) ROW FORMAT delimited FIELDS TERMINATED BY '\t' stored AS textfile;
LOAD DATA LOCAL inpath '/data/logs/suiyingli/tmp/test.data' overwrite INTO TABLE test;
原始數(shù)據(jù)
?A A B 1
?B B A 1
?A A A 2
1夺颤、with cube
查詢語(yǔ)句
SELECT f1,
f2,
f3,
sum(cnt),
GROUPING__ID,
rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')
FROM test
GROUP BY f1,
f2,
f3 WITH CUBE;
結(jié)果
with cube查詢結(jié)果
2痢缎、with rollup
查詢語(yǔ)句
SELECT f1,
f2,
f3,
sum(cnt),
GROUPING__ID,
rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')
FROM test
GROUP BY f1,
f2,
f3 WITH ROLLUP;
結(jié)果
with rollup查詢結(jié)果
3、grouping sets
查詢語(yǔ)句
SELECT f1,
f2,
f3,
sum(cnt),
GROUPING__ID,
rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')
FROM test
GROUP BY f1,
f2,
f3
GROUPING SETS((f1),(f1,f2))
結(jié)果
grouping sets查詢結(jié)果
總結(jié)
cube的分組組合最全世澜，是各個(gè)維度值的笛卡爾（包含null）組合独旷，
rollup的各維度組合應(yīng)滿足，前一維度為null后一位維度必須為null寥裂，前一維度取非null時(shí)嵌洼，下一維度隨意，
grouping sets則為自定義維度封恰，根據(jù)需要分組即可麻养。
ps:通過(guò)grouping sets的使用可以簡(jiǎn)化SQL，比group by單維度進(jìn)行union性能更好诺舔。
