# 179 出行场景

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

## 描述

用户打车记录表tb\_get\_car\_record

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

（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 | 201        | 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      | 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      | 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 | 2021-10-01 08:18:00 | 2021-10-01 08:31:00 | 15      | 44   | 5     |
| 6  | 9006      | 106 | 203        | 2021-10-01 18:01:00 | 2021-10-01 18:09:00 | 2021-10-01 18:31:00 | 8       | 25   | 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     |

（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年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。

**输出示例**：

示例数据的输出结果如下

| city | avg\_order\_num | avg\_income |
| ---- | --------------- | ----------- |
| 北京   | 3.500           | 121.000     |

解释：

在2021年国庆期间北京市的订单中，202共接了3单，兼职收入105；203接了4单，兼职收入137；201共接了1单，但取消了； 接单至少3次的司机有202和203，他两人全部总共接单数为7，总收入为242。因此平均接单数为3.500，平均收入为121.000；

## 示例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 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);
```

复制

输出：

```
北京|3.500|121.000
```

### 解答

1.先计算总的接单收入和兼职收入，因为聚合函数不能嵌套

2.同一个司机的接单数 可以根据司机分组然后直接计算订单id数量即可

```sql
with t_driver_info as (select count(order_id) as order_num, sum(fare) as income
                           from tb_get_car_order as o
                                    join tb_get_car_record as r using (order_id)
                           where city = '北京'
                             and date_format(order_time, "%y-%m-%d") between '2021-10-01' and '2021-10-07'
                           group by driver_id
                           having count(order_id) >= 3)
select 北京 as city,
       round(avg(order_num), 3) as avg_order_num,
       round(avg(income), 3) as avg_income
    from t_driver_info;
```

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

## 描述

现有用户打车记录表tb\_get\_car\_record

| 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
```

### 解答

### 明确题意：

找到2021年10月有过取消订单记录的司机，计算他们每人全部已完成的有评分订单的平均评分及总体平均评分，保留1位小数。

***

### 问题分解：

* 找出2021年10月有取消订单的司机：`WHERE DATE_FORMAT(order_time, "%Y-%m")='2021-10' AND ISNULL(fare)`
* 筛选他们的已完成订单的评分：WHERE driver\_id in (...) AND NOT ISNULL(grade)
* 按司机分组：GROUP BY driver\_id
* 追加汇总信息：WITH ROLLUP
* 输出每个司机的平均评分：
  * 司机ID或总体：IFNULL(driver\_id, "总体") as driver\_id
  * 平均评分：AVG(grade) as avg\_grade
  * 保留1位小数：ROUND(x, 1)

```sql
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;
```

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

## 描述

用户打车记录表tb\_get\_car\_record

| 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      | 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      | 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       | 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-打车结束时间**。
* 若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的f**inish\_time-订单完成时间**填充为取消时间，其余字段设为**null**。
* 当司机接上乘客时，填充订单表中该**start\_time-开始计费的上车时间**。
* 当订单完成时填充订单完成时间、里程数、费用；评分设为**null**，在用户给司机打1\~5星评价后填充。

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

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

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

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

2285068

示例数据的输出结果如下

| city | driver\_id | avg\_grade | avg\_order\_num | avg\_mileage |
| ---- | ---------- | ---------- | --------------- | ------------ |
| 北京   | 203        | 4.8        | 1.7             | 14.700       |

解释：

示例数据中，在北京市，共有2个司机接单，202的平均评分为4.3，203的平均评分为4.8，因此北京的最高评分的司机为203；203的共在3天里接单过，一共接单5次（包含1次接单后未完成），因此日均接单数为1.7；总行驶里程数为44.1，因此日均行驶里程数为14.700

