获取7日留存率

实例所用数据：https://download.youkuaiyun.com/download/weixin_46623003/19011782

关键词：导入csv文件、定义变量、创建临时表 

一、数据

-- 新建表，导入CSV数据
CREATE TABLE IF NOT EXISTS user (
  user_id VARCHAR(10) NOT NULL ,
  date_user DATE DEFAULT NULL,
  money INT(10) DEFAULT NULL
  );

load data local infile "D:/Retention.csv" into table user		-- 注意，文件名不能有中文字符；下面行之间没有逗号隔开
CHARACTER SET UTF8  -- 字符编码，某些情况可以省略
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'  -- 双引号之间表示一个字段的内容（防止内容中因有分隔符而分段出错），某些情况可以省略
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES

二、处理

1、分步进行

--1 目标日所有的用户情况，存储到临时表：temp1
	DROP TABLE IF EXISTS temp1;
	CREATE TEMPORARY TABLE temp1
	select date_user as target_date, user_id as uid1
	from user
	where DATE_user ='2020-01-01';
	
	--目标日所有用户数，存储到变量@temp
	SELECT @temp := COUNT(uid1) FROM temp1 GROUP BY target_date
	
	
--2 7日内的用户情况，存储到临时表：temp2
	DROP TABLE IF EXISTS temp2;
	CREATE TEMPORARY TABLE temp2
	select date_user, user_id as uid2, DATEDIFF(date_user,'2020-01-01') as gap
	from user
	where DATEDIFF(date_user,'2020-01-01') <=7;
	
--3 7日内留存率
	SELECT temp1.target_date, temp2.gap, COUNT(uid1) as retention_num, COUNT(*)/@temp as retention_rate
	FROM temp1 LEFT JOIN temp2
	ON temp1.uid1 = temp2.uid2
	GROUP BY temp1.target_date, temp2.gap;

temp1:

temp2:

result:

2、合并进行

-- 目标日所有用户数，存储到变量@temp
with z as(
select date_user, count(distinct user_id) as day0_UV 
from user 
where date_user = '2020-01-01' 
group by date_user)

SELECT day0_UV FROM z INTO @temp;
-- SELECT @temp := day0_UV FROM z;

-- 计算结果
SELECT temp1.target_date, temp2.gap, COUNT(uid1) as retention_num, COUNT(*)/@temp retention_rate
FROM (
	select date_user as target_date, user_id as uid1
	from user
	where DATE_user ='2020-01-01'
	) temp1 
LEFT JOIN(
	select date_user, user_id as uid2, DATEDIFF(date_user,'2020-01-01') as gap
	from user
	where DATEDIFF(date_user,'2020-01-01') <=7
	) temp2
ON temp1.uid1 = temp2.uid2
GROUP BY temp1.target_date, temp2.gap;

结果：

三、参考

1、MySQL中的变量定义与赋值：https://blog.youkuaiyun.com/qq_35495339/article/details/89160610

2、MySQL数据库中临时表的创建：https://blog.youkuaiyun.com/shuizhongyue_/article/details/84710345

3、MySQL 导入导出 CSV 文件：https://blog.youkuaiyun.com/kikajack/article/details/80529640