一、订单呼叫
1.1 订单应答率----会做
select sum(wd)/count(1) from ( select (case when year(grab_time) = 1970 then 0 else 1 end) wd from didi_order_rcd ) a
讯享网
1.3 呼叫量最高的是哪一个小时?
分组不是按照年月日小时分组,只是单纯的按照小时一组
讯享网select hour(call_time) as t,count(1) from didi_order_rcd group by 1
1.4 第二天继续呼叫的比例为?
即次日留存率,同时记得去重
select count(a2.cust_id)/count(a1.cust_id) from (select distinct cust_id,call_time from didi_order_rcd) a1 left join (select distinct cust_id,call_time from didi_order_rcd) a2 on a1.cust_id=a2.cust_id and a1.call_time = date_sub(a2.call_time,interval 1 day)
1.5 哪个小时的呼叫应答时间最短?
讯享网select hour(call_time), avg(TIMESTAMPDIFF(second,call_time, grab_time)) from didi_order_rcd where year(grab_time)<>1970 group by hour(call_time)
二、货运订单


2.1用车方和司机被禁止(banned=1)的比率分别为?(保留两位小数)–会做
select role,round(sum(banned)/count(1),2) from hll_t2 group by role
2.2 每天的订单完成率
case when 比较繁琐
讯享网select order_dt,sum(bq)/count(1) from ( select *,(case when status=completed then 1 else 0) bq from hll_t1 ) a group by order_dt
if(条件,满足输出,不满足输出) 有时候if更加方便
status=‘completed’ 引号
Select order_dt, sum(if(status='completed',1,0))/count(1) from hll_t1 group by order_dt
2.3 用车至少两次,且主动取消过至少1次的用车方有多少名?
讯享网select usr_id from (select *,(case when status='cancel_by_usr' then 1 else 0 end) bq from hll_t1) a group by usr_id having count(1)>=2 and sum(bq) >=1
select count(a.usr_id) from (select usr_id, count(1) from hll_t1 group by usr_id having count(1) >=2) a --限制用车至少两次 inner join (select usr_id, count(1) from hll_t1 where status= 'cancel_by_usr' --限制主动取消至少 1 次,不要写成 canel,必须是 cancel_by_usr group by usr_id having count(1)>=1)b on a.usr_id =b.usr_id
2.4 北京、上海的非禁止用户的用车取消率分别为?(要求输出结果保留两位小数)
sum(if(status=‘cancel_by_usr’,1,0))的用法
讯享网select a.cty,sum(if(status='cancel_by_usr',1,0))/count(1) from (select * from hll_t1 t1 left join hll_t2 t2 on t1.usr_id = t2.usr_id where t1.sty in ('北京','上海') and t2.banned = 0) a group by a.cty
2.5 长沙、北京被用车方取消率排第一的司机编号为?
先求出取消率–排序-- 筛选序列号为1的行
select * from (select cty,cancel_rate,dense_rank()over(partition by cancel_rate desc) rnk from (select cty,driver_id,sum(if(status='cancel_by_usr',1,0))/count(1) as cancel_rate from hll_t1 group by cty,driver_id ) a )b where rnk=1

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/115335.html