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

# 求工作日各时段（四个时段）的叫车量、平均等待接单时间和平均调度时间
# 等待接单时间：从开始打车到司机接单
# 调度时间：从司机接单到上车

# 联立两张表，筛选出工作日的记录（使用DAYOFWEEK函数）
# 再根据不同时段的定义分组计算：叫车量（有记录就算叫了一次车），等待接单时间（event_time和end_time的时间间隔），调度时间（order_time和start_time的间隔，这个只计算完成了的订单，要独立出来，也就是说要新增一个字段是完成了的订单数）

# 临时表0，筛选出工作日的记录。其实可以和表1合并
WITH temp_0 AS(
    SELECT event_time, order_time, start_time
    FROM tb_get_car_record a
    JOIN tb_get_car_order b USING(order_id)
    WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
    ),
	# 表1，将每条记录落到时间段里，提取小时
    temp_1 AS(
    SELECT CASE WHEN HOUR(event_time) >= 7 AND HOUR(event_time) < 9 THEN '早高峰'
                WHEN HOUR(event_time) >= 9 AND HOUR(event_time) < 17 THEN '工作时间'
                WHEN HOUR(event_time) >= 17 AND HOUR(event_time) < 20 THEN '晚高峰'
                ELSE '休息时间' END period,
            event_time, order_time, start_time
    FROM temp_0
    )

# 主查询，查询时间段，出车数（不管完成与否），平均接单时间（这里采用了求和再除，其实也可以直接求平均的方式），平均调度时间（这里我觉得必须要先计算出成功出车的记录数，除以它才对，但是试了下直接求平均也行）
SELECT period, COUNT(*) get_car_num, ROUND(SUM(TIMESTAMPDIFF(SECOND, event_time, order_time))/COUNT(*)/60, 1) avg_wait_time, ROUND(SUM(TIMESTAMPDIFF(SECOND, order_time, start_time))/COUNT(start_time)/60, 1) avg_dispatch_time
FROM temp_1
# 分组，排序
GROUP BY period
ORDER BY get_car_num

新知识点：

1.判断日期是周几：可以使用DAYOFWEEK()函数来实现这一点，该函数返回给定日期是星期几的数字表示，其中1代表星期天，2代表星期一，依此类推，直到7代表星期六。

2.DATE()提取年月日，TIME()提取时分秒，HOUR()、MINUTE()分别提取时、分。

3.将秒直接除以60即可得到分钟，但是直接用MINUTE()提取出来是0，因为一个是转换，一个是提取。

本质上还是一个分组聚合计算的题目，但是前提条件比较多，要先知道是不是工作日，是哪个时间段。