## 示例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);
```

复制

输出：

```
北京|203|4.8|1.7|14.700
```

### 解答

### 明确题意：

统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。有多个司机评分并列最高时，都输出。

平均评分和日均接单量保留1位小数，日均行驶里程数保留3位小数，按日均接单数升序排序。

***

### 问题分解：

* 计算每个司机的评分、日均接单量、日均里程和城市内评分排名（生成子表t\_driver\_rk）：
  * 计算每个司机的评分等信息（生成子表t\_driver\_info）：
    * 关联打车记录表和订单表：tb\_get\_car\_record JOIN tb\_get\_car\_order USING(order\_id)
    * 按司机和城市分组：GROUP BY driver\_id, city
    * 计算各指标：
      * 平均评分：AVG(grade) as avg\_grade
      * 工作天数：COUNT(DISTINCT DATE(order\_time)) as work\_days
      * 接单量：COUNT(order\_time) as order\_num
      * 总行驶里程数：SUM(mileage) as toal\_mileage
  * 计算日均指标和排名：
    * 日均订单量：order\_num / work\_days as avg\_order\_num
    * 日均里程数：toal\_mileage / work\_days as avg\_mileage
    * 计算城市内的评分排名，允许并列第一：RANK() over(PARTITION BY city ORDER BY avg\_grade DESC) as rk
    * 保留小数位数：ROUND(x, 1)
* 筛选每个城市的第一名：WHERE rk = 1

***

### 细节问题：

* 表头重命名：as
* 按日均接单数升序排序：ORDER BY avg\_order\_num;

```sql
SELECT city, driver_id, avg_grade, avg_order_num, avg_mileage
FROM (
    SELECT city, driver_id, ROUND(avg_grade, 1) as avg_grade,
        ROUND(order_num / work_days, 1) as avg_order_num,
        ROUND(toal_mileage / work_days, 3) as avg_mileage,
        RANK() over(PARTITION BY city ORDER BY avg_grade DESC) as rk
    FROM (
        SELECT driver_id, city, AVG(grade) as avg_grade,
            COUNT(DISTINCT DATE(order_time)) as work_days,
            COUNT(order_time) as order_num,
            SUM(mileage) as toal_mileage
        FROM tb_get_car_record
        JOIN tb_get_car_order USING(order_id)
        GROUP BY driver_id, city
    ) as t_driver_info
) as t_driver_rk
WHERE rk = 1
ORDER BY avg_order_num;

