游戏类常用三日次留查询SQL

发布时间：2019-07-25 04:38查看次数：3752

游戏类常用三日次留查询SQL

名词解释: 

表名字:account  用户注册账号表

表名字:log_user_day 用户每天登陆表

diff0    当日留存

diff1    1日

diff3    3日

diff7     7日 

SQL语句讲解:


第一步: 先到账号表查询出来注册日期在1到30号的

SELECT t.created_at as sumdata,t.openid as new_user_openid FROM account as t where t.created_at BETWEEN "2019-07-01" and "2019-07-30"

第二步: 在登陆表里边查找这个时间段内登陆日志

SELECT * FROM log_user_day as a,(SELECT t.created_at as sumdata,t.openid as new_user_openid FROM account as t where t.created_at BETWEEN "2019-07-01" and "2019-07-30") as b
 WHERE a.openid = b.new_user_openid GROUP BY b.sumdata,a.openid,a.loginday

第三步:在结果集内查找日期 等于0 1 3 7 登陆的信息

SELECT daylist.loginday, daylist.openid,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 0,1,0) as diff0,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 1,1,0) as diff1,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 3,1,0) as diff3,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 7,1,0) as diff7
FROM (SELECT * FROM log_user_day as a,(SELECT t.created_at as sumdata,t.openid as new_user_openid FROM account as t where t.created_at BETWEEN "2019-07-01" and "2019-07-30") as b
 WHERE a.openid = b.new_user_openid GROUP BY b.sumdata,a.openid,a.loginday) as daylist

最终生成SQL:计算 当前登陆  次日登陆  3日登陆 7日登陆

SELECT loginday,SUM(diff0) as diff0,SUM(diff1) as diff1,SUM(diff3) as diff3,SUM(diff7) as diff7  FROM
( SELECT daylist.loginday, daylist.openid,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 0,1,0) as diff0,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 1,1,0) as diff1,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 3,1,0) as diff3,
if (DATEDIFF(daylist.loginday,daylist.sumdata) = 7,1,0) as diff7
FROM (SELECT * FROM log_user_day as a,(SELECT t.created_at as sumdata,t.openid as new_user_openid FROM account as t where t.created_at BETWEEN "2019-07-01" and "2019-07-30") as b
 WHERE a.openid = b.new_user_openid GROUP BY b.sumdata,a.openid,a.loginday) as daylist) as logdiff 
 GROUP BY loginday

这个SQL 知识点在与 MySQL  

DATEDIFF(日期1,日期2)   计算日期间隔 
if(1=1,1,0) 返回结果
就算复制粘贴百度资料  也要一定消化成自己的知识累计~~

2019年7月25日14:11:56  深圳宝安  德玛西亚