sql题目0126

数据源：
文档：同一压缩包下的订单对应呼叫、应答、取消、完单时间（抽样100名乘客）
内容：2018年3月5日-2018年3月11日期间100名x地区乘客的订单基础信息

字段	中文名称	解释
order_id	订单id	呼叫订单识别号
passenger_id	乘客id	乘客识别号
call_time	呼叫时间	乘客从应用上发出需要用车的请求的时间点（北京时间）
grab_time	应答时间	司机点击接单的时间点（北京时间）
cancel_time	取消时间	司机或者乘客取消订单的时间（北京时间）
finish_time	完单时间	司机点击到达目的地的时间点（北京时间）

指标释义

指标名称	含义	统计口径
应答率	呼叫订单被应答的比例	应答订单/呼叫订单
完单率	呼叫订单被完成订单	完成订单呼叫订单
呼叫应答时间	被应答订单从呼叫到被应答平均时长	被应答订单从呼叫到被应答时长总和/被应答订单数量

其它信息：
x地区比中国慢11小时

问题：

数据预处理：

A.将时间相关列转换格式。

--利用cast函数转换成日期数据
update  table1 set
call_time=cast(call_time as datetime ),
grab_time=cast(grab_time as datetime ),
cancel_time=cast(cancel_time as datetime),
finish_time =cast(finish_time as datetime);

B.x地区比中国慢11小时，将表中北京时间转换为x地区时间。

update  table1 set
call_time=date_sub(call_time,interval 11 hour),
grab_time=date_sub(grab_time,interval 11 hour),
cancel_time=date_sub(cancel_time,interval 11 hour),
finish_time=date_sub(finish_time,interval 11 hour);

1、订单的应答率、完单率分别是多少？

应答率=应答订单数/呼叫订单数
完单率=完成订单数/呼叫订单数

select
sum(if(year(grab_time)<>1970,1,0))/count(call_time) as 应答率 ,
sum(if(year(finish_time)<>1970,1,0))/count(call_time) as 完单率 
from table1;

2、呼叫应答时间是多长？

呼叫应答时间=被应答订单从呼叫到被应答时长总和/被应答订单数量

--使用timestampdiff函数，计算应答时间与呼叫时间之间的时长
select sum(timestampdiff(minute,call_time,grab_time))/count(grab_time) as 呼叫应答时间 
from table1
where year(grab_time)<>1970;

3、从这一周的数据来看，呼叫量最高的是哪一个小时（当地时间）？呼叫量最少的是哪一个小时（当地时间）？

新增一个字段，订单呼叫时间的小时

--新增一列
alter table table1  add column call_time_hour VARCHAR(255);

可以用两种方法取出小时，substr()和date_format()

--使用substr函数做字符串截取，为新列赋值
update table1 set call_time_hour=substr(call_time from 12 for 2);

使用date_format函数转换格式
update table1 set call_time_hour=DATE_FORMAT(call_time,'%k')

找出呼叫量最高的小时，显示两行防止出现重复值

select call_time_hour,count(call_time) as 呼叫量
from table1
group by call_time_hour
order by count(call_time) desc
limit 2;

找出呼叫量最少的小时，显示3行确认是否有第三个相等值。

select
call_time_hour,count(call_time) as 呼叫量
from table1
group by call_time_hour
order by count(call_time)  asc 
limit 3;

知识点：
DATE_FORMAT(date,format)

date 参数是合法的日期。format 规定日期/时间的输出格式。

4、呼叫订单第二天继续呼叫的比例有多少？

select count(distinct  a.order_id)/(select count(distinct order_id) from table1) as 第二天继续呼叫比例 
from table1 a 
join  
table1 b 
on  a.passenger_id=b.passenger_id
where datediff(a.call_time,b.call_time)=1;

5、如果要对表中乘客进行分类，你认为需要参考哪些因素？

R:乘客上一次打车距离研究时间段内某个时间点的时间间隔
F:乘客在数据期间的打车频率
M:打车消费金额（如若无打车金额，可以用完成订单总时长代替。）

	R	F	M
重要价值用户	高	高	高
重要发展用户	高	低	高
重要保持用户	低	高	高
重要挽留用户	低	低	高
一般价值用户	高	高	低

知识点：

datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒。

-- 相差2天
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');

TIMESTAMPDIFF函数，有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。

--相差1天
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--相差49小时
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

--相差176400秒

select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');