```

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

## 描述

现有用户打车记录表tb\_get\_car\_record

| id | uid | city | event\_time         | end\_time           | order\_id |
| -- | --- | ---- | ------------------- | ------------------- | --------- |
| 1  | 101 | 北京   | 2021-09-25 08:28:10 | 2021-09-25 08:30:00 | 9011      |
| 2  | 102 | 北京   | 2021-09-25 09:00:30 | 2021-09-25 09:01:00 | 9012      |
| 3  | 103 | 北京   | 2021-09-26 07:59:00 | 2021-09-26 08:01:00 | 9013      |
| 4  | 104 | 北京   | 2021-09-26 07:59:00 | 2021-09-26 08:01:00 | 9023      |
| 5  | 104 | 北京   | 2021-09-27 07:59:20 | 2021-09-27 08:01:00 | 9014      |
| 6  | 105 | 北京   | 2021-09-28 08:00:00 | 2021-09-28 08:02:10 | 9015      |
| 7  | 106 | 北京   | 2021-09-29 17:58:00 | 2021-09-29 18:01:00 | 9016      |
| 8  | 107 | 北京   | 2021-09-30 11:00:00 | 2021-09-30 11:01:00 | 9017      |
| 9  | 108 | 北京   | 2021-09-30 21:00:00 | 2021-09-30 21:01:00 | 9018      |
| 10 | 102 | 北京   | 2021-10-01 09:00:30 | 2021-10-01 09:01:00 | 9002      |
| 11 | 106 | 北京   | 2021-10-01 17:58:00 | 2021-10-01 18:01:00 | 9006      |
| 12 | 101 | 北京   | 2021-10-02 08:28:10 | 2021-10-02 08:30:00 | 9001      |
| 13 | 107 | 北京   | 2021-10-02 11:00:00 | 2021-10-02 11:01:00 | 9007      |
| 14 | 108 | 北京   | 2021-10-02 21:00:00 | 2021-10-02 21:01:00 | 9008      |
| 15 | 103 | 北京   | 2021-10-02 07:59:00 | 2021-10-02 08:01:00 | 9003      |
| 16 | 104 | 北京   | 2021-10-03 07:59:20 | 2021-10-03 08:01:00 | 9004      |
| 17 | 109 | 北京   | 2021-10-03 18:00:00 | 2021-10-03 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  | 9011      | 101 | 211        | 2021-09-25 08:30:00 | 2021-09-25 08:31:00  | 2021-09-25 08:54:00 | 10      | 35   | 5     |
| 2  | 9012      | 102 | 211        | 2021-09-25 09:01:00 | 2021-09-25 09:01:50  | 2021-09-25 09:28:00 | 11      | 32   | 5     |
| 3  | 9013      | 103 | 212        | 2021-09-26 08:01:00 | 2021-09-26 08:03:00  | 2021-09-26 08:27:00 | 12      | 31   | 4     |
| 4  | 9023      | 104 | 213        | 2021-09-26 08:01:00 | NULL                 | 2021-09-26 08:27:00 | NULL    | NULL | NULL  |
| 5  | 9014      | 104 | 212        | 2021-09-27 08:01:00 | 2021-09-27 08:04:00  | 2021-09-27 08:21:00 | 11      | 31   | 5     |
| 6  | 9015      | 105 | 212        | 2021-09-28 08:02:10 | 2021-09-28 08:04:10  | 2021-09-28 08:25:10 | 12      | 31   | 4     |
| 7  | 9016      | 106 | 213        | 2021-09-29 18:01:00 | 2021-09-2918:02:10   | 2021-09-29 18:23:00 | 11      | 39   | 4     |
| 8  | 9017      | 107 | 213        | 2021-09-3011:01:00  | 2021-09-30 11:01:40  | 2021-09-30 11:31:00 | 11      | 38   | 5     |
| 9  | 9018      | 108 | 214        | 2021-09-30 21:01:00 | 2021-09-30 21:02:50  | 2021-09-30 21:21:00 | 14      | 38   | 5     |
| 10 | 9002      | 102 | 202        | 2021-10-01 09:01:00 | 2021-10-01 0 9:06:00 | 2021-10-01 09:31:00 | 10      | 41.5 | 5     |
| 11 | 9006      | 106 | 203        | 2021-10-0118:01:00  | 2021-10-01 18:09:00  | 2021-10-01 18:31:00 | 8       | 25.5 | 4     |
| 12 | 9001      | 101 | 202        | 2021-10-02 08:30:00 | NULL                 | 2021-10-02 08:31:00 | NULL    | NULL | NULL  |
| 13 | 9007      | 107 | 203        | 2021-10-02 11:01:00 | 2021-10-0211:07:00   | 2021-10-02 11:31:00 | 9.9     | 30   | 5     |
| 14 | 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     |
| 15 | 9003      | 103 | 202        | 2021-10-02 08:01:00 | 2021-10-02 08:15:00  | 2021-10-02 08:31:00 | 11      | 41.5 | 4     |
| 16 | 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     |
| 17 | 9009      | 109 | 204        | 2021-10-0318:01:00  | NULL                 | 2021-10-03 18:51:00 | NULL    | NULL | NULL  |

（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星评价后填充。

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

**输出示例**：

示例输出如下

| dt         | finish\_num\_7d | cancel\_num\_7d |
| ---------- | --------------- | --------------- |
| 2021-10-01 | 1.43            | 0.14            |
| 2021-10-02 | 1.57            | 0.29            |
| 2021-10-03 | 1.57            | 0.29            |

解释：

2021年9月25到10月3日每天的订单完成量为：2、1、1、1、1、2、2、3、1；每天的订单取消量为：0、1、0、0、0、0、0、1、1；

因此10.1到10.3期间的近7日订单完成量分别为10、11、11，因此日均订单完成量为：1.43、1.57、1.57；

近7日订单取消量分别为1、2、2，因此日均订单取消量为0.14、0.29、0.29；

## 示例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-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);
```

