【硬刚Hive】Hive实战(3)：Hive 函数(5)HiveSQL 高阶函数合集实战（三）Cube相关、字符相关

Cube相关

GROUPING SETS：使用频率 ★

类似于kylin中的cube，将多种维度进行组合统计；在一个GROUP BY查询中,根据不同维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

1 --按照用户+访问日期统计统计次数
 2 select 
 3  user_id, 
 4  visit_date,
 5  sum(visit_cnt) as visit_cnt
 6 from wedw_tmp.tmp_url_info
 7 group by user_id,visit_date
 8 grouping sets(user_id,visit_date)
 9
10 --下图的结果类似于以下sql
11 select 
12   user_id, 
13   NULL as visit_date,
14   sum(visit_cnt) as visit_cnt
15 from wedw_tmp.tmp_url_info
16 union all 
17 select 
18  NULL as user_id, 
19  visit_date,
20  sum(visit_cnt) as visit_cnt
21 from wedw_tmp.tmp_url_info
22 union all 
23 select 
24  user_id, 
25  visit_date,
26  sum(visit_cnt) as visit_cnt
27 from wedw_tmp.tmp_url_info

字符相关