实例所用数据:https://download.csdn.net/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.csdn.net/qq_35495339/article/details/89160610
2、MySQL数据库中临时表的创建:https://blog.csdn.net/shuizhongyue_/article/details/84710345
3、MySQL 导入导出 CSV 文件:https://blog.csdn.net/kikajack/article/details/80529640