hive Group操作

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

常用于计算各种组合的报表数据。

select * from (
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
    ) d 
        情况1 : 
        where d.a is null and d.b is null;
        情况2 : 
        where d.a is null and d.b is not null;
        情况2 : 
        where d.a is not null and d.b is null;
        情况4 : 
        where d.a is not null and d.b is not null;

示例
创建student_grouping表

CREATE TABLE student_grouping(
id int,
name string,
age int,
sex string 
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 分组
group by age group by sex group by age,sex 查询全部

给表导入数据

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

查询数据
查询分组数据

SELECT age, sex, count(id) FROM student_grouping GROUP BY age, sex GROUPING SETS ((age,sex),age,sex,());
GROUP BY age, sex 设置分组字段
GROUPING SETS ((age,sex),age,sex,()) 分组方式

Total MapReduce CPU Time Spent: 4 seconds 750 msec
OK
NULL    NULL    8
NULL    boy     5
NULL    girl    3
12      NULL    2
12      boy     2
13      NULL    2
13      boy     1
13      girl    1
14      NULL    2
14      boy     2
15      NULL    2
15      girl    2

等于
select age, sex, count(id) from student_grouping group by age,sex; = (age,sex)
以age,sex为组
Total MapReduce CPU Time Spent: 9 seconds 550 msec
OK
12      boy     2
13      boy     1
13      girl    1
14      boy     2
15      girl    2

select age, null, count(id) from student_grouping group by age; = age
以age为组
Total MapReduce CPU Time Spent: 8 seconds 920 msec
OK
12      NULL    2
13      NULL    2
14      NULL    2
15      NULL    2

select null, sex, count(id) from student_grouping group by sex; = sex
以sex为组
Total MapReduce CPU Time Spent: 8 seconds 920 msec
OK
NULL    boy     5
NULL    girl    3

select null,null,count(id) from student_grouping; = ()
求总数
Total MapReduce CPU Time Spent: 9 seconds 190 msec
OK
NULL    NULL    8

grouping sets加上if,case用法

select * from 
(SELECT age, sex, count(id) c FROM student_grouping GROUP BY age, sex GROUPING SETS ((age,sex),age,sex,())) a  
where a.age is null and a.sex is not null;

select 
if(age is not null,age,'ALL'),sex,c from 
(SELECT age, sex, count(id) c FROM student_grouping GROUP BY age, sex GROUPING SETS ((age,sex),age,sex,())) a  
where a.age is null and a.sex is not null;

select 
age,if(sex is not null,sex,'ALL'),c from 
(SELECT age, sex, count(id) c FROM student_grouping GROUP BY age, sex GROUPING SETS ((age,sex),age,sex,())) a  
where a.age is not null and a.sex is null;

-- 除了if 还可以使用 case when
SELECT 
if(age is not null, age, 'ALL'), 
case 
	when sex is not null then sex else 'ALL' end 
as age,count(id) 
FROM student_grouping 
GROUP BY age, sex GROUPING SETS ( (age,sex),age,sex,() );
age sex count
ALL	ALL	8 所有性别和所有年龄 = 8
ALL	boy	5 所有年龄段的男孩 = 5
ALL	girl	3 所有年龄段的女孩 = 3
12	ALL	2 12岁的所有孩子 = 2
12	boy	2 12岁的男孩 = 2
13	ALL	2
13	boy	1
13	girl	1
14	ALL	2
14	boy	2
15	ALL	2
15	girl	2

with cube 是group by中所有key的组合 非常多

根据提供的group by的分组字段的所有组合进行分组数据的查询
举例说明 :
假如 group by a,b,c
with cube =GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),a,b,c,())
所以上面的写法可简写成

SELECT if(age is not null, age, 'ALL'), 
case when sex is not null then sex else 'ALL' end as age,
count(id) 
FROM student_grouping 
GROUP BY age, sex with cube;

-- GROUP BY age, sex

-- with cube (age,sex),age,sex,()

原来的写法

SELECT if(age is not null, age, 'ALL'), 
case when sex is not null then sex
else 'ALL'
end as age,
count(id) 
FROM student_grouping 
GROUP BY age, sex GROUPING SETS ( (age,sex),age,sex,() );

with rollup是 按右侧递减的顺序组合

例如：
group by a,b,c
with rollup
GROUPING SETS ((a,b,c),(a,b),a,())

实际案例 : 年龄 性别 国家 --> 1. 年龄 性别 国家 2. 年龄 性别 3. 年龄 4 () with rollup
grouping sets()

右侧递减的意思就是从左到右 依次减去最右面的;

-- GROUP BY age, sex with rollup 等效于  GROUP BY age, sex GROUPING SETS ( (age,sex),age,() )
-- 相当于按右侧递减的顺序group by
SELECT if(age is not null, age, 'ALL'), 
case when sex is not null then sex else 'ALL' end as age,
count(id) FROM student_grouping GROUP BY age, sex with rollup;
--等于
SELECT if(age is not null, age, 'ALL'), 
case 
	when sex is not null then sex
	else 'ALL'
end as age,
count(id) FROM student_grouping GROUP BY age, sex
GROUPING SETS ( (age,sex),age,() );