MySQL:「学習ノートと実戦」の検索例(1)通過率を求める

一、通過率を求める
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   ;