「MySQL基础」Day6 复杂项目

2019-03-04

1. 任务背景

Datawhale组队学习中MySQL一周学习任务，通过一周组队自主学习，快速熟悉MySQL相关基础知识，并能掌握基本的数据查询操作方式

1.1 任务内容

这次是纯作业形式

2. 作业一行程和用户（难度：困难）

2.1 任务内容

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03| 
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

2.2 建表和数据导入

CREATE TABLE IF NOT EXISTS `trips` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL DEFAULT 0,
    driver_id INT NOT NULL DEFAULT 0,
    city_id INT NOT NULL DEFAULT 0,
    status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client') NOT NULL DEFAULT 'completed',
    request_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day6作业一trips表';
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (1, 10, 1, 'completed', '2013-10-01');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (2, 11, 1, 'cancelled_by_driver', '2013-10-01');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (3, 12, 6, 'completed', '2013-10-01');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (4, 13, 6, 'cancelled_by_driver', '2013-10-01');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (1, 10, 1, 'completed', '2013-10-02');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (2, 11, 6, 'completed', '2013-10-02');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (3, 12, 6, 'completed', '2013-10-02');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (2, 12, 12, 'completed', '2013-10-03');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (3, 10, 12, 'completed', '2013-10-03');
INSERT INTO trips(`client_id`, `driver_id`, `city_id`, `status`, `request_at`) VALUE (4, 13, 12, 'cancelled_by_driver', '2013-10-03');

CREATE TABLE IF NOT EXISTS `users` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    users_id INT NOT NULL DEFAULT 0,
    banned ENUM('No', 'Yes') NOT NULL DEFAULT 'No',
    role ENUM('client', 'driver', 'partner') NOT NULL DEFAULT 'client',
    UNIQUE `UNIQ_USER_ID`(`users_id`)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day6作业一users表';
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (1, 'No', 'client');
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (2, 'Yes', 'client');
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (3, 'No', 'client');
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (4, 'No', 'client');
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (10, 'No', 'driver');
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (11, 'No', 'driver');
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (12, 'No', 'driver');
INSERT INTO users(`users_id`, `banned`, `role`) VALUE (13, 'No', 'driver');

2.3 作业思路

这个其实就是联结两个表，然后根据不同的条件找出所需要的值，对于这里判断是不是取消的，可以用CASE WHEN的方式配合SUM函数来统计

2.4 代码实现

SELECT request_at AS 'Day',
       ROUND(SUM(CASE WHEN trips.status = 'cancelled_by_driver' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 2) AS 'Cancellation Rate'
FROM trips
INNER JOIN users
ON users.users_id = trips.client_id
     AND users.banned = 'No'
     AND users.role = 'client'
GROUP BY trips.request_at;

结果如下

+---------------------+-------------------+
| Day                 | Cancellation Rate |
+---------------------+-------------------+
| 2013-10-01 00:00:00 |              0.33 |
| 2013-10-02 00:00:00 |              0.00 |
| 2013-10-03 00:00:00 |              0.50 |
+---------------------+-------------------+
3 rows in set (0.01 sec)

3. 作业二各部门前3高工资的员工（难度：中等）

3.1 作业内容

将昨天employee表清空，重新插入以下数据（其实是多插入5,6两行）：
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

此外，请考虑实现各部门前N高工资的员工功能。

3.2 建表和数据导入

TRUNCATE employee;

INSERT INTO employee(`name`, `salary`, `department_id`) VALUE ('Joe', 70000, 1);
INSERT INTO employee(`name`, `salary`, `department_id`) VALUE ('Henry', 80000, 2);
INSERT INTO employee(`name`, `salary`, `department_id`) VALUE ('Sam', 60000, 2);
INSERT INTO employee(`name`, `salary`, `department_id`) VALUE ('Max', 90000, 1);
INSERT INTO employee(`name`, `salary`, `department_id`) VALUE ('Janet', 69000, 1);
INSERT INTO employee(`name`, `salary`, `department_id`) VALUE ('Randy', 85000, 1);

3.3 作业思路

可以反复查找employee表两次，取top3，可以理解为最多只有2个比其值大，或者说比其值大的少于3个

3.4 代码实现

SELECT department.name as Department, e1.name, e1.salary
FROM employee e1
INNER JOIN department
ON department_id = department.id
WHERE 3 > (
  SELECT COUNT(DISTINCT e2.name) FROM employee e2
  WHERE e2.salary > e1.salary AND e2.department_id = e1.department_id
  )
ORDER BY Department, e1.salary DESC;

结果如下

+------------+-------+--------+
| Department | name  | salary |
+------------+-------+--------+
| IT         | Max   |  90000 |
| IT         | Randy |  85000 |
| IT         | Joe   |  70000 |
| Sales      | Henry |  80000 |
| Sales      | Sam   |  60000 |
+------------+-------+--------+
5 rows in set (0.01 sec)

4. 作业三分数排名 - （难度：中等）

4.1 作业内容

依然是昨天的分数表，实现排名功能，但是排名是非连续的，如下：

+-------+------+

| Score | Rank |

+-------+------+

| 4.00  | 1    |

| 4.00  | 1    |

| 3.85  | 3    |

| 3.65  | 4    |

| 3.65  | 4    |

| 3.50  | 6    |

+-------+------+

4.2 建表和数据导入

因为表格已经建立，此时不需要再额外建表

4.3 作业思路

统计不连续的排名，直接看有多少成绩比其大即可

4.4 代码实现

SELECT s1.score AS Score,
       ((SELECT COUNT(*) FROM scores s2 where S2.score > s1.score) + 1) AS Rank
FROM scores s1
ORDER BY Rank;

结果如下

+-------+------+
| Score | Rank |
+-------+------+
|  4.00 |    1 |
|  4.00 |    1 |
|  3.85 |    3 |
|  3.65 |    4 |
|  3.65 |    4 |
|  3.50 |    6 |
+-------+------+
6 rows in set (0.00 sec)

1. 任务背景

1.1 任务内容

2. 作业一 行程和用户（难度：困难）

2.1 任务内容

2.2 建表和数据导入

2.3 作业思路

2.4 代码实现

3. 作业二 各部门前3高工资的员工（难度：中等）

3.1 作业内容

3.2 建表和数据导入

3.3 作业思路

3.4 代码实现

4. 作业三 分数排名 - （难度：中等）

4.1 作业内容

4.2 建表和数据导入

4.3 作业思路

4.4 代码实现

2. 作业一行程和用户（难度：困难）

3. 作业二各部门前3高工资的员工（难度：中等）

4. 作业三分数排名 - （难度：中等）