-- GROUPING SETS作为GROUP BY的子句,允许开发人员在GROUP BY语句后面指定多个统计选项,可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来。 select device_id ,os_id ,app_id ,count(user_id) from test_xinyan_reg group by device_id,os_id,app_id grouping sets((device_id),(os_id),(device_id,os_id),()) -- 等价于 SELECT device_id,null,null,count(user_id) FROM test_xinyan_reg group by device_id UNION ALL SELECT null,os_id,null,count(user_id) FROM test_xinyan_reg group by os_id UNION ALL SELECT device_id,os_id,null,count(user_id) FROM test_xinyan_reg group by device_id,os_id UNION ALL SELECT null,null,null,count(user_id) FROM test_xinyan_reg ; -- cube简称数据魔方,可以实现hive多个任意维度的查询,cube(a,b,c)则首先会对(a,b,c)进行group by,然后依次是(a,b),(a,c),(a),(b,c),(b),©,最后在对全表进行group by,他会统计所选列中值的所有组合的聚合 -- cube即为grouping sets的简化过程函数 select device_id,os_id,app_id,client_version,from_id,count(user_id) from test_xinyan_reg group by device_id,os_id,app_id,client_version,from_id with cube; -- rollup可以实现从右到做递减多级的统计,显示统计某一层次结构的聚合。 select device_id,os_id,app_id,client_version,from_id,count(user_id) from test_xinyan_reg group by device_id,os_id,app_id,client_version,from_id with rollup;
select id ,name ,count(age) from ( select 1 as id, 'a' as name,11 as age union all select 2 as id, 'b' as name,12 as age union all select 3 as id, 'c' as name,13 as age union all select 4 as id, 'd' as name,14 as age union all select 4 as id, 'd' as name,15 as age union all select 4 as id, 'd' as name,16 as age union all select 4 as id, 'd' as name,17 as age union all select 4 as id, 'd' as name,18 as age ) t1 group by id ,name with cube ; +------------+------------+------------+ | id | name | _c2 | +------------+------------+------------+ | NULL | NULL | 8 | | NULL | a | 1 | | NULL | b | 1 | | NULL | c | 1 | | NULL | d | 5 | | 1 | NULL | 1 | | 1 | a | 1 | | 2 | NULL | 1 | | 2 | b | 1 | | 3 | NULL | 1 | | 3 | c | 1 | | 4 | NULL | 5 | | 4 | d | 5 | +------------+------------+------------+ select id ,name ,count(age) from ( select 1 as id, 'a' as name,11 as age union all select 2 as id, 'b' as name,12 as age union all select 3 as id, 'c' as name,13 as age union all select 4 as id, 'd' as name,14 as age union all select 4 as id, 'd' as name,15 as age union all select 4 as id, 'd' as name,16 as age union all select 4 as id, 'd' as name,17 as age union all select 4 as id, 'd' as name,18 as age ) t1 group by id ,name with rollup ; +------------+------------+------------+ | id | name | _c2 | +------------+------------+------------+ | NULL | NULL | 8 | | 1 | NULL | 1 | | 1 | a | 1 | | 2 | NULL | 1 | | 2 | b | 1 | | 3 | NULL | 1 | | 3 | c | 1 | | 4 | NULL | 5 | | 4 | d | 5 | +------------+------------+------------+
ref: https://blog.csdn.net/qq_31573519/article/details/89054136