大数据用户行为分析hql

笛卡尔积被禁用
FAILED: SemanticException Cartesian products are disabled for safety reasons. If you know what you are doing, please sethive.strict.checks.cartesian.product to false and that hive.mapred.mode is not set to 'strict' to proceed. Note that if you may get errors or incorrect results if you make a mistake while using some of the unsafe features.
设置
set hive.strict.checks.cartesian.product=false;

上周回流用户
select distinct deviceid from ext_startup_logs 
where appid='sdk34734' and concat(ym,day) >= formattime(getweekbegin(-1),'yyyyMMdd')
and concat(ym,day) < formattime(getweekbegin(),'yyyyMMdd')
and deviceid not in
(
select distinct deviceid from ext_startup_logs 
where appid='sdk34734' and concat(ym,day) >= formattime(getweekbegin(-2),'yyyyMMdd')
and concat(ym,day) < formattime(getweekbegin(-1),'yyyyMMdd')
);

本周回流用户
select distinct deviceid from ext_startup_logs 
where appid='sdk34734' and concat(ym,day) >= formattime(getweekbegin(),'yyyyMMdd')
and deviceid not in
(
select distinct deviceid from ext_startup_logs 
where appid='sdk34734' and concat(ym,day) >= formattime(getweekbegin(-1),'yyyyMMdd')
and concat(ym,day) < formattime(getweekbegin(),'yyyyMMdd')
);

新增用户
本周新增用户
select
count(*)
from
(select min(createdatms) mintime
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having mintime >= getweekbegin() and mintime < getweekbegin(1)
) t ;
前天新增用户
select
count(*)
from
(select min(createdatms) mintime
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having mintime >= getdaybegin(-2) and mintime < getdaybegin(-1)
)t ;
本月新增用户
select
count(*)
from
(select min(createdatms) mintime
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having mintime >= getmonthbegin() and mintime < getmonthbegin(1)
)t ;

活跃用户
前天日活跃
select
count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getdaybegin(-2) and createdatms < getdaybegin(-1);
本周周活跃
select
count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getweekbegin() and createdatms < getweekbegin(1);
本月月活跃
select
count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getmonthbegin() and createdatms < getmonthbegin(1);
本周每一天的日活跃
select
formattime(createdatms,'yyyy/MM/dd') day ,count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and createdatms >= getweekbegin() and createdatms < getweekbegin(1)
group by formattime(createdatms,'yyyy/MM/dd');
半年的每月月活跃
select
formattime(createdatms,'yyyyMM') stdate, count(distinct deviceid) stcount
from ext_startup_logs
where ym >= formattime(getmonthbegin(-5),'yyyyMM') and appid ='sdk34734'
group by formattime(createdatms,'yyyyMM');
连续三周的活跃用户
select deviceid , count(distinct(formattime(createdatms,'yyyyMMdd',0))) c
from ext_startup_logs
where appid = 'sdk34734'
and concat(ym,day) >= formattime(getweekbegin(-2),'yyyyMMdd')
group by deviceid
having c = 3;

用户留存
周留存：本周访问并且是上周的新增用户
select
distinct s.deviceid
from ext_startup_logs s
where appid = 'sdk34734'
and concat(ym,day) >= formattime(getweekbegin(),'yyyyMMdd')
and concat(ym,day) < formattime(getweekbegin(1),'yyyyMMdd')
and deviceid in (
select distinct t.deviceid
from (
select tt.deviceid , min(tt.createdatms) mintime
from ext_startup_logs tt
where tt.appid = 'sdk34734'
group by tt.deviceid having mintime >= getweekbegin(-1) and mintime < getweekbegin()
) t); 

版本分布
前天每个版本的使用用户数
select
appversion,count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and ym = formattime(getdaybegin(),'yyyyMM') and day = formattime(getdaybegin(-1),'dd')
group by appversion;
本周每天各版本版本使用用户数
select
formattime(createdatms,'yyyyMMdd'),appversion , count(distinct deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and concat(ym,day) >= formattime(getweekbegin(),'yyyyMMdd')
group by formattime(createdatms,'yyyyMMdd'), appversion;

启动次数
前天的app启动次数
select
count(deviceid)
from ext_startup_logs
where appid = 'sdk34734'
and ym = formattime(getdaybegin(),'yyyyMM') and day = formattime(getdaybegin(-1),'dd');

沉默用户
沉默用户（只访问过一次，超过2天没访问）
select
count(*)
from
(select deviceid , count(createdatms) dcount,min(createdatms) dmin
from ext_startup_logs
where appid = 'sdk34734'
group by deviceid
having dcount = 1 and dmin < getdaybegin(-1)
)t;