很简单的sql 用户分析语句 :只要自定义简单的udf函数 获取统计时间createdatms字段的
使用的日历类 add方法 和simpledateformat 将long类型的 定义多个重载方法 获取返回值int类型 或者long类型 进行时间判断即可
getdaybegin(天开始),比如2017-08-08这一天的createtime为15288888888888 获取到 152888880000(代表20170808 00:00:00)当天开始的凌晨 getWeekbegin,getMonthgin 同上道理
1.活跃用户数
1.过去的五周(包含本周)某个app每周的周活跃用户数
2 注意,如果能够界定分区区间的话,务必要进行分区限定查询。
//20170501 ym/day/hm
//过去的五周,每周的活跃数
select formattime(createdatms,'yyyyMMdd',0) stdate, count(distinct deviceid) stcount
from ext_startup_logs
where concat(ym,day)>=formattime(getweekbegin(-4),'yyyyMMdd')
and appid ='sdk34734'
group by formattime(createdatms,'yyyyMMdd',0) ;
//2.最近的六个月(包含本月)每月的月活跃数。
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') ;
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 min(createdatms) > getdaybegin(-1)
) t
4.启动次数
// 4.1)今天app的启动次数
// 启动次数类似于活跃用户数,活跃用户数去重,启动次数不需要去重。
select count(*) from ext_startup_logs
where appid = 'sdk34734'
and ym = formattime(getdaybegin(),'yyyyMM')
and day = formattime(getdaybegin(),'dd');
5.版本分布
//5.1)今天appid为34734的不同版本的活跃用户数。
select appversion,count(distinct deviceid)
from ext_startup_logs where appid = 'sdk34734'
and ym = formattime(getdaybegin(),'yyyyMM')
and day = formattime(getdaybegin(),'dd')
group by appversion
//5.2)本周内每天各版本日活
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
6 用户构成分析
1.本周回流用户 上周未启动,本周启动了的用 必须当使用not in 子查询和后续查询都必须加入别名
select
distinct a.deviceid
from ext_startup_logs a
where a.appid = 'sdk34734' and concat(a.ym,a.day) >= formattime(getweekbegin(),'yyyyMMdd') and a.deviceid not in (
select
distinct t.deviceid
from ext_startup_logs t
where t.appid = 'sdk34734' and concat(t.ym,t.day) >= formattime(getweekbegin(-1),'yyyyMMdd') and concat(t.ym,t.day) < formattime(getweekbegin(),'yyyyMMdd')
)
2.连续活跃n周 连续三周活跃 2018101 20181008 20181016 去掉重有三次就是活跃
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
3.忠诚用户 连续活跃5周的
select deviceid , count(distinct(formattime(createdatms,'yyyyMMdd',0))) c
from ext_startup_logs
where appid = 'sdk34734'
and concat(ym,day) >= formattime(getweekbegin(-4),'yyyyMMdd')
group by deviceid
having c = 5
4.连续活跃用户 连续活跃n周
select deviceid , count(distinct(formattime(createdatms,'yyyyMMdd',0))) c
from ext_startup_logs
where appid = 'sdk34734'
and concat(ym,day) >= formattime(getweekbegin(-1),'yyyyMMdd')
group by deviceid
having c = 2
select distinct(a.deviceid)
from ext_startup_logs a
where concat(a.ym,a.day) < formattime(getweekbegin(-4),'yyyyMMdd')
and deviceid not in
(
select distinct(t.deviceid)
from ext_startup_logs t
where concat(t.ym,t.day)>=formattime(getweekbegin(-4),'yyyyMMdd')
)
7.近期流失用户
最近2、3、4都没有启动过app.
查询所有用户访问的时间的max,max不能落在
51 //四周内流失
52 select
53 distinct(deviceid)
54 from ext_startup_logs
55 where appid='#'
56 and concat(ym,day) >= formattime(getweekbegin(-4),'yyyyMMdd')
57 and concat(ym,day) < formattime(getweekbegin(-3),'yyyyMMdd')
58 and deviceid not in (
59 select
60 distinct(t.deviceid)
61 from ext_startup_logs t
62 where t.appid=''
63 and concat(t.ym,t.day) >= formattime(getweekbegin(-3),'yyyyMMdd')
64
65 )
66 union
67 //三周内流失
68 select
69 distinct(deviceid)
70 from ext_startup_logs
71 where appid='#'
72 and concat(ym,day) >= formattime(getweekbegin(-3),'yyyyMMdd')
73 and concat(ym,day) < formattime(getweekbegin(-2),'yyyyMMdd')
74 and deviceid not in (
75 select
76 distinct(t.deviceid)
77 from ext_startup_logs t
78 where t.appid=''
79 and concat(t.ym,t.day) >= formattime(getweekbegin(-2),'yyyyMMdd')
80
81 )
82 union
83 //两周内流失
84 select
85 distinct(deviceid)
86 from ext_startup_logs
87 where appid='#'
88 and concat(ym,day) >= formattime(getweekbegin(-2),'yyyyMMdd')
89 and concat(ym,day) < formattime(getweekbegin(-1),'yyyyMMdd')
90 and deviceid not in (
91 select
92 distinct(t.deviceid)
93 from ext_startup_logs t
94 where t.appid=''
95 and concat(t.ym,t.day) >= formattime(getweekbegin(-1),'yyyyMMdd')
96 )
8 留存分析
1.留存用户
周留存用户。上周新增的用户在本周还使用的
103 select
104 distinct(a.deviceid)
105 from ext_startup_logs a
106 where a.appid = 'sdk34734'
107 and concat(a.ym,a.day) >= formattime(getweekbegin(-1),'yyyyMMdd')
108 and concat(a.ym,a.day) < formattime(getweekbegin(),'yyyyMMdd')
109 and a.deviceid in (
110 select distinct(t.deviceid)
111 from (
112 select tt.deviceid , min(tt.createdatms) mintime
113 from ext_startup_logs tt
114 where tt.appid = 'sdk34734'
115 group by tt.deviceid having mintime >= getweekbegin(-2) and mintime < getweekbegin(-1)
116 ) t
117 )
9.用户的新鲜度
123 新鲜度 = 某段时间的新增用户数/某段时间的活跃的老用户数 .
124 //今天活跃用户
125
126 m = select count(distinct(t.deviceid))
127 from ext_startup_logs where concat(ym,day) = formattime(getdaybegin(),'yyyyMMdd') and appid = ... ;
128 //今天新增用户
129 n = select count(distinct(t.deviceid))
130 from (
131 select tt.deviceid , min(tt.createdatms) mintime
132 from ext_startup_logs tt
133 where tt.appid = 'sdk34734'
134 group by tt.deviceid having mintime >= getdaybegin(0)
135 ) t