SQL | 多表连接 | 用户满意度/非禁止用户的取消率

一、用户满意度

satisfaction表记录了教师和学生对课程的满意程度。
satisfaction列里是老师和学生对课程的评价，其中“是”表示教师和学生都满意。

satisfaction表：
	teacher_id：老师编号
	student_id：学生编号
	satisfaction：是否满意

user表记录了学校教师和学生的信息。

 user表：
	 id：编号，主键，包括老师和学生的编号
	 insystem：是否在系统
	 role：角色

两个表的关系：satisfaction表的student_id 、teacher_id和user表的 id 联结。
分析出学校里人员对课程的满意度。满意度的计算方式如下：
(教师和学生对课程都满意且已存在当前教务系统中的用户) / (在学校里的人数)
1、创建表

CREATE TABLE IF NOT EXISTS user(
id VARCHAR(10) PRIMARY KEY,
insystem VARCHAR(10),
role VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS satisfaction(
teacher_id VARCHAR(10),
student_id VARCHAR(10),
satisfaction VARCHAR(50),
CONSTRAINT FOREIGN KEY(teacher_id) REFERENCES user(id),
CONSTRAINT FOREIGN KEY(student_id) REFERENCES user(id)
);

2、插入数据

INSERT INTO user VALUES('1','是','学生'),
					   ('2','是','学生'),
					   ('3','否','学生'),
					   ('01','是','老师'),
					   ('02','是','老师'),
					   ('03','是','老师');

INSERT INTO satisfaction VALUES('01','1','是'),
							   ('01','2','学生不满意'),
							   ('01','3','是'),
							   ('02','1','老师不满意'),
							   ('02','2','是'),
							   ('02','3','是'),
							   ('03','1','学生不满意'),
							   ('03','2','是'),
							   ('03','3','是');

3、逻辑SQL
（1）多表连接
satisfaction表的student_id 、teacher_id和user表的 id 联结，需与user表连接两次

mysql> SELECT * FROM satisfaction s LEFT JOIN user u1 ON s.teacher_id=u1.id
    -> LEFT JOIN user u2 ON s.student_id=u2.id;
+------------+------------+-------------+------+----------+--------+------+----------+--------+
| teacher_id | student_id | satisfaction| id   | insystem | role   | id   | insystem | role   |
+------------+------------+-------------+------+----------+--------+------+----------+--------+
| 01         | 1          | 是          | 01   | 是       | 老师   | 1    | 是       | 学生   |
| 01         | 2          | 学生不满意   | 01   | 是       | 老师   | 2    | 是       | 学生   |
| 01         | 3          | 是          | 01   | 是       | 老师   | 3    | 否       | 学生   |
| 02         | 1          | 老师不满意   | 02   | 是       | 老师   | 1    | 是       | 学生   |
| 02         | 2          | 是          | 02   | 是       | 老师   | 2    | 是       | 学生   |
| 02         | 3          | 是          | 02   | 是       | 老师   | 3    | 否       | 学生   |
| 03         | 1          | 学生不满意   | 03   | 是       | 老师   | 1    | 是       | 学生   |
| 03         | 2          | 是          | 03   | 是       | 老师   | 2    | 是       | 学生   |
| 03         | 3          | 是          | 03   | 是       | 老师   | 3    | 否       | 学生   |
+------------+------------+-------------+------+----------+--------+------+----------+--------+
9 rows in set (0.00 sec)

（2）筛选出在系统内的用户，且均为满意

mysql> SELECT * FROM satisfaction s LEFT JOIN user u1 ON s.teacher_id=u1.id
    -> LEFT JOIN user u2 ON s.student_id=u2.id
    -> WHERE satisfaction='是' AND u1.insystem='是' AND u2.insystem='是';
+------------+------------+--------------+------+----------+--------+------+----------+--------+
| teacher_id | student_id | satisfaction | id   | insystem | role   | id   | insystem | role   |
+------------+------------+--------------+------+----------+--------+------+----------+--------+
| 01         | 1          | 是           | 01   | 是       | 老师   | 1    | 是       | 学生   |
| 02         | 2          | 是           | 02   | 是       | 老师   | 2    | 是       | 学生   |
| 03         | 2          | 是           | 03   | 是       | 老师   | 2    | 是       | 学生   |
+------------+------------+--------------+------+----------+--------+------+----------+--------+
3 rows in set (0.00 sec)

（3）满意度计算
(教师和学生对课程都满意且已存在当前教务系统中的用户) / (在学校里的人数)
课程都满意且已存在当前教务系统中的用户需COUNT老师和学生相加的总人数
学校的里的人数COUNT总用户数
ROUND()、CONCAT()转为为百分比

mysql> SELECT CONCAT(ROUND((COUNT(DISTINCT teacher_id)+COUNT(DISTINCT student_id))/(SELECT COUNT(id) FROM user),2)*100,'%') AS '满意度'
    -> FROM satisfaction s LEFT JOIN user u1 ON s.teacher_id=u1.id
    -> LEFT JOIN user u2 ON s.student_id=u2.id
    -> WHERE satisfaction='是' AND u1.insystem='是' AND u2.insystem='是';
+-----------+
| 满意度    |
+-----------+
| 83.00%    |
+-----------+
1 row in set (0.02 sec)

二、非禁止用户的取消率

下表是一家出行公司（比如滴滴、Uber）的数据库表。乘客通过该公司的app叫车，司机通过app接收订单。

Users 表：存放的是用户信息。
	Users_Id 每个用户有唯一值 。
	Banned 表示用户是否因为违规被禁止使用app。
	Role 记录了用户的角色，里面的值driver是司机，client是乘客，partner是合伙人。

Trips 表：记录了各个出租车的行程信息。
	Id:每段行程有唯一键（Id） 。
	Client_Id 乘客id
	Driver_Id 司机id
	City_Id 城市id
	Status 行程类型  ‘completed’表行程正常结束, 
					‘cancelled_by_driver’ 表示行程因为司机原因取消，
					‘cancelled_by_client’表示行程因为乘客原因取消。
	Request_at 时间

查出非禁止用户的取消率。
取消率的计算方式如下：
(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
（1）创建表

CREATE TABLE IF NOT EXISTS Users(
Users_Id VARCHAR(255) PRIMARY KEY,
Banned VARCHAR(255),
Role VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS Trips (
Id INT,
Client_Id VARCHAR(255),
Driver_Id VARCHAR(255),
City_Id INT,
Status VARCHAR(255),
Request_at DATE,
CONSTRAINT FOREIGN KEY(Client_Id) REFERENCES Users(Users_Id),
CONSTRAINT FOREIGN KEY(Driver_Id) REFERENCES Users(Users_Id)
);

（2）插入数据

INSERT INTO Users VALUES('1','NO','client'),
						('2','NO','client'),
						('3','NO','client'),
						('4','NO','client'),
						('11','NO','driver'),
						('12','NO','driver'),
						('13','NO','driver'),
						('14','NO','driver');

INSERT INTO Trips VALUES(1,'1','11',1,'completed','2021-3-1'),
						(2,'2','12',1,'cancelled_by_driver','2021-3-1'),
						(3,'3','13',6,'completed','2021-3-1'),
						(4,'4','14',6,'cancelled_by_client','2021-3-1'),
						(5,'1','11',1,'completed','2021-3-2'),
						(6,'2','12',6,'completed','2021-3-2'),
						(7,'3','13',6,'completed','2021-3-2'),
						(8,'2','13',12,'completed','2021-3-3'),
						(9,'3','11',12,'completed','2021-3-3'),
						(10,'4','14',12,'cancelled_by_driver','2021-3-3');

（3）逻辑SQL
COUNT(CASE WHEN t.Status=‘completed’ THEN null ELSE ‘取消’ END)
或
SUM(IF(t.Status=‘completed’,0,1)）

mysql> SELECT t.Request_at,CONCAT(ROUND(COUNT(CASE WHEN t.Status='completed' THEN null ELSE '取消' END)/COUNT(1),2)*100,'%') AS '取消率'
    -> FROM Trips t LEFT JOIN Users u1 ON t.Client_Id=u1.Users_Id
    -> LEFT JOIN Users u2 ON t.Driver_Id=u2.Users_Id
    -> WHERE u1.Banned='No' AND u2.Banned='No'
    -> GROUP BY t.Request_at;
+------------+-----------+
| Request_at | 取消率    |
+------------+-----------+
| 2021-03-01 | 50.00%    |
| 2021-03-02 | 0.00%     |
| 2021-03-03 | 33.00%    |
+------------+-----------+
3 rows in set (0.00 sec)