复制

输出：

```
2021-10-01|1.43|0.14
2021-10-02|1.57|0.29
2021-10-03|1.57|0.29
```

### 解答

```sql
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(select date(order_time) dt,
            sum(case when start_time is not null then 1 else 0 end) as finish_num,
            sum(case when start_time is null then 1 else 0 end) as cancel_num
          from tb_get_car_order
          group by date(order_time)
          order by dt) t ) a 
where dt between '2021-10-01' and '2021-10-03'


```

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

## 描述

用户打车记录表tb\_get\_car\_record

| id | uid | city | event\_time         | end\_time           | order\_id |
| -- | --- | ---- | ------------------- | ------------------- | --------- |
| 1  | 107 | 北京   | 2021-09-20 11:00:00 | 2021-09-20 11:00:30 | 9017      |
| 2  | 108 | 北京   | 2021-09-20 21:00:00 | 2021-09-20 21:00:40 | 9008      |
| 3  | 108 | 北京   | 2021-09-20 18:59:30 | 2021-09-20 19:01:00 | 9018      |
| 4  | 102 | 北京   | 2021-09-21 08:59:00 | 2021-09-21 09:01:00 | 9002      |
| 5  | 106 | 北京   | 2021-09-21 17:58:00 | 2021-09-21 18:01:00 | 9006      |
| 6  | 103 | 北京   | 2021-09-22 07:58:00 | 2021-09-22 08:01:00 | 9003      |
| 7  | 104 | 北京   | 2021-09-23 07:59:00 | 2021-09-23 08:01:00 | 9004      |
| 8  | 103 | 北京   | 2021-09-24 19:59:20 | 2021-09-24 20:01:00 | 9019      |
| 9  | 101 | 北京   | 2021-09-24 08:28:10 | 2021-09-24 08:30:00 | 9011      |

（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  | 9017      | 107 | 213        | 2021-09-20 11:00:30 | 2021-09-20 11:02:10 | 2021-09-20 11:31:00 | 11      | 38   | 5     |
| 2  | 9008      | 108 | 204        | 2021-09-20 21:00:40 | 2021-09-20 21:03:00 | 2021-09-20 21:31:00 | 13.2    | 38   | 4     |
| 3  | 9018      | 108 | 214        | 2021-09-20 19:01:00 | 2021-09-20 19:04:50 | 2021-09-20 19:21:00 | 14      | 38   | 5     |
| 4  | 9002      | 102 | 202        | 2021-09-21 09:01:00 | 2021-09-21 09:06:00 | 2021-09-21 09:31:00 | 10      | 41.5 | 5     |
| 5  | 9006      | 106 | 203        | 2021-09-21 18:01:00 | 2021-09-21 18:09:00 | 2021-09-21 18:31:00 | 8       | 25.5 | 4     |
| 6  | 9007      | 107 | 203        | 2021-09-22 11:01:00 | 2021-09-22 11:07:00 | 2021-09-22 11:31:00 | 9.9     | 30   | 5     |
| 7  | 9003      | 103 | 202        | 2021-09-22 08:01:00 | 2021-10-22 08:15:00 | 2021-10-22 08:31:00 | 11      | 41.5 | 4     |
| 8  | 9004      | 104 | 202        | 2021-09-23 08:01:00 | 2021-09-23 08:13:00 | 2021-09-23 08:31:00 | 7.5     | 22   | 4     |
| 9  | 9005      | 105 | 202        | 2021-09-23 10:01:00 | 2021-09-23 10:13:00 | 2021-09-23 10:31:00 | 9       | 29   | 5     |
| 10 | 9019      | 103 | 202        | 2021-09-24 20:01:00 | 2021-09-24 20:11:00 | 2021-09-24 20:51:00 | 10      | 39   | 4     |
| 11 | 9011      | 101 | 211        | 2021-09-24 08:30:00 | 2021-09-24 08:31:00 | 2021-09-24 08:54:00 | 10      | 35   | 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星评价后填充。

