count distinct if 分析

我们稍做修改

1 2 3 4 5

select partition_date,count(user_id),          count(distinct if(user_is_new = 1, user_id, 0))  --注意新增用户量的统计,加了distinct去重 from dw.nice_live_dw_user_active_day where location_city like '%xxx%' and partition_date >= 20180901 group by partition_date;

结果如下

1	20180901    16737  261

 这次看着就像是对了吧,我们加了distinct进行去重

每次来一条数据先过if()然后再进行去重最后统计.但是实际上结果依旧是错误的.

我们来模拟一下筛选统计的过程

我们有这样四条数据

user_id    user_is_new

   1               1

   2              0

   3              1

   4              0

表中的数据是一条一条遍历的,

(1)当user_id = 1的数据过来的时候,我们先过if函数  user_is_new = 1    ==>  count(distinct user_id = 1), 

然后我们把user_id = 1进行重复判断,我们用一个模拟容器来模拟去重,

从容器里找user_id = 1的数据,发现没有,不重复,所以通过我们把count+1,然后把user_id = 1的数据放入,用于下条去重

(2)当user_id = 2的数据过来的时候,我们先过if函数  user_is_new = 0    ==>  count(distinct 0), 

然后我们把0进行重复判断,

从容器里找0的数据,发现没有,不重复,所以通过我们把count+1,然后把0的数据放入,用于下条去重

(3)当user_id = 3的数据过来的时候,我们先过if函数  user_is_new = 1    ==>  count(distinct user_id = 3), 

然后我们把user_id = 3进行重复判断,

从容器里找user_id = 3的数据,发现没有,不重复,所以通过我们把count+1,然后把user_id = 3的数据放入,用于下条去重

(4)当user_id = 4的数据过来的时候,我们先过if函数  user_is_new = 0    ==>  count(distinct 0), 

然后我们把0进行重复判断,

从容器里找0的数据,发现重复,是之前user_id = 2的时候过if()转化成0的那条数据,所以count不执行

我们通过模拟count(distinct if)过程发现,在count的时候我们把不符合条件的最开始的那条语句也count进去了一次

导致最终结果比正确结果多了1.

我们在原基础语句上再减去1就是正确的hql语句

其实在日常中我们做分类筛选统计的时候一般是用sum来完成的,符合条件sum+1,不符合条件sum+0

1 2 3 4 5

select partition_date,count(user_id),          sum(if(user_is_new = 1, 1, 0))  --用sum进行筛选统计 from dw.nice_live_dw_user_active_day where location_city like '%xxx%' and partition_date >= 20180901 group by partition_date;

 结果如下

1	20180901    16737  260

 sum(if)只试用于单个条件判断,如果筛选条件很多,我们可以用sum(case when then else end)来进行多条件筛选

注意,hive中并没有sum(distinct col1)这种使用方式,我们可以使用sum(col) group by col来达到相同效果.

 

count(distinct if(user_is_new = 1, user_id, null)) //这样统计结果就不会多1了