Shulou(Shulou.com)06/01 Report--

This article mainly shows you the "Hive high-order aggregate function usage examples", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Hive high-order aggregate function usage examples" this article.

If we need to do some metric statistics in multi-dimensions and the amount of data is relatively large, we will generally choose the olap engine, such as kylin, but if the amount of data is not very large, hive can also be achieved.

Create table wedw_tmp.hive_function_test_df

(

Province_name string comment 'province name'

, city_name string comment 'city name'

, area_name string comment 'region name'

, people_cnt int comment 'population'

, amt decimal (16pyr2) comment 'GDP'

)

Row format delimited fields terminated by','

Guangdong, Guangzhou, Baiyun District, 100pm 30

Guangdong, Guangzhou, Panyu District, 120pm 45

Guangdong, Shenzhen, Futian District, 200pm 67

Guangdong, Shenzhen, Nanshan District, 290100

Zhejiang, Hangzhou, Xiaoshan District, 80pi 20

Zhejiang, Hangzhou, Binjiang District, 120pm 50

Zhejiang, Ningbo, Jiangdong District, 80pr 23

Zhejiang, Ningbo, Jiangbei District, 45pence 5

Select * from wedw_tmp.hive_function_test_df

One with cube

Select

Province_name

, city_name

, area_name

, sum (people_cnt) as all_people_cnt

From

Wedw_tmp.hive_function_test_df

Group by

Province_name

, city_name

, area_name

With cube

Two grouping sets

Select

Province_name

, city_name

, area_name

, sum (people_cnt) as all_people_cnt

From

Wedw_tmp.hive_function_test_df

Group by

Province_name

, city_name

, area_name

Grouping sets ((), (province_name), (province_name,city_name))

Three with rollup

Select

Province_name

, city_name

, area_name

, sum (people_cnt) as all_people_cnt

From

Wedw_tmp.hive_function_test_df

Group by

Province_name

, city_name

, area_name

With rollup

These are all the contents of the article "examples of the use of Hive high-order aggregate functions". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.