03-04 SQL查询练习

1、用户信息表
需查询返回下：

注：该手机厂商想要分析应用中相机的活跃情况，需统计如下信息：某日活跃用户在后续一周内的留存情况（计算次日留存用户数，3日留存用户数，7日留存用户数）

①某日活跃用户

解析：
某日- select 登陆时间
用户数- count（用户id），又有用户一日启动登陆多次，需去重
应用为 相机 -where 应用= ‘相机’
又返回的0501 ~0510 ，以日期分组-group by= 登陆时间

select 登陆时间，count( distinct 用户 id）
from 用户信息表
where 应用名称=' 相机'
group by 登陆时间；

②次日留存用户

即前后两天都有登陆的用户，即时间间隔为1. （单位 : day）

另注：当表只有一个且涉及时间间隔时，就绪要用到自联结,先求出两张视图，然后做时间差再选取差值=1的。

select a.用户id,a.登陆时间 as a_t , b.用户id,b.登陆时间 as b_t
from 用户信息表 as a 
left join 用户信息表 as b
on a.用户id= b.用户id
where 应用名称= '相机';

将上述临时表记为c，然后从中做出差值选择

select * ,timestampdiff（day，a_t,b_t） as 时间间隔
from c

则次日留存用户为 时间间隔为1的用户

select * ，count( distinct case when 时间间隔 =1 then else 用户id nullend ） as 次日留存数
from 
	(select * ，timestampdiff( day,a_t,b_t） as 时间间隔
	from c ）
group by a_t；

③ 次日留存率
次日留存率= 次日留存数/当日用户数

select * ,count(distinct case when 时间间隔=1 then 用户id else nullend）/count( distinct 用户id ） as 次日留存率
from 
	(select * ，timestampdiff( day,a_t,b_t） as 时间间隔
	from c ）
group by a_t；

总体合起来就是：

select * ,count(distinct case when 时间间隔=1 then 用户id else nullend）/count( distinct 用户id ） as 次日留存率
from 
	(select * ，timestampdiff( day,a_t,b_t） as 时间间隔
	from 
		(select a.用户id,a.登陆时间 as a_t , b.用户id,b.登陆时间 as b_t
		from 用户信息表 as a 
		left join 用户信息表 as b
		on a.用户id= b.用户id
		where 应用名称= '相机') as c 
	) as d 
group by a_t；

④至于 3日 ，7日留存率 就是时间间隔等于3或7