SQL：结构化查询语言(Structured Query Language)
留存率
现有一表Retention如下
| id |
name |
register_time |
logout_time |
| 101 |
Anna |
2018-01-01 00:00:01.000 |
2018-01-02 00:00:01.003 |
| … |
… |
… |
… |
register_time表示用户注册时间，logout_time表示用户注销时间。现要计算每一日的用户次日留存率（假如1号有5人注册，这5人有2人在2号注销，则1号当天的次日留存率为3/5=60%）
1、建表
-
CREATE TABLE Retention
-
(
-
id int NOT NULL PRIMARY KEY,
-
name nvarchar(50) NOT NULL,
-
register_timedatetime NOT NULL,
-
logout_timedatetime NOT NULL,
- );
|
2、插入数据
-
INSERT INTO Retention
-
VALUES (101, 'Anna', CONVERT(datetime,2018-01-01 00:00:01', 20), CONVERT(datetime,2018-01-01 00:00:01', 20));
- …
|
3、更新数据
-
UPDATE Retention
-
SET logout_time = convert(datetime, 2018-01-02 00:00:02.003', 20)
-
WHERE id=101;
|
4、计算留存率
当前表中数据如下：
SQL代码如下：
-
SELECT new.register_day AS '日期',
-
new.cnt AS '当日注册数',
-
out.cnt AS '次日留存数',
-
CAST(out.cnt*100.0/new.cnt AS varchar(20))+'%' AS '留存率'
-
FROM (SELECT COUNT(1) as cnt, CONVERT(nvarchar(20),register_time,111) AS register_day
-
FROM Retention
-
GROUP BY CONVERT(nvarchar(20),register_time,111)
-
) AS new
-
JOIN
-
(SELECT COUNT(1) as cnt, CONVERT(nvarchar(20),register_time,111) AS register_day
-
FROM Retention
-
where CONVERT(nvarchar(20),logout_time,111) > CONVERT(nvarchar(20),DATEADD(DAY,1,register_time),111)
-
GROUP BY CONVERT(nvarchar(20),register_time,111)
-
) AS out
-
ON new.register_day=out.register_day
|
计算结果：
这个方法好麻烦，有没有简单一点的方法/(ㄒoㄒ)/~~
5、删除表
