MYSQL(面试真题)【滴滴2020】

糖逗

参考链接：https://mp.weixin.qq.com/s?__biz ... =21#wechat_redirect

USE train;
#1. 订单的应答率，完单率分别是多少？
#创建表
DROP TABLE IF EXISTS 订单信息表;
CREATE TABLE 订单信息表(
        order_id INT,
        passenger_id INT,
        call_time TIMESTAMP,
        grab_time TIMESTAMP,
        cancel_time TIMESTAMP,
        finish_time TIMESTAMP
);
INSERT INTO 订单信息表
VALUES (70361, 0001, '2018-03-08 09:07:00', '2018-03-03 09:09:00', '1971-01-01 00:00:00', '2018-03-09 09:37:00'),
        (70362, 0001, '2018-03-09 22:07:00', '2018-03-09 22:07:00', '2018-03-09 22:08:00', '1971-01-01 00:00:00'),
        (70363, 0002, '2018-03-09 20:10:00', '1971-01-01 00:00:00', '2018-03-09 20:11:00', '1971-01-01 00:00:00'),
        (70364, 0003, '2018-03-10 08:00:00', '1971-01-01 00:00:00', '1971-01-01 00:00:00', '1971-01-01 00:00:00'),
        (70365, 0004, '2018-03-12 09:00:00', '2018-03-12 09:01:00', '1971-01-01 00:00:00', '2018-03-12 09:30:00');

#(1)应答率 = 应答/呼叫 = 有效的grab / call


SELECT COUNT(*) INTO @count
FROM 订单信息表;

SELECT COUNT(grab_time) / @count
FROM 订单信息表
WHERE grab_time != '1971-01-01 00:00:00';

#(2)完单率 = 有效finish_time / 呼叫订单数
SELECT COUNT(finish_time) / @count
FROM 订单信息表
WHERE finish_time != '1971-01-01 00:00:00';

#2. 呼叫应答时间有多长？
#有效grab_time - call_time的平均minute
SELECT SUM(TIMESTAMPDIFF(MINUTE, call_time, grab_time)) / COUNT(grab_time) AS 平均呼叫应答时间
FROM 订单信息表
WHERE grab_time != '1971-01-01 00:00:00';

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

#(1)将巴西时间转为中国时间
UPDATE 订单信息表 SET call_time = CAST(call_time AS DATETIME);
UPDATE 订单信息表 SET grab_time = CAST(grab_time AS DATETIME);
UPDATE 订单信息表 SET cancel_time = CAST(cancel_time AS DATETIME);
UPDATE 订单信息表 SET finish_time = CAST(finish_time AS DATETIME);

UPDATE 订单信息表 SET call_time = DATE_SUB(call_time, INTERVAL 11 HOUR);
UPDATE 订单信息表 SET grab_time = DATE_SUB(grab_time, INTERVAL 11 HOUR);
UPDATE 订单信息表 SET cancel_time = DATE_SUB(cancel_time, INTERVAL 11 HOUR);
UPDATE 订单信息表 SET finish_time = DATE_SUB(finish_time, INTERVAL 11 HOUR);


ALTER TABLE 订单信息表 ADD COLUMN call_time_hour VARCHAR(255);
UPDATE 订单信息表 SET call_time_hour = DATE_FORMAT(call_time, '%H');

SELECT call_time_hour, COUNT(call_time_hour) AS 总数
FROM 订单信息表
GROUP BY call_time_hour
ORDER BY 总数 DESC
LIMIT 1;

#(2)呼叫量最少的是哪一个小时？
SELECT call_time_hour, COUNT(call_time_hour) AS 总数
FROM 订单信息表
GROUP BY call_time_hour
ORDER BY 总数
LIMIT 3;

#4. 呼叫订单第二天继续呼叫的比例有多少？
#(1)次日呼叫的用户id
SELECT COUNT(DISTINCT data2.passenger_id) / @count
FROM 订单信息表 AS data1 LEFT JOIN 订单信息表 AS data2
        ON data1.passenger_id = data2.passenger_id 
           AND TIMESTAMPDIFF(DAY, data1.call_time, data2.call_time) = 1;