首先对日期进行格式化
update didi set call_time=cast(call_time as datetime);
update didi set grab_time=cast(grab_time as datetime);
update didi set cancel_time=cast(cancel_time as datetime);
update didi set finish_time=cast(finish_time as datetime);
对日期减去11,计算巴西时间
update didi set call_time= DATE_SUB(call_time,INTERVAL 660 minute) ;
update didi set grab_time= DATE_SUB(grab_time,INTERVAL 660 minute) ;
update didi set cancel_time= DATE_SUB(cancel_time,INTERVAL 660 minute) ;
update didi set finish_time= DATE_SUB(finish_time,INTERVAL 660 minute) ;
select sum(case when grab_time between '2018-03-05' and '2018-03-12' then 1 else 0 end)/count(*) as 应答率,
sum(case when finish_time between '2018-03-05' and '2018-03-12' then 1 else 0 end)/count(*) as 完答率
from didi
这里值得注意的是如果我们要截止到3-11号,一定要在between当中写到3-12号。
select sum(TIMESTAMPDIFF(minute,call_time,grab_time))/count(grab_time) as 呼叫应答时间
from didi
where grab_time between '2018-03-05' and '2018-03-12'
ALTER TABLE didi add COLUMN call_time_hour varchar(255);
UPDATE didi
SET
call_time_hour=DATE_FORMAT(call_time,'%k')
数据格式化转换成小时
select *
from
(select call_time_hour,count(order_id) as 最小次数
from didi
GROUP BY call_time_hour
order by 最小次数 asc )a
limit 2
select *
from
(select call_time_hour,count(order_id) as 最大次数
from didi
GROUP BY call_time_hour
order by 最大次数 desc )a
limit 1
ALTER TABLE didi add COLUMN call_time_day varchar(255);
UPDATE didi
SET
call_time_day=DATE_FORMAT(call_time,'%Y-%m-%d')
select count(*)/2/297 as 次日打车率
from (
select TIMESTAMPDIFF(day,b.call_time_day,a.call_time_day) as 间隔
from didi a
left JOIN didi b
on a.passenger_id=b.passenger_id and a.order_id <> b.order_id
where a.call_time_day between '2018-03-05' and '2018-03-12'
and b.call_time_day between '2018-03-05' and '2018-03-12'
)a
where 间隔 =1
先要转换成天,不然隔天如果没有超过24小时可能算作是同一天,要注意的是left join 运算后有笛卡尔积运算产生四条数据,两条是相同的两个id,这里where 间隔=0 已经筛除,还有一条是对称的所以要进行除以2,最后除以总数297就是次日打车率了。
做到第四题的表是这个样子:
题目存在很多易错点,因为是真实的业务问题,特别是对日期的处理大家要加以注意。