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

id	uid	city	event_time	end_time	order_id
1	101	北京	2021-10-01 07:00:00	2021-10-01 07:02:00	NULL
2	102	北京	2021-10-01 09:00:30	2021-10-01 09:01:00	9001
3	101	北京	2021-10-01 08:28:10	2021-10-01 08:30:00	9002
4	103	北京	2021-10-02 07:59:00	2021-10-02 08:01:00	9003
5	104	北京	2021-10-03 07:59:20	2021-10-03 08:01:00	9004
6	105	北京	2021-10-01 08:00:00	2021-10-01 08:02:10	9005
7	106	北京	2021-10-01 17:58:00	2021-10-01 18:01:00	9006
8	107	北京	2021-10-02 11:00:00	2021-10-02 11:01:00	9007
9	108	北京	2021-10-02 21:00:00	2021-10-02 21:01:00	9008
10	109	北京	2021-10-08 18:00:00	2021-10-08 18:01:00	9009

（uid-用户ID, city-城市, event_time-打车时间, end_time-打车结束时间, order_id-订单号）

打车订单表tb_get_car_order

id	order_id	uid	driver_id	order_time	start_time	finish_time	mileage	fare	grade
1	9002	101	202	2021-10-01 08:30:00	null	2021-10-01 08:31:00	null	null	null
2	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
3	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
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
5	9005	105	203	2021-10-01 08:02:10	null	2021-10-01 08:31:00	null	null	null
6	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
7	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
8	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
9	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-订单号, uid-用户ID, driver_id-司机ID, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分）

场景逻辑说明：

用户提交打车请求后，在用户打车记录表生成一条打车记录，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升序输出，再输出总体情况。

输出示例:

示例数据的输出结果如下

driver_id	avg_grade
202	4.3
203	4.8
总体	4.6

解释：

2021年国庆有未完成订单的司机有202和203；202的所有订单评分有：5、4、4，平均分为4.3；203的所有订单评分有：5、5、4、5，平均评分为4.8；总体平均评分为(5+4+4+5+5+4+5)/7=4.6

示例1

输入:

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);

输出:

202|4.3
203|4.8
总体|4.6

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6388KB, 提交时间: 2022-01-24

/**
select
driver_id as id,
round(sum(grade)/count(*),1) as fen
FROM tb_get_car_order 
where driver_id in (
select 
driver_id
from 
  tb_get_car_record r join tb_get_car_order o using(order_id)
  where date(event_time) between '2021-10-01' and '2021-10-31' 
  and start_time is  null ) and start_time is not null
  group by driver_id 
  
  union 
  
 select
'总体' as id,
round(sum(grade)/count(*),1) as fen
FROM tb_get_car_order 
where driver_id in (
select 
driver_id
from 
  tb_get_car_record r join tb_get_car_order o using(order_id)
  where date(event_time) between '2021-10-01' and '2021-10-31' 
  and start_time is  null ) and start_time is not null */
  
  select 
  COALESCE(driver_id,'总体'),
  round(sum(grade)/count(*),1)
  from 
  tb_get_car_order
  where driver_id in (
  select 
  driver_id
  from 
  tb_get_car_order  
  where start_time is null and DATE_FORMAT(order_time,'%Y-%m')= '2021-10'
  ) and start_time is not null group by driver_id
  with rollup

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2021-12-19

select
coalesce(o.driver_id,'总体') as driver_id,
round(avg(o.grade),1) as avg_grade
from tb_get_car_order o
where driver_id in(
             select distinct driver_id 
             from tb_get_car_order 
             where date_format(order_time,'%Y%m')=202110 and start_time is null)
group by o.driver_id with rollup

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2022-02-09

with t as (
select driver_id,grade
from tb_get_car_order 
where grade is not null
and driver_id in (select distinct driver_id 
                 from tb_get_car_order 
                 where grade is null))
(
select driver_id,round(avg(grade),1) avg_grade
from t 
group by driver_id
order by driver_id
)
union
(select "总体",round(sum(grade)/count(*),1)
from t)

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2021-12-20

SELECT IFNULL(driver_id, "总体") as driver_id,
    ROUND(AVG(grade), 1) as avg_grade
FROM tb_get_car_order
WHERE driver_id in (
    SELECT driver_id
    FROM tb_get_car_order
    WHERE DATE_FORMAT(order_time, "%Y-%m")='2021-10' AND ISNULL(fare)
) AND NOT ISNULL(grade)
GROUP BY driver_id
WITH ROLLUP;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2021-12-08

# 找到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 order_time is not NULL and start_time is null
AND DATE(order_time) BETWEEN '2021-10-01' and '2021-10-31')
GROUP BY driver_id)

UNION ALL

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 order_time is not NULL and start_time is null
AND DATE(order_time) BETWEEN '2021-10-01' and '2021-10-31')

上一题

下一题

详情