表名: user_active_day (使用者日活表)
表内容:
user_id(使用者id) user_is_new(是否新使用者 1:新增使用者 0:老使用者) location_city(使用者所在地區) partition_date(日期分區)
需求:
找出20180901至今的xxx地區的使用者日活量以及新增使用者量
思路:
篩選日期分區和地區,統計user_id的數量為使用者日活量,統計user_is_new = 1的數量為新增使用者量.
最開始寫的hql語句
select partition_date,count(user_id),
count(if(user_is_new = 1, user_id, 0)) --注意新增使用者量的統計
from dw.nice_live_dw_user_active_day
where location_city like '%xxx%' and partition_date >= 20180901
group by partition_date;
我們使用count(if())來進行篩選統計,但是效果并沒有達到,出現的結果如下
2018090116737 16737
根本就沒有達到篩選的目的,為什麼?
這就要從count的機制說起
首先count()是對資料進行計數,說白了就是你來一條資料我計數一條,我不關心你怎麼分類,我隻對資料計數
每條資料從if()函數出來,還是一條資料,是以count+1
是以count(user_id)跟count(if(user_id))沒有任何的差別.
我們稍做修改
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;
結果如下
2018090116737 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
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;
結果如下
2018090116737 260
sum(if)隻試用于單個條件判斷,如果篩選條件很多,我們可以用sum(case when then else end)來進行多條件篩選
注意,hive中并沒有sum(distinct col1)這種使用方式,我們可以使用sum(col) group by col來達到相同效果.