【牛客刷题-SQL大厂面试真题】NO4.出行场景（某滴打车）
体系化学习SQL，请到牛客经典高频面试题库，参加实训，提高你的SQL技能吧~
https://www.nowcoder.com/link/pc_csdncpt_itbd_sql
文章目录
- 前言
前言
SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
🐴 SQL174 2021年国庆在北京接单3次及以上的司机统计信息
🚀 建表语句
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;CREATE TABLE tb_get_car_record ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid INT NOT NULL COMMENT '用户ID', city VARCHAR(10) NOT NULL COMMENT '城市', event_time datetime COMMENT '打车时间', end_time datetime COMMENT '打车结束时间', order_id INT COMMENT '订单号') CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', order_id INT NOT NULL COMMENT '订单号', uid INT NOT NULL COMMENT '用户ID', driver_id INT NOT NULL COMMENT '司机ID', order_time datetime COMMENT '接单时间', start_time datetime COMMENT '开始计费的上车时间', finish_time datetime COMMENT '订单结束时间', mileage DOUBLE COMMENT '行驶里程数', fare DOUBLE COMMENT '费用', grade TINYINT COMMENT '评分') CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES (101, '北京', '2021-10-01 07:00:00', '2021-10-01 07:02:00', null), (102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9001), (101, '北京', '2021-10-01 08:28:10', '2021-10-01 08:30:00', 9002), (103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003), (104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004), (105, '北京', '2021-10-01 08:00:00', '2021-10-01 08:02:10', 9005), (106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006), (107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007), (108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008) ;INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES (9002, 101, 201, '2021-10-01 08:30:00', null, '2021-10-01 08:31:00', null, null, null), (9001, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5), (9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4), (9004, 104, 202, '2021-10-03 08:01:00', '2021-10-03 08:13:00', '2021-10-03 08:31:00', 7.5, 22, 4), (9005, 105, 203, '2021-10-01 08:02:10', '2021-10-01 08:18:00', '2021-10-01 08:31:00', 15.0, 44, 5), (9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25, 5), (9007, 107, 203, '2021-10-02 11:01:00', '2021-10-02 11:07:00', '2021-10-02 11:31:00', 9.9, 30, 5), (9008, 108, 203, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4);📖 需求
场景逻辑说明：用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。问题：请统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。🍌🍌 答案
SELECT "北京" as city, ROUND(AVG(order_num), 3) as avg_order_num, ROUND(AVG(income), 3) as avg_incomeFROM ( SELECT driver_id, COUNT(order_id) as order_num, SUM(fare) as income FROM tb_get_car_order JOIN tb_get_car_record USING(order_id) WHERE city = "北京" and DATE_FORMAT(order_time,"%Y%m%d") BETWEEN '20211001' AND '20211007' GROUP BY driver_id HAVING COUNT(order_id) >= 3) as t_driver_info;
🐴 SQL175 有取消订单记录的司机平均评分
🚀 建表语句
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;CREATE TABLE tb_get_car_record ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid INT NOT NULL COMMENT '用户ID', city VARCHAR(10) NOT NULL COMMENT '城市', event_time datetime COMMENT '打车时间', end_time datetime COMMENT '打车结束时间', order_id INT COMMENT '订单号') CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', order_id INT NOT NULL COMMENT '订单号', uid INT NOT NULL COMMENT '用户ID', driver_id INT NOT NULL COMMENT '司机ID', order_time datetime COMMENT '接单时间', start_time datetime COMMENT '开始计费的上车时间', finish_time datetime COMMENT '订单结束时间', mileage FLOAT COMMENT '行驶里程数', fare FLOAT COMMENT '费用', grade TINYINT COMMENT '评分') CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES (101, '北京', '2021-10-01 07:00:00', '2021-10-01 07:02:00', null), (102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9001), (101, '北京', '2021-10-01 08:28:10', '2021-10-01 08:30:00', 9002), (103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003), (104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004), (105, '北京', '2021-10-01 08:00:00', '2021-10-01 08:02:10', 9005), (106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006), (107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007), (108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008), (109, '北京', '2021-10-08 18:00:00', '2021-10-08 18:01:00', 9009);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES (9002, 101, 202, '2021-10-01 08:30:00', null, '2021-10-01 08:31:00', null, null, null), (9001, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5), (9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4), (9004, 104, 202, '2021-10-03 08:01:00', '2021-10-03 08:13:00', '2021-10-03 08:31:00', 7.5, 22, 4), (9005, 105, 203, '2021-10-01 08:02:10', null, '2021-10-01 08:31:00', null, null, null), (9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25.5, 5), (9007, 107, 203, '2021-10-02 11:01:00', '2021-10-02 11:07:00', '2021-10-02 11:31:00', 9.9, 30, 5), (9008, 108, 203, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4), (9009, 109, 203, '2021-10-08 18:01:00', '2021-10-08 18:11:50', '2021-10-08 18:51:00', 13, 40, 5);📖 需求
场景逻辑说明：用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。问题：请找到2021年10月有过取消订单记录的司机，计算他们每人全部已完成的有评分订单的平均评分及总体平均评分，保留1位小数。先按driver_id升序输出，再输出总体情况。🍌🍌 答案
selectcoalesce(o.driver_id,'总体') as driver_id,round(avg(o.grade),1) as avg_gradefrom tb_get_car_order owhere driver_id in( select distinct driver_idfrom tb_get_car_orderwhere date_format(order_time,'%Y%m')=202110 and start_time is null)group by o.driver_id with rollup
🐴 SQL176 每个城市中评分最高的司机信息
🚀 建表语句
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;CREATE TABLE tb_get_car_record ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid INT NOT NULL COMMENT '用户ID', city VARCHAR(10) NOT NULL COMMENT '城市', event_time datetime COMMENT '打车时间', end_time datetime COMMENT '打车结束时间', order_id INT COMMENT '订单号') CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', order_id INT NOT NULL COMMENT '订单号', uid INT NOT NULL COMMENT '用户ID', driver_id INT NOT NULL COMMENT '司机ID', order_time datetime COMMENT '接单时间', start_time datetime COMMENT '开始计费的上车时间', finish_time datetime COMMENT '订单结束时间', mileage FLOAT COMMENT '行驶里程数', fare FLOAT COMMENT '费用', grade TINYINT COMMENT '评分') CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES (101, '北京', '2021-10-01 07:00:00', '2021-10-01 07:02:00', null), (102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9001), (101, '北京', '2021-10-01 08:28:10', '2021-10-01 08:30:00', 9002), (103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003), (104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004), (105, '北京', '2021-10-01 08:00:00', '2021-10-01 08:02:10', 9005), (106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006), (107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007), (108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008), (109, '北京', '2021-10-08 18:00:00', '2021-10-08 18:01:00', 9009);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES (9002, 101, 202, '2021-10-01 08:30:00', null, '2021-10-01 08:31:00', null, null, null), (9001, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5), (9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4), (9004, 104, 202, '2021-10-03 08:01:00', '2021-10-03 08:13:00', '2021-10-03 08:31:00', 7.5, 22, 4), (9005, 105, 203, '2021-10-01 08:02:10', null, '2021-10-01 08:31:00', null, null, null), (9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25.5, 5), (9007, 107, 203, '2021-10-02 11:01:00', '2021-10-02 11:07:00', '2021-10-02 11:31:00', 9.9, 30, 5), (9008, 108, 203, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4), (9009, 109, 203, '2021-10-08 18:01:00', '2021-10-08 18:11:50', '2021-10-08 18:51:00', 13, 40, 5);📖 需求
场景逻辑说明：用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。问题：请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。注：有多个司机评分并列最高时，都输出。平均评分和日均接单量保留1位小数，日均行驶里程数保留3位小数，按日均接单数升序排序。'🍌🍌 答案
select city,driver_id,avg_grade,avg_order_num,avg_mileage from(select r.city,o.driver_id,round(avg(o.grade),1) avg_grade,round(count(o.order_id)/(count(distinct date(order_time))),1) avg_order_num,round(sum(o.mileage)/(count(distinct date(order_time))),3) avg_mileage,dense_rank() over (partition by r.city order by round(avg(o.grade),1) desc) t_rankfrom tb_get_car_order o inner join tb_get_car_record r on r.order_id=o.order_idgroup by r.city,o.driver_id) as awhere t_rank = 1order by avg_order_num
🐴 SQL177 国庆期间近7日日均取消订单量
🚀 建表语句
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;CREATE TABLE tb_get_car_record ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid INT NOT NULL COMMENT '用户ID', city VARCHAR(10) NOT NULL COMMENT '城市', event_time datetime COMMENT '打车时间', end_time datetime COMMENT '打车结束时间', order_id INT COMMENT '订单号') CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_get_car_order ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', order_id INT NOT NULL COMMENT '订单号', uid INT NOT NULL COMMENT '用户ID', driver_id INT NOT NULL COMMENT '司机ID', order_time datetime COMMENT '接单时间', start_time datetime COMMENT '开始计费的上车时间', finish_time datetime COMMENT '订单结束时间', mileage FLOAT COMMENT '行驶里程数', fare FLOAT COMMENT '费用', grade TINYINT COMMENT '评分') CHARACTER SET utf8 COLLATE utf8_bin;INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id) VALUES (101, '北京', '2021-09-25 08:28:10', '2021-09-25 08:30:00', 9011), (102, '北京', '2021-09-25 09:00:30', '2021-09-25 09:01:00', 9012), (103, '北京', '2021-09-26 07:59:00', '2021-09-26 08:01:00', 9013), (104, '北京', '2021-09-26 07:59:00', '2021-09-26 08:01:00', 9023), (104, '北京', '2021-09-27 07:59:20', '2021-09-27 08:01:00', 9014), (105, '北京', '2021-09-28 08:00:00', '2021-09-28 08:02:10', 9015), (106, '北京', '2021-09-29 17:58:00', '2021-09-29 18:01:00', 9016), (107, '北京', '2021-09-30 11:00:00', '2021-09-30 11:01:00', 9017), (108, '北京', '2021-09-30 21:00:00', '2021-09-30 21:01:00', 9018), (102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9002), (106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006), (101, '北京', '2021-10-02 08:28:10', '2021-10-02 08:30:00', 9001), (107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007), (108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008), (103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003), (104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004), (109, '北京', '2021-10-03 18:00:00', '2021-10-03 18:01:00', 9009);INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES (9011, 101, 211, '2021-09-25 08:30:00', '2021-09-25 08:31:00', '2021-09-25 08:54:00', 10, 35, 5), (9012, 102, 211, '2021-09-25 09:01:00', '2021-09-25 09:01:50', '2021-09-25 09:28:00', 11, 32, 5), (9013, 103, 212, '2021-09-26 08:01:00', '2021-09-26 08:03:00', '2021-09-26 08:27:00', 12, 31, 4), (9023, 104, 213, '2021-09-26 08:01:00', null, '2021-09-26 08:27:00', null, null, null), (9014, 104, 212, '2021-09-27 08:01:00', '2021-09-27 08:04:00', '2021-09-27 08:21:00', 11, 31, 5), (9015, 105, 212, '2021-09-28 08:02:10', '2021-09-28 08:04:10', '2021-09-28 08:25:10', 12, 31, 4), (9016, 106, 213, '2021-09-29 18:01:00', '2021-09-29 18:02:10', '2021-09-29 18:23:00', 11, 39, 4), (9017, 107, 213, '2021-09-30 11:01:00', '2021-09-30 11:01:40', '2021-09-30 11:31:00', 11, 38, 5), (9018, 108, 214, '2021-09-30 21:01:00', '2021-09-30 21:02:50', '2021-09-30 21:21:00', 14, 38, 5), (9002, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5), (9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25.5, 4), (9001, 101, 202, '2021-10-02 08:30:00', null, '2021-10-02 08:31:00', null, null, null), (9007, 107, 203, '2021-10-02 11:01:00', '2021-10-02 11:07:00', '2021-10-02 11:31:00', 9.9, 30, 5), (9008, 108, 204, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4), (9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4), (9004, 104, 202, '2021-10-03 08:01:00', '2021-10-03 08:13:00', '2021-10-03 08:31:00', 7.5, 22, 4), (9009, 109, 204, '2021-10-03 18:01:00', null, '2021-10-03 18:51:00', null, null, null);📖 需求
场景逻辑说明：用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。问题：请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。🍌🍌 答案
SELECT dt, finish_num_7d, cancel_num_7dFROM ( SELECT dt, ROUND(AVG(finish_num) over(ORDER BY dt ROWS 6 preceding), 2) as finish_num_7d, ROUND(AVG(cancel_num) over(ORDER BY dt ROWS 6 preceding), 2) as cancel_num_7d FROM ( SELECT dt, SUM(is_finish) as finish_num, COUNT(1) - SUM(is_finish) as cancel_num FROM ( SELECT DATE(order_time) as dt, IF(start_time IS NULL, 0, 1) as is_finish FROM tb_get_car_order WHERE DATE(order_time) BETWEEN '2021-09-25' and '2021-10-03' ) as t_order_status GROUP BY dt ) as t_finish_cancel_daily)as t_finish_cancel_7dWHERE dt >= '2021-10-01';
