之前整理了一下Hive 0.10版引进的GROUPING SETS子句特性，并作了简单的句法使用体验和数据验证。但是当时没有注意到稍微复杂一点的情况，然后，在实际使用过程中，妥妥地就中了一枪。 这一枪发生在有JOIN操作的时候，情况是这样的：我要对Hive表data_table的
之前整理了一下Hive 0.10版引进的GROUPING SETS子句特性，并作了简单的句法使用体验和数据验证。但是当时没有注意到稍微复杂一点的情况，然后，在实际使用过程中，妥妥地就中了一枪。
这一枪发生在有JOIN操作的时候，情况是这样的：我要对Hive表data_table的a, b, c这3个字段去统计UV和VV这两个数据，并需要由c上卷到b，再上卷到a的统计数据。同时，要对字段b的值进行值映射，这是通过和另外一个专门描述b值的表进行JOIN来实现的。HQL语句如下：
select
t1.a_desc,
if(t1.group_bitvector= 1, '未区分', t2.b_desc) as b_desc,
if(t1.group_bitvector= 1 or t1.group_bitvector = 3, '未区分', t1.c_desc) as c_desc,
t1.group_bitvector,
t1.act_uv,
t1.act_vv
from
(
select
a_desc,
b,
c_desc,
grouping__id as group_bitvector,
count(distinctuer_ID) as act_uv,
(sum(if(vv_ID is null, 1, 0)) + sum(if(vv_ID = "", 1, 0)) + count(distinct if(vv_ID is not null, if(vv_ID != "", vv_ID, null), null))) as act_vv
from
(
select
(
case a
when 0 then 'str_val_1'
when 1 then 'str_val_2'
when 2 then 'str_val_3'
else 'str_val_4'
end
)as a_desc,
b,
if(c= -2 or c = -1 or c = 9, 'c_desc1', 'c_desc2') as c_desc,
uer_ID,
vv_ID
from data_table
where a = xxx
) t
group by a_desc, b, c_desc
grouping sets (a_desc,(a_desc, b),(a_desc, b, c_desc))
) t1 join dim_table t2 on (t1.b = t2.b)
跑出来数据，晃眼一看，是正常的，仔细一看就经不起眼睛的考验了，group_bitvector这一列貌似缺了一个值，这样GROUPING SETS下来，group_bitvector应该出现1, 3, 7这三个值，但是出来的数据竟然没有1，也就是说，缺了最顶层的聚合(只对a进行求聚合)数据！
用力想了想，才拍脑袋发现了这个微妙的错误，JOIN君扔掉了一些数据！GROUPING SETS不是会把GROUP BY子句中没有参与聚合的列置为NULL么，这些NULL值在JOIN的时候就被无情地抛弃了。。。这个时候，LEFT OUTER JOIN勇敢地站了出来，它说：只要用我替换原来的JOIN(内连接)，就可以漂亮地消除了数据被过滤的问题：
select
t1.a_desc,
if(t1.group_bitvector= 1, '未区分', t2.b_desc) as b_desc,
if(t1.group_bitvector= 1 or t1.group_bitvector = 3, '未区分', t1.c_desc) as c_desc,
t1.group_bitvector,
t1.act_uv,
t1.act_vv
from
(
select
a_desc,
b,
c_desc,
grouping__id as group_bitvector,
count(distinctuer_ID) as act_uv,
(sum(if(vv_ID is null, 1, 0)) + sum(if(vv_ID = "", 1, 0)) + count(distinct if(vv_ID is not null, if(vv_ID != "", vv_ID, null), null))) as act_vv
from
(
select
(
case a
when 0 then 'str_val_1'
when 1 then 'str_val_2'
when 2 then 'str_val_3'
else 'str_val_4'
end
)as a_desc,
b,
if(c= -2 or c = -1 or c = 9, 'c_desc1', 'c_desc2') as c_desc,
uer_ID,
vv_ID
from data_table
where a = xxx
) t
group by a_desc, b, c_desc
grouping sets (a_desc,(a_desc, b),(a_desc, b, c_desc))
) t1 left outer join dim_table t2 on (t1.b = t2.b)
这个场景中，grouping__id这个函数的用处也显现出来了，要是没有它，缺数据的问题更容易被忽视。。。
最后，如果是多重或多列进行JOIN，就尤其要注意这个问题了~~~利用LEFT OUTER JOIN和GROUPING__ID的返回值，还是你能够很清晰无误地表达查询数据并进行GROUP组区分的需求。
最后的最后，还有一颗小小的地雷：gouping__id的返回值竟然是字符串类型，而不是直观上看到的整型！有时候Hive会自动为我们处理整型和字符串类型间的解析，毕竟字符串的很多运算和整型运算很相似。但有时候Hive也不会给我们处理，比如在用case…when…then…end子句的时候，字段数据类型必须严格相同，否则会报错，——虽然这个错误提示很明显，比较容易排查。
本文原创发布php中文网，转载请注明出处，感谢您的尊重！
