【SQL】營運資料計算（DAU、留存）

營運資料計算（DAU、留存）

• • • 1 計算DAU日活
    • 2 計算留存率：次日留存

1 計算DAU日活

建立訂單表

create table SC (id varchar(10),uid varchar(10),dt datetime(10));
insert into SC values ('01' , '466' , '2017-01-07 18:24:55');
insert into SC values ('02' , '458' , '2017-01-07 18:25:18');
insert into SC values ('03' , '458' , '2017-01-07 18:26:21');
insert into SC values ('04' , '592' , '2017-01-07 19:09:59');
insert into SC values ('05' , '393' , '2017-01-08 00:41:19');
insert into SC values ('06' , '592' , '2017-01-08 09:14:27');
insert into SC values ('07' , '592' , '2017-01-08 11:18:08');
insert into SC values ('08' , '636' , '2017-01-08 11:27:24');
insert into SC values ('09' , '458' , '2017-01-09 11:13:12');
insert into SC values ('10' , '458' , '2017-01-09 11:34:02');
insert into SC values ('11' , '413' , '2017-01-09 12:29:25');
insert into SC values ('12' , '413' , '2017-01-09 12:40:39');
insert into SC values ('13' , '393' , '2017-01-09 16:22:48');
insert into SC values ('14' , '592' , '2017-01-09 16:31:07');
insert into SC values ('15' , '592' , '2017-01-10 10:55:06');
insert into SC values ('16' , '458' , '2017-01-10 12:14:11');
insert into SC values ('17' , '458' , '2017-01-10 12:33:53');
insert into SC values ('18' , '462' , '2017-01-10 12:34:59');
insert into SC values ('19' , '413' , '2017-01-10 15:22:45');
insert into SC values ('20' , '466' , '2017-01-10 19:12:47');
           

Output:

id	uid	dt
01	466	2017-01-07 18:24:55
02	458	2017-01-07 18:25:18
03	458	2017-01-07 18:26:21
04	592	2017-01-07 19:09:59
05	393	2017-01-08 00:41:19
06	592	2017-01-08 09:14:27
07	592	2017-01-08 11:18:08
08	636	2017-01-08 11:27:24
09	458	2017-01-09 11:13:12
10	458	2017-01-09 11:34:02
11	413	2017-01-09 12:29:25
12	413	2017-01-09 12:40:39
13	393	2017-01-09 16:22:48
14	592	2017-01-09 16:31:07
15	592	2017-01-10 10:55:06
16	458	2017-01-10 12:14:11
17	458	2017-01-10 12:33:53
18	462	2017-01-10 12:34:59
19	413	2017-01-10 15:22:45
20	466	2017-01-10 19:12:47

計算DAU

原則：按日分組，統計每日不重複客戶數量

select substr(dt, 1, 10) as dtt, 
    count(distinct uid ) as DAU 
    from SC 
    group by substr(dt, 1, 10); 
           

Output:

dtt	DAU
2017-01-07	3
2017-01-08	3
2017-01-09	4
2017-01-10	5

2 計算留存率：次日留存

  • 用表合并的方式構造一階滞後的new col，然後用new col除以old col得到比率

  • 通過構造一階滞後的new column，巧妙地避免了循環

select substr(a.dt, 1,10) as ddt,
    count(distinct a.uid) as DAU,          --當日活躍使用者數A
    count(distinct b.uid) as nextday_ret,  --次日留存使用者數B
    (count(distinct b.uid) * 100 / count(distinct a.uid)) || '%' as one_day_ret  --次日留存率=B/A
    from SC a
    left join
    SC b
    on a.uid = b.uid and substr(b.dt,9,2)-substr(a.dt,9,2) = 1 --條件：滞後一天合并
    group by substr(a.dt, 1,10) ;
           

Output:

ddt	DAU	nextday_ret	one_day_ret
2017-01-07	3	1	33%
2017-01-08	3	2	66%
2017-01-09	4	3	75%
2017-01-10	4	0%

注意1：最後一天的存留率無法知道

注意2：條件語句

可以替換為

參考

https://cloud.tencent.com/developer/article/1587655