[APP大数据项目]第五天笔记

查询某个app当前周每天新增用户数

SELECT formattime(t.mintime,'yyyy/MM/dd') stdate, count(*) stcount
FROM
(
SELECT deviceid, min(createdatms) mintime
FROM ext_startup_logs
GROUP BY deviceid
HAVING mintime>=getweekbegin() AND mintime < getweekbegin(1)
) t
GROUP BY  formattime(t.mintime,'yyyy/MM/dd')

用户分析

务必利用分区以避免全局扫描！

过去五周某个app的周活跃用户数

SELECT formattime(createdatms, 'yyyyMMdd', 0), count(distinct deviceid) stcount 
FROM ext_startup_logs
WHERE concat(ym,day)>= formattime(getweekbegin(-4),'yyyyMM') AND appid='sdk34734'
GROUP BY formattime(createdatms, 'yyyyMMdd', 0)

解释:

concat() 自带的字符串拼接函数
formattime(string,string,int) 获得某个时间当前周的第一天

1. formattime(getweekbegin(-4),'yyyyMM') 获得四周前的第一天的年月，通过和ym+day比较 限定分区
2. 根据每周第一天分区，count 计算分区内的数量

最近六个月的月活

SELECT formattime(createdatms, 'yyyyMM'), count(distinct deviceid) stcount 
FROM ext_startup_logs
WHERE ym>= formattime(getmonthbegin(-5),'yyyyMM') AND appid='sdk34734'
GROUP BY formattime(createdatms, 'yyyyMM')

沉默用户

用户仅在安装当天启动一次，后续经过一段时间内时间内都无启动行为
反映新增用户质量和app匹配程度
这里我们按下载后大于两天没再启动

SELECT COUNT(*)
FROM
(
SELECT deviceid, count(createdatms) dcount,min(createdatms) dmin
FROM ext_startup_logs
WHERE appid='sdk37434' 
AND dmin<getdaybegin(-1) 
AND dcount = 1 
) t;

版本分布

不同版本的用户分析
如 某app 不同版本的活跃用户数

当天某app 不同版本的活跃用户数

SELECT appversion, count(*)
FROM ext_startup_logs
WHERE appid = 'sdk34734'
AND ym = fomattime(getdaybegin(),'yyyyMM') 
AND day = formattime(getdaybegin(),'dd')
GROUP BY appversion

当前周每天的日活

SELECT formattime(createdatms, 'yyyyMMdd'), appversion, count(distinct deviceid)
FROM ext_startup_logs
WHERE concat(ym,day)>=formattime(getweekbegin(),'yyyyMMdd')
AND appid = 'sdk34734'
GROUP BY formattime(createdatms, 'yyyyMMdd') , version

用户构成分析

本周回流用户

上周未启动过应用，本周启动了应用的用户

SELECT distinct deviceid
FROM ext_startup_logs
WHERE appid='#' 
AND concat(ym,day) >= formattime(getweekbegin(),'yyyyMMdd')
AND deviceid NOT IN (
    SELECT distinct b.deviceid
    FROM ext_startup_logs b
    WHERE b.appid = '#'
    AND concat(b.ym,b.day) >= formattime(getweekbegin(-1),'yyyyMMdd')
    AND concat(b.ym,b.day) < formattime(getweekbegin(),'yyyyMMdd')
);

解释：
分别查出上周启动过的用户和本周启动过的用户，通过NOT IN 集合作差取到上周未启动，本周启动的用户
注意，子嵌套查询 需要加表别名，否则无法分辨列名到底属于哪一个表

连续活跃n周的用户

假如为3周

SELECT deviceid, count(distinct(formattime(getweekbegin(createdatms),'yyyyMMdd'))) c
FROM ext_startup_logs
WHERE appid = '#'
AND concat(ym,day)>=fomattime(getweekbegin(-2),'yyyyMMdd')
GROUP BY deviceid
HAVING c = 3

解释:
先按照deviceid分组，再把时间转为周起始时间，统计每个组里不同的周起始时间有几个

留存用户

对于一段时间内新增的用户，经过一段时间后仍在使用应用的用户
比如 周留存用户： 上上一周新增的用户 在上一周仍有启动应用的操作

SELECT distinct(deviceid)
FROM ext_startup_logs
WHERE appid='#'
AND concat(ym,day)>=formattime(getweekbegin(-1),'yyyyMMdd')
AND concat(ym,day)<formattime(getweekbegin(),'yyyyMMdd')
AND NOT IN (
    SELECT t.deviceid
    FROM
    (
        SELECT min(b.createdatms) mintime,b.deviceid
        FROM ext_startup_logs b
        WHERE b.appid='#'
        GROUP BY b.deviceid
        HAVING mintime>=getweekbegin(-2)
        AND mintime<getweekbegin(-1)
    )t
)

解释：
拿出上周启动过的用户，然后再和上上周的新增用户比较，交集即为留存用户

新鲜度

某段时间的新增用户数 / 某段时间活跃的老用户
比如某日的新鲜度

m = 
SELECT count(*) FROM(
SELECT min(createdatms) mintime,deviceid
FROM ext_startup_logs
WHERE appid = '#'
GROUP BY deviceid
HAVING mintime>getdaybegin()
)t;

n = 
SELECT count(distinct deviceid)
FROM ext_startup_logs
WHERE appid='#'
AND concat(ym,day) = formattime(getdaybegin(),'yyyyMMdd')

n/(m-n)