【题目】
“订单信息表”里记录了巴西乘客使用打车软件的信息，包括订单呼叫、应答、取消、完单时间。（滴滴2020年笔试题）
注意：
（1）表中的时间是北京时间，巴西比中国慢11小时。
（2）应答时间列的数据值如果是“1970”年，表示该订单没有司机应答，属于无效订单。
问题
1. 订单的应答率，完单率分别是多少？
2. 呼叫应答时间有多长？
3. 从这一周的数据来看，呼叫量最高的是哪一个小时（当地时间）？呼叫量最少的是哪一个小时（当地时间）？
4. 呼叫订单第二天继续呼叫的比例有多少？
5. （选做）如果要对乘客进行分类，你认为需要参考哪一些因素？
【解题步骤】
我们首先对数据进行预处理，将北京时间转化为巴西时间。具体需要分两步来实现，首先为了确保表中的时间为标准的日期格式，我们统一对其进行日期格式处理。然后再将处理后的日期转换成巴西时间。
（1）日期格式化
由于在日期格式化中，我们会涉及到需要修改表中的日期数据，因此考虑用update语句。而修改表的具体操作会涉及到日期数据类型之间的转换，我们考虑用cast函数。
由于表中的时间应是datetime的格式，也就是精确到时分秒（YYYY-MM-DD HH:mm:ss）。转换后的效果如下图。
因此可以写出下列sql语句。
update订单信息表setcall_time=cast(call_timeasdatetime);update订单信息表setgrab_time=cast(grab_timeasdatetime);update订单信息表setcancel_time=cast(cancel_timeasdatetime);update订单信息表setfinish_time=cast(finish_timeasdatetime);
日期格式化后的表如下图。
（2） 转换成巴西时间
由于数据中的时间为北京时间，而且已知巴西比中国慢11小时，因此我们这里使用 date_sub函数。
因此可以写出下列sql语句：
update订单信息表setcall_time=date_sub(call_time,interval11hour) ;update订单信息表setgrab_time=date_sub(grab_time,interval11hour) ;update订单信息表setcancel_time=date_sub(cancel_time,interval11hour) ;update订单信息表setfinish_time=date_sub(finish_time,interval11hour) ;
时间转换结果如下图：
按照以上操作，数据日期预处理完成。
1. 订单的应答率，完单率分别是多少？
（1）应答率
应答率=应答订单数/呼叫订单数
呼叫订单： 呼叫订单数等于呼叫时间（call_time）这一列的数据总数，可以用count(call_time)汇总。
应答订单：应答订单数等于应答时间（grab_time）这一列的数据总数，可以用count(grab_time)汇总。需要注意，这一列里的值不等于‘1970’的数据的数量才是有效的应答订单数。如下图：红框的部分为应答订单。
根据题目的业务要求，需要对不同的条件进行统计，在《猴子 从零学会sql》里讲过条件判断要用case when表达式。所以应答订单数对应的sql是：
sum(casewhengrab_time <>1970then1else0end)
现在可以计算出指标 应答率=应答订单数/呼叫订单数 ：
selectsum(casewhengrab_time <>1970then1else0end)/count(call_time)as应答率from订单信息表;
查询结果如下：
（2）完单率
完单率=完成订单数/呼叫订单数
完成订单：完成时间（finish_time）这一列中，值不等于‘1970’的数据数量为有效的完成订单数。如下图：红框的部分为完成订单。
所以完成订单数为：
sum(casewhenfinish_time <>1970then1else0end)
现在可以计算出指标完单率=完成订单数/呼叫订单数 ：
selectsum(casewhenfinish_time <>1970then1else0end)/count(*)as完单率from订单信息表;
查询结果如下
2. 呼叫应答时间有多长？
根据题目中指标定义：
呼叫应答时间=被应答订单从呼叫到被应答时长总和/被应答订单数量
被应答订单从呼叫到被应答时长=被应答的时间（grab_time） - 呼叫的时间（call_time）。
这涉及到计算两个日期之间的差值，《猴子 从零学会sql》里讲到对应单函数是timestampdiff。下图是这个函数的用法。
我们回到题目，利用timestampdiff函数计算呼叫到被应答时长的总和。
综上，相应的sql语句分析如下
查询结果如下
3. 从这一周的数据来看，呼叫量最高的是哪一个小时（当地时间）？呼叫量最少的是哪一个小时（当地时间）？
（1）时间转换
由于题目中要求的是“哪一个小时”，因此我们首先将数据格式化转换成小时。新增一列来表示时间中的“小时”,列名设为call_time_hour。
-- 添加列altertable订单信息表addcolumncall_time_hourvarchar(255);
利用date_format 函数，用于以不同的格式显示日期数据，将将数据格式转换成小时。
/** 给列添加数据%k表示显示的是24小时制中的小时*/update订单信息表setcall_time_hour=date_format(call_time,'%k');
转化后的表如下图
（2）呼叫量最高的是哪一个小时？
呼叫订单是order_id列。按“每个小时”分组（group by call_time_hour），然后统计每个小时的呼叫订单量count(order_id)，然后排序就可以知道哪个小时的订单量最高。
下图给出sql语句分析过程：
此时得到查询结果如下图
因为题目要求的是排序后的最大值（呼叫量最高的小时），可以用limit子句 来筛选出第一行数据。
sql语句如下：
selectcall_time_hour,count(order_id)as最大次数from订单信息表groupbycall_time_hourorderby最大次数desclimit1;
（2）呼叫量最少的是哪一个小时？
接着上面的排序结果，我们看到有3个呼叫小时的数据都为最小次数，用limit 3都将它们筛选出来即可。
selectcall_time_hour,count(order_id)as最小次数from订单信息表groupbycall_time_hourorderby最小次数asclimit3;
4. 呼叫订单第二天继续呼叫的比例有多少？
呼叫订单第二天继续呼叫的比例=第二天继续呼叫的用户量/总的呼叫订单量。
计算第二天继续呼叫的用户量的思路如下图：
我们具体分析看每一部分。
（1）自关联查询，求得呼叫的时间间隔。由于我们需要时间的单位为天，因此我们使用date_format函数来提取出日期中的“年月日”部分。
sql语句如下：
-- 添加一列来显示时间中的“年月日”部分altertable订单信息表addcolumncall_time_dayvarchar(255);update订单信息表setcall_time_day=date_format(call_time,'%Y-%m-%d');
此时变化后的表如下：
我们接下来利用表的联结来计算相隔天数。这里由于涉及到计算相隔的天数之差，我们使用上面讲过的timestampdiff函数。单位为天。
此时查询结果如下
筛选出时间差为1天的数据，也就是间隔=1的数据。
利用子查询嵌套，将上面的查询结果作为新表，在其中做出筛选，并求和。sql语句分析如下图。
此时查询结果如下图
最后我们计算出第二天继续呼叫比例
查询结果如下图
5. （选做）如果要对表中乘客进行分类，你认为需要参考哪一些因素？
我们可以从以下两个角度来考虑对用户分类。
用户行为分类
1) 根据完成时间和接单时间，可大致计算出乘客在乘车过程中所消耗的时间，对这个时间进行预判，属于长途、中途或者是短途，来分析乘客的乘车习惯。
2) 根据呼叫时间可以判断乘客是在时间点发单的，乘客需求是如何产生的，可分析用户在哪些场景有乘车需求，上班、下班、就餐、出游、临时等场景。
用户价值分类
使用之前学过的RFM分析方法，对用户按价值分类。
RFM具体到本题可以做以下定义：
R：最近一次乘客的完单时间。F：乘客打车的频率。M：打车消费的金额。此处可以用乘车过程消耗的时长来代替等。
【本题考点】
1.对日期数据的处理，掌握题目里讲过对常用日期处理方法。
2.考查分析思维能力。使用学过的如何使用数据分析解决问题的框架来解决。
推荐：如何从零学会sql？
网友评论