Datawhale-MySQL-任務六（複雜專案）

專案十：行程和使用者（難度：困難）

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

作業程式碼：

-- 建立Trips表
CREATE TABLE Trips(
id INT PRIMARY KEY,
Client_id INT,
Driver_id INT,
City_id INT,
Status ENUM('completed','cancelled_by_driver','cancelled_by_client'),
Request_at VARCHAR(50)
);

-- 插入資料
INSERT INTO Trips VALUES ('1', '1', '10', '1', 'completed', '2013-10-01');
INSERT INTO Trips VALUES ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
INSERT INTO Trips VALUES ('3', '3', '12', '6', 'completed', '2013-10-01');
INSERT INTO Trips VALUES ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
INSERT INTO Trips VALUES ('5', '1', '10', '1', 'completed', '2013-10-02');
INSERT INTO Trips VALUES ('6', '2', '11', '6', 'completed', '2013-10-02');
INSERT INTO Trips VALUES ('7', '3', '12', '6', 'completed', '2013-10-02');
INSERT INTO Trips VALUES ('8', '2', '12', '12', 'completed', '2013-10-03');
INSERT INTO Trips VALUES ('9', '3', '10', '12', 'completed', '2013-10-03');
INSERT INTO Trips VALUES ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');

-- 檢視Trips表
SELECT * FROM Trips; 

-- 建立Users表
CREATE TABLE Users(
Users_id INT PRIMARY KEY,
Banned varchar(20),
Role ENUM('client','driver','partner')
);

-- 插入資料
INSERT INTO Users VALUES ('1',  'No',  'client');
INSERT INTO Users VALUES ('2',  'Yes', 'client');
INSERT INTO Users VALUES ('3',  'No',  'client');
INSERT INTO Users VALUES ('4',  'No',  'client');
INSERT INTO Users VALUES ('10', 'No',  'driver');
INSERT INTO Users VALUES ('11', 'No',  'driver');
INSERT INTO Users VALUES ('12', 'No',  'driver');
INSERT INTO Users VALUES ('13', 'No',  'driver');

-- 檢視Users表
SELECT * FROM users;

-- 作業解答
SELECT t.Request_at AS Day,
ROUND(sum((CASE WHEN t.Status LIKE 'cancelled%' THEN 1 ELSE 0 END))/count(*),2) AS 'Cancellation Rate' -- 如果是取消的就為1，否則為0，sum求和後除以當天的總單數，即為取消率。Round函式用來保留兩位小數。
FROM Trips t
INNER JOIN Users u ON u.Users_Id =t.Client_Id AND u.Banned = 'No' -- 連線兩張表
GROUP BY t.Request_at; -- 以訂單時間分組

執行結果：

專案十一：各部門前3高工資的員工（難度：中等）

將昨天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高工資的員工功能。
作業程式碼：要想實現各部門前N高只要替換where條件中的<3即可，前幾就是小於幾

-- 清空昨天的employee表
TRUNCATE TABLE employee;

SELECT *FROM employee

-- 插入資料
INSERT INTO employee VALUES (1,'Joe',70000,1);
INSERT INTO employee VALUES (2,'Henry',80000,2);
INSERT INTO employee VALUES (3,'Sam',60000,2);
INSERT INTO employee VALUES (4,'Max',90000,1);
INSERT INTO employee VALUES (5,'Janet',69000,1);
INSERT INTO employee VALUES (6,'Randy',85000,1);

-- 作業解答，
SELECT d.`Name` AS Department,e1.`Name` AS Employee,e1.Salary AS Salary
FROM employee e1
JOIN department d
ON e1.DepartmentId = d.id
WHERE (
SELECT COUNT(DISTINCT e2.Salary)
FROM employee e2 
WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
) <3
ORDER BY d.`Name`,e1.Salary desc;

執行結果：

專案十二：分數排名（難度:中等）

依然是昨天的分數表，實現排名功能，但是排名是非連續的，如下：

Score	Rank
4.00	1
4.00	1
3.85	3
3.65	4
3.65	4
3.50	6

作業程式碼：
因為需要不連續的，所以count(*)取表中總記錄數即可，之後統計多少個人比這個分數高，對結果+1後即為排名。因為比如對於最高分，沒有人比他高，所以結果是0，排名需要+1才可以。並且使用format函式可以對結果強制保留幾位小數輸出。

SELECT FORMAT(Score,2),
(SELECT count(*) FROM Score AS s2 WHERE s2.Score > s1.Score)+1 AS Rank 
FROM Score AS s1
ORDER BY Score DESC;

執行結果：