一、求通过率
1.数据源如下
oederid | is old | createtime | state |
o123 | 1 | 2018-6-1 10:25 | 20 |
o124 | 1 | 2018-6-2 1:00 | 20 |
o125 | 1 | 2018-6-1 16:00 | 20 |
o126 | 0 | 2018-6-2 6:07 | 20 |
o127 | 0 | 2018-6-1 3:00 | 20 |
o128 | 0 | 2018-6-2 0:10 | 10 |
o129 | 1 | 2018-7-1 9:00 | 20 |
o130 | 0 | 2018-7-1 0:33 | 10 |
o131 | 0 | 2018-7-1 20:00 | 10 |
o132 | 1 | 2018-7-1 10:00 | 20 |
o133 | 1 | 2018-7-1 0:11 | 20 |
o134 | 0 | 2018-7-3 2:00 | 20 |
o135 | 0 | 2018-7-3 20:00 | 20 |
o136 | 1 | 2018-7-3 19:00 | 10 |
0:新用户 | 10:未通过 | ||
1:老用户 | 20:通过 |
2.报表格式
报表格式 | |||
日期 | 新用户通过率 | 老用户通过率 | 总通过率 |
2018-6-1 | |||
2018-6-2 | |||
3.sql 代码
select dt,sum(new_user) as new_user_no,sum(old_user) as old_user_no,sum(user) as user_no,count(orderid) as all_user,
concat(round(sum(new_user)/count(orderid)
*100,2),'%') as new_user_rate,
concat(round(sum(old_user)/count(orderid)*100,2),'%') as old_user_rate,
concat(round(sum(user)/count(orderid)*100,2),'%') as user_rate
from
(select date(createtime) as dt,
case when isold=1 and state=20 then 1 else 0 end as old_user,
case when isold=0 and state=20 then 1 else 0 end as
new_user,
case when state=20 then 1 else 0 end as user,orderid,state from a1 )tmp
group by dt;
#程老师给的代码 只提供了思路 结果是错的
select DATE_FORMAT(ctime,'%m月%d日') as 日期,sum(score-10)/count(score)*10 as 总通过率 from a group by 日期
select DATE_FORMAT(t0.ctime,'%m月%d日') as 日期,sum(case when t0.score=20 then 1 else 0 end)/count(t0.score) as 新用户通过率 from (SELECT * from a where old='0') as t0
group by 日期;
select DATE_FORMAT(t1.ctime,'%m月%d日') as 日期,sum(case when t1.score=20 then 1 else 0 end)/count(t1.score) as 老用户通过率 from (SELECT * from a where old='1') as t1
group by 日期;