海牛部落 hive 系列教程（二十三）：hive 的 grouping sets、排序、窗口函数用法

1. GROUPING SETS

语法
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 country,gpcategory,count(1) from user_install_status_mid where dt=20141228
group by country,gpcategory
grouping sets((country,gpcategory),(country),(gpcategory),()) limit 10;
等于
select * from (
select country,gpcategory,count(1) from user_install_status_mid where dt=20141228
group by country,gpcategory
union all
select country,null,count(1) from user_install_status_mid where dt=20141228
group by country
union all
select null,gpcategory,count(1) from user_install_status_mid where dt=20141228
group by ,gpcategory
union all
select null,null,count(1) from user_install_status_mid where dt=20141228
) a limit 10;

以下内容回帖可见………………