**问题**：统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以**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）
* 时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）
* 从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间。

**输出示例**：

示例数据的输出结果如下：

| period | get\_car\_num | avg\_wait\_time | avg\_dispatch\_time |
| ------ | ------------- | --------------- | ------------------- |
| 工作时间   | 1             | 0.5             | 1.7                 |
| 休息时间   | 1             | 0.7             | 2.3                 |
| 晚高峰    | 3             | 2.1             | 7.3                 |
| 早高峰    | 4             | 2.2             | 8.0                 |

解释：订单9017打车开始于11点整，属于工作时间，等待时间30秒，调度时间为1分40秒，示例数据中工作时间打车订单就一个，平均等待时间0.5分钟，平均调度时间1.7分钟。

## 示例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
 (107, '北京', '2021-09-20 11:00:00', '2021-09-20 11:00:30', 9017),
 (108, '北京', '2021-09-20 21:00:00', '2021-09-20 21:00:40', 9008),
 (108, '北京', '2021-09-20 18:59:30', '2021-09-20 19:01:00', 9018),
 (102, '北京', '2021-09-21 08:59:00', '2021-09-21 09:01:00', 9002),
 (106, '北京', '2021-09-21 17:58:00', '2021-09-21 18:01:00', 9006),
 (103, '北京', '2021-09-22 07:58:00', '2021-09-22 08:01:00', 9003),
 (104, '北京', '2021-09-23 07:59:00', '2021-09-23 08:01:00', 9004),
 (103, '北京', '2021-09-24 19:59:20', '2021-09-24 20:01:00', 9019),
 (101, '北京', '2021-09-24 08:28:10', '2021-09-24 08:30:00', 9011);

INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES
 (9017, 107, 213, '2021-09-20 11:00:30', '2021-09-20 11:02:10', '2021-09-20 11:31:00', 11, 38, 5),
 (9008, 108, 204, '2021-09-20 21:00:40', '2021-09-20 21:03:00', '2021-09-20 21:31:00', 13.2, 38, 4),
 (9018, 108, 214, '2021-09-20 19:01:00', '2021-09-20 19:04:50', '2021-09-20 19:21:00', 14, 38, 5),
 (9002, 102, 202, '2021-09-21 09:01:00', '2021-09-21 09:06:00', '2021-09-21 09:31:00', 10.0, 41.5, 5),
 (9006, 106, 203, '2021-09-21 18:01:00', '2021-09-21 18:09:00', '2021-09-21 18:31:00', 8.0, 25.5, 4),
 (9007, 107, 203, '2021-09-22 11:01:00', '2021-09-22 11:07:00', '2021-09-22 11:31:00', 9.9, 30, 5),
 (9003, 103, 202, '2021-09-22 08:01:00', '2021-09-22 08:15:00', '2021-09-22 08:31:00', 11.0, 41.5, 4),
 (9004, 104, 202, '2021-09-23 08:01:00', '2021-09-23 08:13:00', '2021-09-23 08:31:00', 7.5, 22, 4),
 (9005, 105, 202, '2021-09-23 10:01:00', '2021-09-23 10:13:00', '2021-09-23 10:31:00', 9, 29, 5),
 (9019, 103, 202, '2021-09-24 20:01:00', '2021-09-24 20:11:00', '2021-09-24 20:51:00', 10, 39, 4),
 (9011, 101, 211, '2021-09-24 08:30:00', '2021-09-24 08:31:00', '2021-09-24 08:54:00', 10, 35, 5);
