【SQL】运营数据计算(DAU、留存)

运营数据计算(DAU、留存)



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:

iduiddt
014662017-01-07 18:24:55
024582017-01-07 18:25:18
034582017-01-07 18:26:21
045922017-01-07 19:09:59
053932017-01-08 00:41:19
065922017-01-08 09:14:27
075922017-01-08 11:18:08
086362017-01-08 11:27:24
094582017-01-09 11:13:12
104582017-01-09 11:34:02
114132017-01-09 12:29:25
124132017-01-09 12:40:39
133932017-01-09 16:22:48
145922017-01-09 16:31:07
155922017-01-10 10:55:06
164582017-01-10 12:14:11
174582017-01-10 12:33:53
184622017-01-10 12:34:59
194132017-01-10 15:22:45
204662017-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:

dttDAU
2017-01-073
2017-01-083
2017-01-094
2017-01-105


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:

ddtDAUnextday_retone_day_ret
2017-01-073133%
2017-01-083266%
2017-01-094375%
2017-01-10400%

注意1:最后一天的存留率无法知道

注意2:条件语句

substr(b.dt,9,2)-substr(a.dt,9,2) = 1 

可以替换为

date_add(substr(a.ts, 1, 10), INTERVAL 1 day) = substr(b.ts, 1, 10)



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

相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页