本篇介绍Hive三种方法，优化多count(distinct )
先上待优化代码：
select count(distinct sid) as sid
,count(distinct entity_id) as entity_id
,count(distinct billing_status_code) as billing_status_code
from c_detail
where cal_dt='2020-03-30';
因为count(disticnt)需要去重操作，需要将所有数据放到同一task去重，只会产生一个reduce task。如果数据量过大，成为性能瓶颈
两种普通操作：
一、负载均衡法(将一步到位转成两步 ①去重 ②count)
select max(case when col = 'sid' then cn end) as sid
,max(case when col = 'entity_id' then cn end) as entity_id
,max(case when col = 'billing_status_code' then cn end) as billing_status_code
from
(select count(1) as cn,'sid' as col from (select sid from c_detail where cal_dt='2020-03-30' group by sid)a union all
select count(1) as cn,'entity_id' as col from (select entity_id from c_detail where cal_dt='2020-03-30' group by entity_id)a union all
select count(1) as cn,'billing_status_code' as col from (select billing_status_code from c_detail where cal_dt='2020-03-30' group by billing_status_code)a )b ;
优点：负载均衡，完全解决一个reduce产生的性能瓶颈
缺点：写起来比较麻烦，需要每个字段单独去重union all到一起。最后还得行转列，如果几十个count(distinct) 写死也不为过。
二、省事法 ①先组合去重减少数据量 ②count(distinct) 第二步仍然是一个reduce，但是数据量减少了。
select count(distinct sid),count(distinct entity_id),count(distinct billing_status_code)
from
(select sid,entity_id,billing_status_code
from c_detail
where cal_dt='2020-03-30'
group by sid,entity_id,billing_status_code)a;
优点：写起来简单
缺点：如果去重后数据量仍然非常大，还是跑不出来，如果sid是用户id基数很大，其他两列基数很小，这个去重效果就微乎其微，需要一二组合使用
组合折中法
select count(distinct sid),count(distinct entity_id),count(distinct billing_status_code)
from
(select entity_id,billing_status_code from c_detail
where cal_dt='2020-03-30'
group by entity_id,billing_status_code)a
join (select count(1) as sid from (select distinct sid from c_detail
where cal_dt='2020-03-30') b)c on 1=1;
如果基数低字段较多的话，组合使用写起来也非常烦
三、高端大气grouping sets法，既写起来方便，又能负载均衡
select count(case when entity_id is null and billing_status_code is null then 1 end) as sid
,count(case when sid is null and billing_status_code is null then 1 end) as entity_id
,count(case when sid is null and entity_id is null then 1 end) as billing_status_code
from
(select sid,entity_id,billing_status_code
from c_detail
group by sid,entity_id,billing_status_code
grouping sets((sid),(entity_id),(billing_status_code)))a;
相当于使用grouping sets来代替 无数的group by+union all
缺点：grouping sets组过多会产生性能问题，grouping sets语法逻辑，会产生什么样的性能问题呢？请看下篇 Hive多维度聚合
