Hive的group by

-- 创建 stu 表
CREATE TABLE stu(
id int,
name string,
age int,
sex string 
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 表内数据
load data local inpath '/home/hadoop/stu' into table stu;
1   name1   12  boy
2   name2   12  boy
3   name3   13  girl
4   name4   13  boy
5   name5   14  boy
6   name6   14  boy
7   name7   15  girl
8   name8   15  girl

group by

group by 根据一个或多个列对结果集进行分组,一般和聚合函数一起使用才有意义,比如 count sum avg max min等,
使用group by的两个要素:
• 出现在select后面的字段,要么是聚合函数中的,要么就是group by中的.即select列表项中出现的列必须全部出现在group by后面(聚合函数除外).group by中的字段可以不在select列表项中.
• 要筛选结果:
        1.可以先使用 where 再用 group by.
        2.可以先使用 group by 再用 having.

-- 先where后group by
select max(id),max(name),max(age),sex from stu where age=13 group by sex;
-- 先group by后having
select max(id),max(name),age,sex from stu group by age,sex having age=13;

grouping sets

grouping sets是一种将多个 group by 逻辑写在一个sql语句中的便利写法.

GROUP BY a, b GROUPING SETS ((a,b))

SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a,b))
-- 等于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b

GROUP BY a, b GROUPING SETS ((a,b), a)

SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a,b), a)
-- 等于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b 
UNION ALL
SELECT a, null, SUM(c) FROM tab1 GROUP BY a

GROUP BY a, b GROUPING SETS (a,b)

SELECT a,b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)
-- 等于
SELECT a, null, SUM(c) FROM tab1 GROUP BY a 
UNION ALL
SELECT null, b, SUM(c) FROM tab1 GROUP BY b

GROUP BY a, b GROUPING SETS ((a, b), a, b, ())

SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a, b), a, b, ())
-- 等于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b 
UNION ALL
SELECT a, null, SUM(c) FROM tab1 GROUP BY a
UNION ALL
SELECT null, b, SUM(c) FROM tab1 GROUP BY b 
UNION ALL
SELECT null, null, SUM(c) FROM tab1

with cube

是group by中所有key的组合(类似于笛卡尔积)

select age,sex,count(id) from stu group by age,sex with cube;
--等于
select age,sex,count(id) from stu group by age,sex grouping sets((age,sex),age,sex,());

--例如：
group by a,b,c with cube =
grouping sets(
(a,b,c)
(a,b)
(b,c)
(a,c)
a
b
c
()
)

with rollup

是按右侧递减的顺序组合

-- 相当于按右侧递减的顺序group by
select age,sex,count(id) from stu group by age,sex with rollup;
-- 等于
select age,sex,count(id) from stu group by age,sex grouping sets((age,sex),age,());

-- 例如：
group by a,b,c with rollup =
grouping sets(
(a,b,c)
(a,b)
(a)
()
)