应水友要求帮忙写的SQL
留存率:
某一个时段内,使用过该产品的用户,在未来时间段还使用的比率。
原来搞过一个hive的
http://blog.itpub.net/29254281/viewspace-2097338/
以我们的登录表为例
space_user_task_statistics_log 表 如果loginflag = 1 说明用户登录过产品.其中createDate 表示登录日期。
查询计算'2017-11-06' 至'2017-11-12' 登录过的用户,在后来6周的留存信息。
其中nums是数字辅助表
初始化数字辅助表
call pFastCreateNums(100000);
查询如下:
留存率:
某一个时段内,使用过该产品的用户,在未来时间段还使用的比率。
原来搞过一个hive的
http://blog.itpub.net/29254281/viewspace-2097338/
以我们的登录表为例
space_user_task_statistics_log 表 如果loginflag = 1 说明用户登录过产品.其中createDate 表示登录日期。
查询计算'2017-11-06' 至'2017-11-12' 登录过的用户,在后来6周的留存信息。
其中nums是数字辅助表
- create table nums(id int not null primary key);
-
- delimiter $$
- create procedure pCreateNums(cnt int)
- begin
- declare s int default 1;
- truncate table nums;
- while s<=cnt do
- insert into nums select s;
- set s=s+1;
- end while;
- end $$
- delimiter ;
-
- delimiter $$
- create procedure pFastCreateNums(cnt int)
- begin
- declare s int default 1;
- truncate table nums;
- insert into nums select s;
- while s*2<=cnt do
- insert into nums select id+s from nums;
- set s=s*2;
- end while;
- end $$
- delimiter ;
初始化数字辅助表
call pFastCreateNums(100000);
查询如下:
- select d.startdate ,d.enddate,
- count(
- distinct
- case when t2.createdate between d.startdate and d.enddate then t2.userid else null end
- ) as '周活'
- from
- (
- select distinct t1.userid,t1.createdate basedate,l.createdate from (
- SELECT
- log.userid, log.createDate
- FROM
- space_user_task_statistics_log log
- WHERE
- log.loginflag = 1
- AND log.createDate between '2017-11-06' and '2017-11-12'
- ) t1
- left join
- space_user_task_statistics_log l
- on(t1.userid=l.userid and l.createDate between '2017-11-06' and ('2017-11-06' + interval 6*7-1 day))
- -- order by t1.userid,t1.createdate,l.createdate
- ) t2
- left join
- (
- select
- '2017-11-06' + interval (id-1)*7 day startdate
- ,
- '2017-11-06' + interval (id)*7-1 day enddate
- from nums where id<=6
- ) d
- on(t2.createdate between d.startdate and d.enddate)
- group by d.startdate ,d.enddate
这个SQL我觉得已经接近最优了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-2149332/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-2149332/