Hive高阶聚合函数 GROUPING SETS、Cube、Rollup

-- 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;