题解 | #工作日各时段叫车量、等待接单时间和调度时间#

工作日各时段叫车量、等待接单时间和调度时间

https://www.nowcoder.com/practice/34f88f6d6dc549f6bc732eb2128aa338

题目

统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间
全部以event_time-开始打车时间为时段划分依据
平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单
结果按叫车量升序排序

指标说明：

不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 , 20:00:00）、休息时间 [20:00:00 , 07:00:00）；时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）
等待接单时间：从开始打车到司机接单为等待接单时间，即 end_time - event_time
调度时间：从司机接单到上车为调度时间，即 start_time - order_time

日期函数 —— 用于提取周几（参考链接：MYSQL经典SQL之星期问题）：

WEEKDAY() ：取值为0～6，对应星期一到星期日，每周以星期一为第一天；如，0即代表星期一
DAYOFWEEK() ：取值为1～7，对应星期日到星期六，每周以星期日为第一天；如，2即代表星期一
DAYNAME() ：取值为Sunday~Saturday，对应星期日到星期六，第一天是星期日；该函数与参数lc_time_names有关，如果设置此参数值为"zh_CN"，则得到对应的中文名称“星期日”～“星期六”

解题步骤：

步骤 1：针对周一到周五的打车记录，根据event_time来拆分叫车的时间段

周一到周五的识别，需要使用WEEKDAY()函数来识别和筛选

拆分不同时段则需要使用CASE WHEN和TIME函数

SELECT CASE WHEN TIME(cr.event_time) >= "07:00:00" AND TIME(cr.event_time) < "09:00:00" THEN "早高峰"
            WHEN TIME(cr.event_time) >= "09:00:00" AND TIME(cr.event_time) < "17:00:00" THEN "工作时间"
            WHEN TIME(cr.event_time) >= "17:00:00" AND TIME(cr.event_time) < "20:00:00" THEN "晚高峰"
            WHEN TIME(cr.event_time) >= "20:00:00" OR TIME(cr.event_time) < "07:00:00" THEN "休息时间" 
            ELSE NULL END AS period
FROM tb_get_car_order AS co LEFT OUTER JOIN tb_get_car_record AS cr
ON co.order_id = cr.order_id
/* 需要先把event_time的具体日期提取出来，再使用WEEKDAY来识别其周几，0-4代表周一到五 */
WHERE WEEKDAY(DATE(cr.event_time)) BETWEEN 0 AND 4

步骤 2：计算每一次打车记录的等待时间和调度时间，注意：一定要先以秒为单位计算时间差

SELECT
       CASE WHEN TIME(cr.event_time) >= "07:00:00" AND TIME(cr.event_time) < "09:00:00" THEN "早高峰"
            WHEN TIME(cr.event_time) >= "09:00:00" AND TIME(cr.event_time) < "17:00:00" THEN "工作时间"
            WHEN TIME(cr.event_time) >= "17:00:00" AND TIME(cr.event_time) < "20:00:00" THEN "晚高峰"
            WHEN TIME(cr.event_time) >= "20:00:00" OR TIME(cr.event_time) < "07:00:00" THEN "休息时间" 
            ELSE NULL END AS period,
        /* 使用TIMESTAMPDIFF还计算时间差 */
        TIMESTAMPDIFF(SECOND, cr.event_time, cr.end_time) AS wait_time,
        TIMESTAMPDIFF(SECOND, co.order_time, co.start_time) AS dispatch_time
FROM tb_get_car_order AS co LEFT OUTER JOIN tb_get_car_record AS cr
ON co.order_id = cr.order_id
/* 需要先把event_time的具体日期提取出来，再使用WEEKDAY来识别其周几，0-4代表周一到五 */
WHERE WEEKDAY(DATE(cr.event_time)) BETWEEN 0 AND 4

步骤 3：根据时间段进行GROUP BY分组，并计算各时段的叫车量、平均等待接单时间和平均调度时间

注意，在计算平均时间差的时候，还需要将“秒”转化为“分钟”，即，除以60，之后再保留1位小数

SELECT
     CASE WHEN TIME(cr.event_time) >= "07:00:00" AND TIME(cr.event_time) < "09:00:00" THEN "早高峰"
          WHEN TIME(cr.event_time) >= "09:00:00" AND TIME(cr.event_time) < "17:00:00" THEN "工作时间"
          WHEN TIME(cr.event_time) >= "17:00:00" AND TIME(cr.event_time) < "20:00:00" THEN "晚高峰"
          WHEN TIME(cr.event_time) >= "20:00:00" OR TIME(cr.event_time) < "07:00:00" THEN "休息时间" 
          ELSE NULL END AS period,
      /* 计算叫车量 */
      COUNT(co.order_id) AS get_car_num,
      /* 使用TIMESTAMPDIFF还计算时间差 */ 
      ROUND(AVG(TIMESTAMPDIFF(SECOND, cr.event_time, cr.end_time)) / 60, 1) AS avg_wait_time,
      ROUND(AVG(TIMESTAMPDIFF(SECOND, co.order_time, co.start_time)) / 60, 1) AS avg_dispatch_time
FROM tb_get_car_order AS co LEFT OUTER JOIN tb_get_car_record AS cr
ON co.order_id = cr.order_id
WHERE WEEKDAY(DATE(cr.event_time)) BETWEEN 0 AND 4
GROUP BY CASE WHEN TIME(cr.event_time) >= "07:00:00" AND TIME(cr.event_time) < "09:00:00" THEN "早高峰"
            WHEN TIME(cr.event_time) >= "09:00:00" AND TIME(cr.event_time) < "17:00:00" THEN "工作时间"
            WHEN TIME(cr.event_time) >= "17:00:00" AND TIME(cr.event_time) < "20:00:00" THEN "晚高峰"
            WHEN TIME(cr.event_time) >= "20:00:00" OR TIME(cr.event_time) < "07:00:00" THEN "休息时间" 
            ELSE NULL END
/* 根据叫车量升序排列 */
ORDER BY get_car_num ASC;