场景一
邮件表
记录了某邮件App的数据，包括：编号（主键）、寄信人编号、收信人编号、枚举类型（completed表示邮件发送成功，no_completed表示邮件发送失败）
创建表格
create table 邮件表
(id int not null,
sender_id int not null,
receiver_id int not null,
type varchar(20) not null,
date datetime not null)
插入数据
insert into 邮件表 values
(1,2,3,'completed','2020-01-11'),
(2,1,3,'completed','2020-01-11'),
(3,1,4,'uncompleted','2020-01-11'),
(4,3,1,'completed','2020-01-12'),
(5,3,4,'completed','2020-01-12'),
(6,4,1,'completed','2020-01-12')
用户表
里记录了该邮件App的所有用户信息。其中，用户编号为主键，是否为黑名单（值为0表示是正常用户，值为1表示是黑名单用户）
创建表格
create table 用户表
(user_id int not null,
feature int )
插入数据
insert into 用户表 values
(1,0),
(2,1),
(3,0),
(4,0);
– 每一个日期里，正常用户发送给正常用户邮件失败的概率是多少？
select a.date,
sum(case when type='completed' then 1 else 0 end)/count(type) as rate
from 邮件表 a
left join 用户表 b
on a.sender_id=b.user_id
left join 用户表 c
on a.receiver_id=c.user_id
where b.feature=0 and c.feature=0
group by a.date
场景二
-1用户活跃模型表
create table tmp_liujg_dau_based
(
imp_date varchar(20) not null comment '日期',
qimei varchar(20) not null comment '用户唯一标识',
is_new varchar(10) comment '新用户表示，1表示新用户，0表示老用户',
primary key(imp_date,qimei));
ALTER TABLE tmp_liujg_dau_based COMMENT '用户活跃模型表';
用户活跃模型表数据导入
insert into tmp_liujg_dau_based values
('20190301','001','0'),
('20190301','002','0'),
('20190301','003','1'),
('20190301','004','1'),
('20190301','005','1'),
('20190301','006','1'),
('20190302','001','0'),
('20190302','002','0'),
('20190302','003','0'),
('20190302','005','0'),
('20190302','006','0'),
('20190302','007','1'),
('20190303','005','0'),
('20190303','006','0'),
('20190303','007','0'),
('20190303','008','1'),
('20190303','009','1'),
('20190303','010','1'),
('20190401','008','0'),
('20190401','009','0'),
('20190401','010','0'),
('20190401','011','1'),
('20190401','012','1'),
('20190402','009','0'),
('20190402','010','0'),
('20190402','011','0'),
('20190402','012','0'),
('20190402','013','1'),
('20190402','014','1'),
('20190501','001','0'),
('20190501','002','0'),
('20190501','008','0'),
('20190501','007','0'),
('20190501','015','1'),
('20190501','016','1'),
('20190501','017','1'),
('20190501','018','1'),
('20190601','008','0'),
('20190601','017','0'),
('20190601','018','0'),
('20190601','019','1'),
('20190601','020','1'),
('20190601','021','1'),
('20190601','022','1'),
('20190603','021','0'),
('20190603','022','0'),
('20190603','011','0'),
('20190603','012','0'),
('20190603','023','1'),
('20190701','023','0'),
('20190701','008','0'),
('20190701','011','0'),
('20190701','022','0'),
('20190701','012','0'),
('20190701','024','1'),
('20190701','025','1'),
('20190701','026','1'),
('20190701','027','1'),
('20190705','026','0'),
('20190705','027','0'),
('20190705','009','0'),
('20190705','010','0'),
('20190705','028','1'),
('20190705','029','1');
–2红包参与领取模型表
create table tmp_liujg_packed_based
(
imp_date varchar(20) comment '日期',
report_time varchar(20) comment '领取时间戳',
qimei varchar(20) not null comment '用户唯一标识',
add_money varchar(20) not null comment '领取金额，单位为分');
ALTER TABLE tmp_liujg_packed_based COMMENT '红包参与领取模型表';
红包参与领取模型表数据导入
insert into tmp_liujg_packed_based values
('20190301','2019/03/01 10:15:01','001','1.05')
,('20190301','2019/03/01 13:15:01','001','2.30')
,('20190301','2019/03/01 11:15:01','002','0.80')
,('20190301','2019/03/01 17:15:01','002','0.89')
,('20190301','2019/03/01 14:15:01','003','2.12')
,('20190301','2019/03/01 18:15:01','003','1.12')
,('20190301','2019/03/01 17:15:01','005','1.12')
,('20190301','2019/03/01 19:15:01','005','0.12')
,('20190301','2019/03/01 09:15:01','006','0.98')
,('20190301','2019/03/01 11:15:01','006','1.45')
,('20190302','2019/03/02 09:30:01','001','0.78')
,('20190302','2019/03/02 19:30:01','001','0.88')
,('20190302','2019/03/02 10:30:01','003','0.68')
,('20190302','2019/03/02 09:30:01','005','1.01')
,('20190302','2019/03/02 14:30:01','005','1.88')
,('20190302','2019/03/02 14:30:01','006','1.88')
,('20190302','2019/03/02 10:30:01','006','0.68')
,('20190302','2019/03/02 15:30:01','007','0.68')
,('20190302','2019/03/02 16:30:01','007','1.78')
,('20190303','2019/03/03 16:30:01','005','0.68')
,('20190303','2019/03/03 08:50:01','006','0.32')
,('20190303','2019/03/03 10:50:01','006','1.78')
,('20190303','2019/03/03 10:50:01','007','0.32')
,('20190303','2019/03/03 15:50:01','008','1.01')
,('20190303','2019/03/03 17:50:01','008','1.68')
,('20190303','2019/03/03 10:30:01','010','1.88')
,('20190303','2019/03/03 15:50:01','010','0.32')
,('20190401','2019/04/01 09:50:00','008','0.18')
,('20190401','2019/04/01 11:50:00','009','0.88')
,('20190401','2019/04/01 12:35:00','009','0.32')
,('20190401','2019/04/01 17:50:00','010','1.01')
,('20190401','2019/04/01 12:35:00','011','1.68')
,('20190401','2019/04/01 12:50:00','011','0.88')
,('20190401','2019/04/01 15:50:00','012','0.32')
,('20190401','2019/04/01 17:50:00','012','1.68')
,('20190401','2019/04/01 12:35:00','012','1.88')
,('20190402','2019/04/02 09:50:00','009','0.18')
,('20190402','2019/04/02 19:50:00','009','1.18')
,('20190402','2019/04/02 17:50:00','010','0.88')
,('20190402','2019/04/02 16:50:00','010','0.32')
,('20190402','2019/04/02 19:50:00','010','0.32')
,('20190402','2019/04/02 16:50:00','013','0.88')
,('20190402','2019/04/02 19:55:00','013','0.88')
,('20190402','2019/04/02 20:55:00','013','1.01')
,('20190402','2019/04/02 19:55:00','014','0.32')
,('20190402','2019/04/02 20:55:00','014','1.01')
,('20190501','2019/05/01 09:50:00','001','1.18')
,('20190501','2019/05/01 09:55:00','002','0.32')
,('20190501','2019/05/01 10:55:00','002','0.32')
,('20190501','2019/05/01 10:00:00','007','0.88')
,('20190501','2019/05/01 10:55:00','015','0.88')
,('20190501','2019/05/01 10:00:00','015','0.32')
,('20190501','2019/05/01 14:00:00','017','1.01')
,('20190501','2019/05/01 15:00:00','017','1.01')
,('20190501','2019/05/01 15:30:00','018','0.88')
,('20190501','2019/05/01 16:30:00','018','0.68')
,('20190601','2019/06/01 09:50:00','008','1.38')
,('20190601','2019/06/01 14:50:00','017','0.88')
,('20190601','2019/06/01 11:50:00','019','1.01')
,('20190601','2019/06/01 13:50:00','019','0.88')
,('20190601','2019/06/01 14:50:00','019','0.68')
,('20190602','2019/06/02 09:50:00','021','0.38')
,('20190602','2019/06/02 13:50:00','012','0.88')
,('20190602','2019/06/02 15:50:00','012','1.12')
,('20190602','2019/06/02 13:59:00','023','0.88')
,('20190602','2019/06/02 15:50:00','023','1.01')
,('20190701','2019/07/01 09:50:00','023','0.38')
,('20190701','2019/07/01 13:50:00','023','0.78')
,('20190701','2019/07/01 14:50:00','008','0.68')
,('20190701','2019/07/01 14:50:00','024','0.68')
,('20190701','2019/07/01 15:50:00','024','1.68')
,('20190701','2019/07/01 14:50:00','026','0.68')
,('20190701','2019/07/01 18:50:00','026','1.68')
,('20190701','2019/07/01 14:50:00','027','0.68')
,('20190701','2019/07/01 19:35:00','027','1.11')
,('20190702','2019/07/02 09:50:00','026','0.78')
,('20190702','2019/07/02 11:50:00','026','0.78')
,('20190702','2019/07/02 13:50:00','028','1.01')
,('20190702','2019/07/02 14:35:00','028','0.88')
,('20190702','2019/07/02 15:35:00','028','0.33')
查看数据
select * from test.tmp_liujg_dau_based
order by qimei
select * from test.tmp_liujg_packed_based
order by qimei
计算2019年6月1日至今，每日DAU（活跃用户量，即有登陆的用户）
select imp_date,count(qimei) as cnt from test.tmp_liujg_dau_based
where imp_date>'20190601'
group by imp_date
计算20190601至今，每日领取红包的新用户数，老用户数，及人均领取金额，人均领取次数
Select
t. imp_date,
t.is_new,
count(distinct t.qimei) 领取红包人数,
sum(t.add_money)/count(distinct t.qimei) 人均领取金额,
count(t.report_time)/count(distinct t.qimei) 人均领取次数
from
(Select a. imp_date,
a.qimei,
a.add_money,
a.report_time,
Case when b.is_new = 1 then '新用户'
when b.is_new = 0 then '老用户'
else '领取红包但未登陆'end is_new
from
tmp_liujg_packed_based a
Left join
tmp_liujg_dau_based b
on a.imp_date = b.imp_date
and a.qimei = b.qimei
where a.imp_date > '20190601'
)t
Group by t.imp_date,t.is_new
计算取每个月领取红包的用户数，人均领取金额，人均领取次数
Select
left(imp_date,6) 月份,
count(distinct imp_date) 每月领取天数
count(distinct qimei) 每月用户数,
sum(add_money)/count(distinct qimei) 每月人均领取金额,
count(report_time)/count(distinct qimei) 每月人均领取次数
from
tmp_liujg_packed_based
where imp_date >= '20190301'
Group by left(imp_date,6)
版权声明：本文为LCS198903原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。