原文链接：http://click.aliyun.com/m/13956/
随着双十一数据量的暴增，之前用distinct去重可以简单处理的场景，现在消耗的时间成倍增长。如果用了multiple distinct，那就更要警惕，因为多重去重本身会带来数据量的成倍增长，很可能10分钟的任务，在双十一期间会跑上几个小时都没有结果。
这里介绍一个小技巧，其实在稳定性手册里面已经有过介绍，不过总感觉没有看懂。最近正好做了一次优化，于是在这里小结一下：
例如原来的代码是这样：
select D1,D2, count(distinct case when A is not null then B end) as B_distinct_cnt from xxx group by D1,D2
那么优化方案可以是这样：
create table tmp1asselect D1,D2,B,count( case when A is not null then B end ) as B_cntfrom xxxgroup by D1, D1, Bselect D1,D2,sum(case when B_cnt > 0 then 1 else 0 end) as B_distinct_cntfrom tmp1group by D1,D2
多重去重的优化也可以采用上面的方案，只是要注意Group By的Key是以源表聚合维度为基础，根据distinct计算的值进行组合。
例如下面的这个例子：
select D1,D2, count(distinct case when A is not null then B end) as B_distinct_cnt , count(distinct case when E is not null then C end) as C_distinct_cnt from xxx group by D1,D2
那么优化方案可以是：
create table tmp1asselect D1,D2,B,count( case when A is not null then B end ) as B_cntfrom xxxgroup by D1, D1, Bcreate table tmp1_1asselect D1,D2,sum(case when B_cnt > 0 then 1 else 0 end) as B_distinct_cntfrom tmp1group by D1,D2create table tmp2asselect D1,D2,C,count( case when E is not null then C end ) as C_cntfrom xxxgroup by D1, D1, Ccreate table tmp2_1asselect D1,D2,sum(case when C_cnt > 0 then 1 else 0 end) as C_distinct_cntfrom tmp1group by D1,D2select t1.D1,t1.D2, t1.B_distinct_cnt, t2.C_distinct_cntfrom tmp1_1 t1left outer join tmp2_1 t2on t1.D1=t2.D1 and t1.D2=t2.D2
