NIUKE SQL：大厂面试真题（四） 【某滴打车】

SQL174 2021年国庆在北京接单3次及以上的司机统计信息

问题：请统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数

SELECT
    city,
    ROUND(SUM(order_num)/COUNT(driver_id),3) AS avg_order_num,
    ROUND(SUM(income)/COUNT(driver_id),3) AS avg_income
FROM
(
    SELECT
        city,driver_id,COUNT(order_time) AS order_num,SUM(fare) AS income
    FROM tb_get_car_order JOIN tb_get_car_record USING(order_id)
    WHERE city='北京'
    AND DATE(order_time) BETWEEN 20211001 AND 20211007
    GROUP BY driver_id
) AS A
WHERE order_num>=3
GROUP BY city

SQL175 有取消订单记录的司机平均评分

问题：请找到2021年10月有过取消订单记录的司机，计算他们每人全部已完成的有评分订单的平均评分及总体平均评分，保留1位小数。先按driver_id升序输出，再输出总体情况

SELECT
    driver_id,
    ROUND(AVG(grade),1) AS avg_grade
FROM tb_get_car_order
WHERE grade IS NOT NULL
AND driver_id IN (SELECT driver_id FROM tb_get_car_order WHERE start_time IS NULL AND DATE(order_time) BETWEEN 20211001 AND 20211031)

GROUP BY driver_id
UNION
SELECT
    '总体' AS driver_id,
    ROUND(AVG(grade),1) AS avg_grade
FROM tb_get_car_order
WHERE grade IS NOT NULL
AND driver_id IN (SELECT driver_id FROM tb_get_car_order WHERE start_time IS NULL AND DATE(order_time) BETWEEN 20211001 AND 20211031)

ORDER BY IF(driver_id='总体',1,0),driver_id

 SQL176 每个城市中评分最高的司机信息

问题：请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数

注：有多个司机评分并列最高时，都输出

平均评分和日均接单量保留1位小数

日均行驶里程数保留3位小数，按日均接单数升序排序

WITH A AS (
    SELECT 
        city,
        driver_id,
        ROUND(AVG(grade),1) as avg_grade,
        ROUND(COUNT(order_time)/COUNT(DISTINCT DATE(order_time)),1) AS avg_order_num,
        ROUND(SUM(mileage)/COUNT(DISTINCT DATE(order_time)),3) AS avg_mileage,
        RANK() OVER(PARTITION BY city ORDER BY AVG(grade) DESC) AS rk 
    FROM tb_get_car_order JOIN tb_get_car_record USING (order_id)
    GROUP BY driver_id ,city )

SELECT
    city,
    driver_id,
    avg_grade,
    avg_order_num,
    avg_mileage
FROM A
WHERE rk=1
ORDER BY avg_order_num

SQL177 国庆期间近7日日均取消订单量

问题：请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数

WITH A AS(
SELECT
    DATE(order_time) AS dt,
    SUM(IF(start_time IS NULL,0,1)) AS finish_num,
    SUM(IF(start_time IS NULL,1,0)) AS cancel_num
FROM tb_get_car_order
GROUP BY dt
ORDER BY dt)

SELECT * FROM
(
    SELECT  
        dt,
        ROUND(SUM(finish_num) OVER(ORDER BY dt ROWS 6 PRECEDING)/7,2) AS finish_num_7d,
        ROUND(SUM(cancel_num) OVER(ORDER BY dt ROWS 6 PRECEDING)/7,2) AS cancel_num_7d
    FROM A
    GROUP BY dt
    ORDER BY dt
) AS B
WHERE dt BETWEEN 20211001 AND 20211003

窗口函数：RANGE ROWS

range是逻辑窗口，指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内

SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum

当id=1时，是sum为1-1<=id<=1+2 的和，即sum=1+1+3=5(取id为1,1,3)
当id=3时，是sum为3-1<=id<=3+2 的和，即sum=3（取id为3)

rows是物理窗口，根据order by 子句排序后，取的前N行及后N行的数据计算（如rows_sum结果，是取前1行和后2行数据的求和，分析上例rows_sum的结果：

SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum

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

问题：统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以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）

从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间

SELECT
    (CASE WHEN T BETWEEN 7 AND 8 THEN '早高峰'
        WHEN T BETWEEN 9 AND 16 THEN '工作时间'
        WHEN T BETWEEN 17 AND 19 THEN '晚高峰' ELSE '休息时间' END) AS period,
    COUNT(event_time) AS get_car_num,
    ROUND(AVG(wait_time)/60,1) AS avg_wait_time,
    ROUND(SUM(dispatch_time)/COUNT(start_time)/60,1) AS avg_dispatch_time
FROM(
    SELECT
        HOUR(event_time) AS T,
        event_time,
        start_time,
        TIMESTAMPDIFF(SECOND,event_time,order_time) AS wait_time,      
        TIMESTAMPDIFF(SECOND,order_time,start_time) AS dispatch_time  
    FROM tb_get_car_order JOIN tb_get_car_record USING(order_id)
    WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
) AS A
GROUP BY period
ORDER BY get_car_num

DAYOFWEEK

函数返回日期的工作日索引值，星期日为1，星期一为2

DAYOFWEEK(date)

 SQL179 各城市最大同时等车人数

问题：请统计各个城市在2021年10月期间，单日中最大的同时等车人数

等车指从开始打车起，直到取消打车、取消等待或上车前的这段时间里用户的状态

如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少

结果按各城市最大等车人数升序排序，相同时按城市升序排序

#瞬时在线用户数
WITH A AS(
SELECT 
    city,
    SUM(uv) OVER (PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt
FROM
(
    SELECT
        city,
        event_time AS uv_time,
        1 AS uv
    FROM tb_get_car_record
    UNION ALL
    SELECT 
        city,
        end_time AS uv_time,
        -1 AS uv
    FROM tb_get_car_record
    WHERE order_id IS NULL #接单前取消
    UNION ALL
    SELECT 
        city,
        finish_time AS uv_time,
        -1 AS uv
    FROM tb_get_car_record LEFT JOIN tb_get_car_order USING(order_id)
    WHERE start_time IS NULL #接单后取消
    UNION ALL
    SELECT 
        city,
        start_time AS uv_time,
        -1 AS uv
    FROM tb_get_car_record LEFT JOIN tb_get_car_order USING(order_id)
    WHERE start_time IS NOT NULL #上车  
) AS B
WHERE DATE(uv_time) BETWEEN 20211001 AND 20211031
)


SELECT
    city,
    MAX(uv_cnt) AS max_wait_uv
FROM A
GROUP BY city
ORDER BY max_wait_uv,city