目錄

準(zhǔn)備工作：

在hive中建表

在presto中計(jì)算

分解式

按照城市分組 統(tǒng)計(jì)人數(shù)

按照性別分組 統(tǒng)計(jì)人數(shù)

?編輯

按照愛好分組 統(tǒng)計(jì)人數(shù)

?編輯

按照城市和性別分組 統(tǒng)計(jì)人數(shù)

按照城市和愛好分組 統(tǒng)計(jì)人數(shù)

按照性別和愛好分組 統(tǒng)計(jì)人數(shù)

按照城市和性別還有愛好分組 統(tǒng)計(jì)人數(shù)

統(tǒng)計(jì)人數(shù)

合并式

presto使用grouping

presto使用grouping sets

grouping作用例子展示

高級(jí)用法: cube

rollup 用法

準(zhǔn)備工作：

在hive中建表

drop database if exists db_test cascade;create database db_test;create table db_test.tb_student(name string,score   int,city    string,sex string,hobby string
)
row format delimited fields terminated by '\t';load data local inpath '/test/student.txt' into table db_test.tb_student;select * from db_test.tb_student;

student.txt數(shù)據(jù)

張三 ? ?10 ? ? ?北京 ? ?男 ? ? ?喝酒
李四 ? ?20 ? ? ?北京 ? ?男 ? ? ?抽煙
王五 ? ?30 ? ? ?北京 ? ?女 ? ? ?燙頭
趙六 ? ?40 ? ? ?上海 ? ?男 ? ? ?抽煙
麻七 ? ?50 ? ? ?上海 ? ?女 ? ? ?燙頭

在presto中計(jì)算

分解式

按照城市分組 統(tǒng)計(jì)人數(shù)

select city,count(1) as cnt from hive.db_test.tb_student group by city;

按照性別分組 統(tǒng)計(jì)人數(shù)

select hobby,count(1) as cnt from hive.db_test.tb_student group by hobby;

按照愛好分組 統(tǒng)計(jì)人數(shù)

select hobby,count(1) as cnt from hive.db_test.tb_student group by hobby;

按照城市和性別分組 統(tǒng)計(jì)人數(shù)

select city, sex, count(1) as cnt from hive.db_test.tb_student group by city, sex;

按照城市和愛好分組 統(tǒng)計(jì)人數(shù)

select city, hobby, count(1) as cnt from hive.db_test.tb_student group by city, hobby;

按照性別和愛好分組 統(tǒng)計(jì)人數(shù)

select sex, hobby, count(1) as cnt from hive.db_test.tb_student group by sex, hobby;

按照城市和性別還有愛好分組 統(tǒng)計(jì)人數(shù)

select city, sex, hobby, count(1) as cnt from hive.db_test.tb_student group by city, sex, hobby;

統(tǒng)計(jì)人數(shù)

select count(1) as cnt from hive.db_test.tb_student group by ();

合并式

with t1 as (select city, null as sex, null as hobby, count(1) as cnt, 1 as o from hive.db_test.tb_student group by cityunion allselect null as city, sex, null as hobby, count(1) as cnt, 2 as o from hive.db_test.tb_student group by sexunion allselect null, null, hobby,count(1) as cnt, 3 as o from hive.db_test.tb_student group by hobbyunion allselect city, sex, null, count(1) as cnt, 4 as o from hive.db_test.tb_student group by city, sexunion allselect city, null, hobby, count(1) as cnt, 5 as o from hive.db_test.tb_student group by city, hobbyunion allselect null, sex, hobby, count(1) as cnt, 6 as o from hive.db_test.tb_student group by sex, hobbyunion allselect city, sex, hobby, count(1) as cnt, 7 as o from hive.db_test.tb_student group by city, sex, hobbyunion allselect null, null, null, count(1) as cnt, 8 as o from hive.db_test.tb_student group by ()
)
select * from t1
order by o, city, sex, hobby
;

presto使用grouping

selectcity,sex,count(1) as cnt,grouping(city, sex) as g
from hive.db_test.tb_student
group by city, sex
;

presto使用grouping sets

selectcity,sex,hobby,count(1) as cnt,grouping(city, sex, hobby)
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby)
;

selectcity,sex,hobby,count(1) as cnt,grouping(city, sex, hobby)
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby, (city, sex), (city, hobby), (sex, hobby), (city, sex, hobby), ())
;

selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)=3 then 1when grouping(city, sex, hobby)=5 then 2when grouping(city, sex, hobby)=6 then 3when grouping(city, sex, hobby)=1 then 4when grouping(city, sex, hobby)=2 then 5when grouping(city, sex, hobby)=4 then 6when grouping(city, sex, hobby)=0 then 7when grouping(city, sex, hobby)=7 then 8else 100end as o
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby, (city, sex), (city, hobby), (sex, hobby), (city, sex, hobby), ())
order by o, city, sex, hobby
;

grouping作用例子展示

with t1 as (select '北京' as city, '男' as sexunion allselect '北京' as city, '男' as sexunion allselect '北京' as city, '女' as sexunion allselect '北京' as city, null as sex
)
selectcity,sex,count(1) as cnt
from t1
group by grouping sets (city, (city, sex))

with t1 as (select '北京' as city, '男' as sexunion allselect '北京' as city, '男' as sexunion allselect '北京' as city, '女' as sexunion allselect '北京' as city, null as sex
)
selectcity,sex,count(1) as cnt,grouping(city, sex) g
from t1
group by grouping sets (city, (city, sex))

selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)=3 then 1when grouping(city, sex, hobby)=5 then 2when grouping(city, sex, hobby)=6 then 3when grouping(city, sex, hobby)=1 then 4when grouping(city, sex, hobby)=2 then 5when grouping(city, sex, hobby)=4 then 6when grouping(city, sex, hobby)=0 then 7when grouping(city, sex, hobby)=7 then 8else 100end as o
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby, (city, sex), (city, hobby), (sex, hobby), (city, sex, hobby), ())
order by o, city, sex, hobby

selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)=3 then 1when grouping(city, sex, hobby)=5 then 2when grouping(city, sex, hobby)=6 then 3when grouping(city, sex, hobby)=1 then 4when grouping(city, sex, hobby)=2 then 5when grouping(city, sex, hobby)=4 then 6when grouping(city, sex, hobby)=0 then 7when grouping(city, sex, hobby)=7 then 8else 100end as o
from hive.db_test.tb_student
group by cube(city, sex, hobby)
order by o, city, sex, hobby

selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)=3 then 1when grouping(city, sex, hobby)=5 then 2when grouping(city, sex, hobby)=6 then 3when grouping(city, sex, hobby)=1 then 4when grouping(city, sex, hobby)=2 then 5when grouping(city, sex, hobby)=4 then 6when grouping(city, sex, hobby)=0 then 7when grouping(city, sex, hobby)=7 then 8else 100end as o
from hive.db_test.tb_student
group by rollup(city, sex, hobby)
order by o, city, sex, hobby
;