```

复制

输出：

```
工作时间|1|0.5|1.7
休息时间|1|0.7|2.3
晚高峰|3|2.1|7.3
早高峰|4|2.2|8.0
```

### 解答

### 明确题意：

统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。

全部以开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。

***

### 问题分解：

* 计算每次叫车的等待时间和调度时间（生成子表t\_wait\_dispatch\_time）：
  * 关联打车记录和订单表：tb\_get\_car\_record JOIN tb\_get\_car\_order USING(order\_id)
  * 筛选工作日的记录（周一到周五）：WHERE DAYOFWEEK(event\_time) BETWEEN 2 AND 6
  * 转换打车时间所属时段：CASE WHEN HOUR(event\_time) IN (7, 8) THEN '早高峰' ... END as period
  * 计算等待接单时间：TIMESTAMPDIFF(SECOND, event\_time, end\_time) as wait\_time
  * 计算调度时间：TIMESTAMPDIFF(SECOND, order\_time, start\_time) as dispatch\_time
* 按时段分组：GROUP BY period
* 计算叫车量：COUNT(1) as get\_car\_num
* 计算平均等待接单时间：AVG(wait\_time/60) as avg\_wait\_time
* 计算平均调度时间：AVG(dispatch\_time/60) as avg\_dispatch\_time
* 保留1位小数：ROUND(x, 1)

***

### 细节问题：

* 表头重命名：as
* 按叫车量升序排序：ORDER BY get\_car\_num

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

```

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

## 描述

用户打车记录表tb\_get\_car\_record

| id | uid | city | event\_time         | end\_time           | order\_id |
| -- | --- | ---- | ------------------- | ------------------- | --------- |
| 1  | 108 | 北京   | 2021-10-20 08:00:00 | 2021-10-20 08:00:40 | 9008      |
| 2  | 118 | 北京   | 2021-10-20 08:00:10 | 2021-10-20 08:00:45 | 9018      |
| 3  | 102 | 北京   | 2021-10-20 08:00:30 | 2021-10-20 08:00:50 | 9002      |
| 4  | 106 | 北京   | 2021-10-20 08:05:41 | 2021-10-20 08:06:00 | 9006      |
| 5  | 103 | 北京   | 2021-10-20 08:05:50 | 2021-10-20 08:07:10 | 9003      |
| 6  | 104 | 北京   | 2021-10-20 08:01:01 | 2021-10-20 08:01:20 | 9004      |
| 7  | 105 | 北京   | 2021-10-20 08:01:15 | 2021-10-20 08:01:30 | 9019      |
| 8  | 101 | 北京   | 2021-10-20 08:28:10 | 2021-10-20 08:30:00 | 9011      |

（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  | 9008      | 108 | 204        | 2021-10-20 08:00:40 | 2021-10-20 08:03:00 | 2021-10-20 08:31:00 | 13.2    | 38   | 4     |
| 2  | 9018      | 108 | 214        | 2021-10-20 08:00:45 | 2021-10-20 08:04:50 | 2021-10-20 08:21:00 | 14      | 38   | 5     |
| 3  | 9002      | 102 | 202        | 2021-10-20 08:00:50 | 2021-10-20 08:06:00 | 2021-10-20 08:31:00 | 10      | 41.5 | 5     |
| 4  | 9006      | 106 | 206        | 2021-10-20 08:06:00 | 2021-10-20 08:09:00 | 2021-10-20 08:31:00 | 8       | 25.5 | 4     |
| 5  | 9003      | 103 | 203        | 2021-10-20 08:07:10 | 2021-10-20 08:15:00 | 2021-10-20 08:31:00 | 11      | 41.5 | 4     |
| 6  | 9004      | 104 | 204        | 2021-10-20 08:01:20 | 2021-10-20 08:13:00 | 2021-10-20 08:31:00 | 7.5     | 22   | 4     |
| 7  | 9019      | 105 | 205        | 2021-10-20 08:01:30 | 2021-10-20 08:11:00 | 2021-10-20 08:51:00 | 10      | 39   | 4     |
| 8  | 9011      | 101 | 211        | 2021-10-20 08:30:00 | 2021-10-20 08:31:00 | 2021-10-20 08:54:00 | 10      | 35   | 5     |

（order\_id-订单号, uid-用户ID, driver\_id-司机ID, order\_time-接单时间, start\_time-开始计费的上车时间, finish\_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分）

