HiveQL如何合并按日、按月统计的两个SELECT结果写入新表
问题原因
你遇到的问题主要有两点:
- 语法错误:现有SQL中
substr('date_upd',xxx)将字段名用单引号包裹,实际是对固定字符串date_upd做截取,而非操作表中的date_upd字段,首先需要去掉字段名外的单引号。 - 逻辑错误:同时按「月、日」两个维度分组时,分组最小粒度是「某月份的某一天」,两次count统计的都是单日的行数,自然得不到整月的汇总值。
实现方案
方案1:UNION ALL 合并查询(逻辑直观)
直接将两个不同维度的统计结果通过UNION ALL合并,要求两个子查询返回的字段数量、类型完全对齐,可新增维度标识字段区分统计类型:
-- 写入目标表,可根据需要调整目标表字段结构 INSERT INTO target_table(stat_type, stat_time, cnt) -- 日维度统计 SELECT 'day' AS stat_type, substr(date_upd, 1, 10) AS stat_time, count(*) AS cnt FROM tbl_one GROUP BY substr(date_upd, 1, 10) UNION ALL -- 月维度统计 SELECT 'month' AS stat_type, substr(date_upd, 1, 7) AS stat_time, count(*) AS cnt FROM tbl_one GROUP BY substr(date_upd, 1, 7);
方案2:GROUPING SETS 多维度聚合(性能更优)
Hive原生支持GROUPING SETS语法,仅需扫描一次原表即可完成多个不同粒度的分组统计,性能远高于UNION ALL方案:
INSERT INTO target_table(month, day, cnt_month, cnt_day) SELECT substr(date_upd, 1, 7) AS month, substr(date_upd, 1, 10) AS day, -- 当月维度聚合时返回统计值,否则返回空 IF(grouping__id = 1, count(*), NULL) AS cnt_month, -- 当日维度聚合时返回统计值,否则返回空 IF(grouping__id = 3, count(*), NULL) AS cnt_day FROM tbl_one GROUP BY substr(date_upd, 1, 7), substr(date_upd, 1, 10) -- 定义需要计算的两种聚合粒度:仅按月、按月+日 GROUPING SETS( (substr(date_upd, 1, 7)), (substr(date_upd, 1, 7), substr(date_upd, 1, 10)) );
grouping__id是Hive提供的内置字段,用于标识当前行对应的聚合分组粒度,和GROUPING SETS中定义的分组规则一一对应。
内容的提问来源于stack exchange,提问作者user16252641
相关产品推荐
相关产品推荐

