周活

周活

select
date_format(from_utc_timestamp(unix_timestamp()*1000,"GMT+8"),'yyyy-MM-dd') as current_date_day,
case
when pmod(datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),'2018-01-01') + 1,7) = 1 
then count(distinct
case when datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd') , dt) <=7 then  user_id end 
) 
when pmod(datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),'2018-01-01') + 1,7) = 2
then count(distinct
case when datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd') , dt) <=1 then  user_id end 
) 
when pmod(datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),'2018-01-01') + 1,7) = 3 
then count(distinct
case when datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd') , dt) <=2 then  user_id end 
) 
when pmod(datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),'2018-01-01') + 1,7) = 4 
then count(distinct
case when datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd') , dt) <=3 then  user_id end 
) 
when pmod(datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),'2018-01-01') + 1,7) = 5 
then count(distinct
case when datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd') , dt) <=4 then  user_id end 
) 
when pmod(datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),'2018-01-01') + 1,7) = 6 
then count(distinct
case when datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd') , dt) <=5 then  user_id end 
) 
when pmod(datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),'2018-01-01') + 1,7) = 7 
then count(distinct
case when datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd') , dt) <=6 then  user_id end 
) 
end 
from ruiquan_dws.dws_ruiquan_user
;

先判断现在是这周的第几天，然后在根据实际的星期几统计这一周之内的数据。
月活就是把周活的判断做个拓展。把七天变成三十天。