SQL MySQL和Sql Server的留存率及留存人数计算查询语句

最新推荐文章于 2024-07-28 06:12:33 发布

N老师

最新推荐文章于 2024-07-28 06:12:33 发布

阅读量4.1k

点赞数 10

分类专栏： SQL 文章标签： mysql sql sqlserver 数据库数据分析

本文链接：https://blog.csdn.net/kaho1996/article/details/106294561

版权

本文针对SQL数据库中的留存率和留存人数计算，总结了多种查询语句，覆盖MySQL和SQL Server，包括直接使用datediff函数、datetime加N天的方法。同时，文章提出并优化了查询效率低下的问题，提供了优化后的查询语句，提升运行效率约3倍。此外，还介绍了数据表结构和留存人数的两种定义。

摘要由CSDN通过智能技术生成

#SQL MySQL和Sql Server留存率及留存人数计算

本文作者：
第一作者：负责MySQL的赵芮萱（下文称赵老师）
第二作者：负责Sql Server的叶嘉浩（下文称叶同学）

目前在墙外的Stackoverflow和墙内的CSDN BLOGs上都有不少实现留存率计算的文章，但是绝大部分此类文章存在3个问题：
1）查询语句思路单一，缺乏多个实现方法总结。
2）内容只涵盖了MySQL或者Sql Server，缺乏对两种Sql 工具的查询语句总结或者通用查询语句。
3）查询语句只能满足业务需求，无法满足效率需求，缺乏优化代码的思考。
下文将针对此3个问题展示留存率及留存人数查询语句。

数据表简介：
数据表名称为浏览明细表，该表有2个字段。
第1个字段为uid varchar(20)，第2个字段为登陆日期时间(datetime)。
详细数据在文末。

一、留存人数计算
**1.1）第一种留存人数定义：**新增用户在第1天登录且在第N天登录。例如，3日留存用户数定义为在第1天作为新增用户登录且在第3天登录，至于第2天登录与否不考虑。
1.1.1）第1种写法，直接使用datediff函数=N。

CREATE VIEW a as 
	(select uid,min(logtime) as first_logtime from 浏览明细表 group by uid)
create view U as
	(select distinct(logtime) from 浏览明细表)
select U.logtime, 
	count(distinct a.uid)as 新增数,
	count(distinct b.uid)as 次留数,
	count(distinct c.uid)as 三日留数,
	count(distinct d.uid) as 七日留数
from  U 
	left join a on U.logtime=a.first_logtime 
	left join 浏览明细表 b on datediff(dd,a.logtime,b.logtime)=1 and b.uid=a.uid 
	left join 浏览明细表 c on datediff(dd,a.logtime,b.logtime)=2 and c.uid=a.uid 
	left join 浏览明细表 d on datediff(dd,a.logtime,b.logtime)=6 and d.uid=a.uid 
group by U.logtime

1.1.2）第2种写法，使用datetime+N的写法。

CREATE VIEW a as 
	(select uid,min(logtime) as first_logtime from 浏览明细表 group by uid)
create view U as
	(select distinct(logtime) from 浏览明细表)
select U.logtime, 
	count(distinct a.uid)as 新增数,
	count(distinct b.uid)as 次留数,
	count(distinct c.uid)as 三日留数,
	count(distinct d.uid) as 七日留数
from  U 
	left join a on U.logtime=a.first_logtime 
	left join 浏览明细表 b on b.logtime=a.first_logtime+1 and b.uid=a.uid 
	left join 浏览明细表 c on c.logtime=a.first_logtime+2 and c.uid=a.uid 
	left join 浏览明细表 d on d.logtime=a.first_logtime+6 and d.uid=a.uid 
group by U.logtime

用上述两种算法写出来的答案应该如下：
在这里插入图片描述
**1.2）第二种留存人数定义：**新增用户在第1天登录且在第2到N天之间任意一天登录。例如，3日留存用户数定义为在第1天作为新增用户登录且在第2或第3天任意一天登录。此种写法和上述2种写法不同之处在于每句left join语句结合了2句datediff函数语句。

CREATE VIEW a as 
	(select uid,min(logtime) as first_logtime from 浏览明细表 group by uid)
create view U as
	(select distinct(logtime) from 浏览明细表)
select U.logtime, 
	count(distinct a.uid)as 新增数,
	count(distinct b.uid)as 次留数,
	count(distinct c.uid)as 三日留数,