# 图解面试题：如何分析用户满意度？

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV7-L7YgQXDrGAc2utY%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_1.jpg?alt=media\&token=9fc75cea-741e-4ce0-b151-3941941a61ef)

**【题目】**

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV7-OzuwP3GL51kLQxm%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_2.png?alt=media\&token=f88384c7-a359-4f02-9280-a6e798b7e0b3)

“用户表” 记录了学校教师和学生的信息。每个用户有唯一键 “编号”，“是否在系统” 表示这个用户是否还在这所学校里，“角色” 表示这个人是学生还是教师。

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV7-S3sV8CPDXHismUt%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_3.png?alt=media\&token=f5d7c526-c3fd-457e-b05b-94a9406d89e8)

两个表的关系：满意度表的 “学生编号” 、 “教师编号” 和用户表的 “编号” 联结。

现在需要分析出学校里人员对课程的满意度。满意度的计算方式如下：

> (教师和学生对课程都满意且已存在当前教务系统中的用户) / (在学校里的人数)

**【解题思路】**

**1. 多表联结**

统计满意度的前提是需要用户在学校里，需要用到 “用户表” 里的 “是否在系统” 来判断。满意度需要用到“满意度表”。所以涉及到两个表里的数据，就要用到《[猴子 从零学会 sql](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)》里讲过的多表联结。

**那么，使用哪种联结呢？**

统计值为满意度，所以使用 “满意度表” 为主表，进行左联结。

```sql
select * 
from 满意度表
left join 用户表;
```

**如何联结呢？**

两个表的关系：满意度表的 “学生编号” 、 “教师编号” 和用户表的 “编号” 联结。所以联结条件是：

> 满意度表. 教师编号 = 用户表. 编号 or 满意度表. 学生编号 = 用户表. 编号

基于上面的分析，多表联结查询 sql 语句如下：

```sql
select * 
from 满意度表
left join 用户表
on (满意度表.教师编号 = 用户表.编号 or 
    满意度表.学生编号 = 用户表.编号);
```

**2. 查询条件**

满意度等于：

> (教师和学生对课程都满意且已存在当前教务系统中的用户) / (在学校里的人数)

由这个公式可以知道，分子和分母计算的前提都是存在学校里的用户。所以，查询条件是：用户表中存在学校里的用户。

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV7-ZF36BJIVWGmt9R2%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_4.jpg?alt=media\&token=1220dde1-338e-40f3-b86d-eefc96e7520f)

```sql
where 用户表.是否在系统 = '是'
```

把条件子句加入前面的多表查询 sql 里，就是下面的 sql：

```sql
select * 
from 满意度表
left join 用户表
on (满意度表.教师编号 = 用户表.编号 or 满意度表.学生编号 = 用户表.编号)
where 用户表.是否在系统 = '是';
```

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV7-dpXCn2au_Q_t2KY%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_5.jpg?alt=media\&token=3455d4ab-ebbf-4979-bb7a-7626408fa97c)

**3. 统计**

满意度等于：

> (教师和学生对课程都满意且已存在当前教务系统中的用户) / (在学校里的人数)

我把这个公示简化为：满意度 = a/b

其实 a = 教师和学生对课程都满意且已存在当前教务系统中的用户

b = 在学校里的人数

我们只需要把 a 和 b 的值计算出来就可以啦。

1）计算 a

统计好 “是否满意” 列里有多少个值为“是”。

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV7-wFZZFLlcrFXMxu6%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_6.jpg?alt=media\&token=3ddfc62b-8e46-471c-a209-451dd9b9229f)

我们可以把 “是” 转化成 1，然后累计求和，对应的 sql 就是：

```sql
sum(if(是否满意='是',1,0)
```

2）计算 b

b = 在学校里的人数，直接用计数函数（count）就可以：count(是否满意)

3）计算出满意度

满意度 = a/b，也就是

```sql
sum(if(是否满意='是',1,0))/count(是否满意) as 满意度
```

把这个计算公示加入前面 sql 语句的查询结果里就是（下面的 select 子句）：

```sql
select sum(if(满意度表.是否满意='是',1,0))/count(满意度表.是否满意) as 满意度
from 满意度表
left join 用户表
on (满意度表.教师编号 = 用户表.编号 or 满意度表.学生编号 = 用户表.编号)
where 用户表.是否在系统 = '是';
```

最后查询结果是满意度 = 0.75。

**【本题考点】**

● 考察多表查询的应用

● 如何将业务需求转换为 sql 语句的能力

● 聚合函数的运用

**【举一反三】**

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV70-JBC1lkcRMCTVj2%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_7.jpg?alt=media\&token=cd1c77df-7bbd-4103-bcf2-064069d301e9)

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV70OJi28pGipzFoXn9%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_8.png?alt=media\&token=d101107b-934e-4110-8127-126f8fa6281e)

Trips 表记录了各个出租车的行程信息。每段行程有唯一键（Id） 。Status 行程类型 ‘completed’表行程正常结束, ‘cancelled\_by\_driver’ 表示行程因为司机原因取消，‘cancelled\_by\_client’表示行程因为乘客原因取消。

两个表的联结关系：Trips 表（Client\_Id 、 Driver\_Id） 和 Users 表中 Users\_Id 的联结。

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV70RuIGhcR0oZ2AkWv%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_9.png?alt=media\&token=ed62eef4-a03d-4b89-bd6e-a73cc3463a70)

写一段 SQL 语句查出非禁止用户的取消率。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV70hzEjTq8zCNdREL7%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_10.png?alt=media\&token=a8767441-db7a-4505-9d37-b00f9c2928af)

**参考答案：**

1.两表联结，找出非禁止的用户

```sql
select *
from trips
left join users_id
on (trips.Client_Id = users_id.Users_Id or trips.Driver_Id = users_id.Users_Id)
where users_id.banned = 'no';
```

2.按日期分组

因为要计算的是 “每天” 的取消率，所以要按日期分组，统计每一天的。

```sql
group by trips.request_at
```

3.计算取消率  

被司机或乘客取消的非禁止用户生成的订单数量 =

sum(if(status = 'completed',0,1))

非禁止用户生成的订单总数 = count(status)

最终 sql 如下：

```sql
select sum(if(trips.status = 'completed',0,1)) / count(trips.status)
from trips
left join users_id
on (trips.Client_Id = users_id.Users_Id or trips.Driver_Id = users_id.Users_Id)
where users_id.banned = 'no'
group by trips.request_at;
```

推荐：[如何从零学会 sql？](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MV7-CKA-OKH7uj6NkKn%2F-MV70neCcGfL-XyuyhfR%2F%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E7%94%A8%E6%88%B7%E6%BB%A1%E6%84%8F%E5%BA%A6_11.jpg?alt=media\&token=583a7088-22d7-45ec-92d8-7ccfdfe6f7ed)