**场景逻辑说明**：

* 用户提交打车请求后，在用户打车记录表生成一条打车记录，**订单号-order\_id**设为**null**；
* 当有司机接单时，在打车订单表生成一条订单，**填充接单时间-order\_time及其左边的字段**，上车时间及其右边的字段全部为**null**，并把订单号和接单时间（打车结束时间）写入打车记录表；若一直无司机接单、超时或中途用户主动取消打车，则记录打车结束时间。
* 若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的**订单完成时间**-**finish\_time填充为取消时间**，其余字段设为**null**。
* 当司机接上乘客时，填充打车订单表中该订单的**上车时间start\_time**。
* 当订单完成时填充订单完成时间、里程数、费用；评分设为**null**，在用户给司机打1\~5星评价后填充。

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

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

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

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

**输出示例**：

示例结果如下

| city | max\_wait\_uv |
| ---- | ------------- |
| 北京   | 5             |

解释：由打车订单表可以得知北京2021年10月20日有8条打车记录，108号乘客从08:00:00等到08:03:00，118号乘客从08:00:10等到08:04:50....,由此得知08:02:00秒时刻，共有5人在等车。

## 示例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
 (108, '北京', '2021-10-20 08:00:00', '2021-10-20 08:00:40', 9008),
 (108, '北京', '2021-10-20 08:00:10', '2021-10-20 08:00:45', 9018),
 (102, '北京', '2021-10-20 08:00:30', '2021-10-20 08:00:50', 9002),
 (106, '北京', '2021-10-20 08:05:41', '2021-10-20 08:06:00', 9006),
 (103, '北京', '2021-10-20 08:05:50', '2021-10-20 08:07:10', 9003),
 (104, '北京', '2021-10-20 08:01:01', '2021-10-20 08:01:20', 9004),
 (103, '北京', '2021-10-20 08:01:15', '2021-10-20 08:01:30', 9019),
 (101, '北京', '2021-10-20 08:28:10', '2021-10-20 08:30:00', 9011);

INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade) VALUES
 (9008, 108, 204, '2021-10-20 08:00:40', '2021-10-20 08:03:00', '2021-10-20 08:31:00', 13.2, 38, 4),
 (9018, 108, 214, '2021-10-20 08:00:45', '2021-10-20 08:04:50', '2021-10-20 08:21:00', 14, 38, 5),
 (9002, 102, 202, '2021-10-20 08:00:50', '2021-10-20 08:06:00', '2021-10-20 08:31:00', 10.0, 41.5, 5),
 (9006, 106, 203, '2021-10-20 08:06:00', '2021-10-20 08:09:00', '2021-10-20 08:31:00', 8.0, 25.5, 4),
 (9003, 103, 202, '2021-10-20 08:07:10', '2021-10-20 08:15:00', '2021-10-20 08:31:00', 11.0, 41.5, 4),
 (9004, 104, 202, '2021-10-20 08:01:20', '2021-10-20 08:13:00', '2021-10-20 08:31:00', 7.5, 22, 4),
 (9019, 103, 202, '2021-10-20 08:01:30', '2021-10-20 08:11:00', '2021-10-20 08:51:00', 10, 39, 4),
 (9011, 101, 211, '2021-10-20 08:30:00', '2021-10-20 08:31:00', '2021-10-20 08:54:00', 10, 35, 5);
