Hive高阶聚合函数用法示例

一、创建表：

create table hive_function_test_df
(
 province_name       string        comment '省份名称'
,city_name           string        comment '城市名称'
,area_name           string        comment '地区名称'
,people_cnt          int           comment '人口数量'
,amt                 decimal(16,2) comment 'GDP'
)
row format delimited fields terminated by ',';

二、加载数据：

注：也可以采用插入数据方式

load data local inpath '/opt/module/hive/datas/city.txt' into table hive_function_test_df;

三、查询结果：

select * from hive_function_test_df;

四、with cube用法示例：

4.1查询语句：

select province_name
     ,city_name
     ,area_name
     ,sum(people_cnt) as all_people_cnt
from hive_function_test_df
group by province_name
       , city_name
       , area_name
with cube ;

4.2查询结果：

五、grouping sets用法示例：

5.1查询语句：

set mapred.map.tasks.speculative.execution=true
set mapred.reduce.tasks.speculative.execution=true
select  province_name,
       city_name,
       area_name,
       sum(people_cnt) as all_people_cnt
from hive_function_test_df
group by province_name, city_name, area_name
grouping sets ((),(province_name),(province_name,city_name));

5.2查询结果：

 六、with rollup用法示例：

6.1查询语句：

set mapred.map.tasks.speculative.execution=true
set mapred.reduce.tasks.speculative.execution=true
select province_name ,
    city_name,
    area_name,
    sum(people_cnt) as all_people_cnt
from hive_function_test_df
group by 
    province_name,
    city_name,
    area_name
with rollup;

6.2查询结果：