```

复制

输出：

```
北京|5
```

### 解答

## 一、知识点总结与拓展

感觉本题主要的考点是对瞬时在线用户数这种业务的理解。用到SQL的方法相对简单，只要是union的用法，除此之外还用大了ifnull()函数。

* union 去重连接；union all 全连接不去重
* ifnull(表达式1,表达式2)：如果表达式1为空则返回表达式2，不为空则返回表达式1
* 瞬时UV的计算方法：进入事件记作UV=1，离开事件记作UV=-1，使用窗口函数SUM(uv)OVER(ORDER BY 事件，uv DESC)进行累加。

## 二、题目解读

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

这是一道典型的统计同时在线用户数的题，和统计直播间在线用户数的逻辑是一样的。进入的用户定义uv为1，离开的用户定义uv为-1。

因而，**需要对用户进入和离开的时间进行定义**。

**进入时间**：event\_time,开始打车的时间即为等车开始。

**离开时间**：有3种情况

* 状态1：司机接单前取消，则没有生成order\_id,这种情况 order\_id IS NULL 记录end\_time
* 状态2：司机接单后取消，则没有上车时间，start\_time IS NULL 记录 finish\_time
* 状态3：正常上车，记录start\_time,start\_time IS NOT NULL
* 状态2和3可以直接使用IFNULL()合并，IFNULL(start\_time,finish\_time) 如果start\_time空则返回finish\_time，不空则start\_time

定义完用户进入等车和离开等车这两种事件之后，关联所有表格，使用窗口函数排序累加即可。

* **具体的状态的数据可以看下面的图**

![img](https://uploadfiles.nowcoder.com/images/20220411/235454159_1649649313469/1F460BD7361EC80AA9D5B4F018305233)

![img](https://raw.githubusercontent.com/qkd90/figureBed/main/202407121735469.png)

## 三、解题步骤

解题思路：整个解题过程只要3个步骤，首先建立一张子表，对用户进入等车状态和离开等车状态进行定义后进行表并联；接着使用窗口函数对每个城市的等车状态进出uv进行累加，最后取出每个城市最大的UV即可。

\*\*1）建立子表，\*\***对用户进入等车状态和离开等车状态进行定义后进行表并联**

* uv为1，用户进入打车状态

```
SELECT city,event_time uv_time,``1` `AS uv FROM tb_get_car_record
```

* uv为-1，状态1：司机接单前取消，则没有生成order\_id,这种情况 order\_id IS NULL 记录end\_time

```
SELECT city,end_time uv_time,-``1` `AS uv FROM tb_get_car_record WHERE order_id IS NULL #接单前取消
```

* uv为-1，状态2：接单后取消或者用户正常上车

```
SELECT city,IFNULL(start_time,finish_time) uv_time,-``1` `AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车
```

* 建立子表：使用union all 全关联

```
SELECT city,event_time uv_time,``1` `AS uv FROM tb_get_car_record ``UNION ALL``SELECT city,end_time uv_time,-``1` `AS uv FROM tb_get_car_record WHERE order_id IS NULL #接单前取消``UNION ALL``SELECT city,IFNULL(start_time,finish_time) uv_time,-``1` `AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车
```

![img](https://raw.githubusercontent.com/qkd90/figureBed/main/202407121735416.png)

**2）使用窗口函数对每个城市的等车状态进出uv进行累加**

* 统计2021年10月，每个城市的瞬时UV情况

```
SELECT city,SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt
DATE_FORMAT(uv_time,``'%Y%m'``)=``'202110'
```

**3）最后取出每个城市最大的UV，排序先按照uv升序，uv一样按照城市升序。**

```sql
WITH t1 AS(
	SELECT city,SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt #每个城市等车瞬时UV
    FROM (
    	SELECT city,event_time uv_time,1 AS uv FROM  tb_get_car_record #进入等车状态
		UNION ALL
		SELECT city,end_time uv_time,-1 AS uv FROM  tb_get_car_record WHERE order_id IS NULL #接单前取消
		UNION ALL
		SELECT city,IFNULL(start_time,finish_time) uv_time,-1 AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车
    )AS t WHERE DATE_FORMAT(uv_time,'%Y%m')='202110' #2021年10月
)
SELECT city,MAX(uv_cnt) max_wait_uv FROM t1 GROUP BY citY ORDER BY max_wait_uv,citY;#排序先按照uv升序，uv一样按照城市升序
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qiangrens-organization.gitbook.io/qkd90/shu-ju-ku-he-zhong-jian-jian/sql-yu-ju/174179-chu-xing-chang-jing.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.