【MySQL】avg 函数和 if 函数联合巧用

力扣题

1、题目地址

1934. 确认率

2、模拟表

表: Signups

Column Name	Type
user_id	int
time_stamp	datetime

• User_id是该表的主键。
• 每一行都包含ID为user_id的用户的注册时间信息。

表: Confirmations

Column Name	Type
user_id	int
time_stamp	datetime
action	ENUM

• (user_id, time_stamp)是该表的主键。
• user_id是一个引用到注册表的外键。
• action是类型为(‘confirmed’， ‘timeout’)的ENUM
• 该表的每一行都表示ID为user_id的用户在time_stamp请求了一条确认消息，
• 该确认消息要么被确认(‘confirmed’)，要么被过期(‘timeout’)。

3、要求

• 用户的 确认率 是 ‘confirmed’ 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。
• 确认率四舍五入到 小数点后两位 。
• 编写一个SQL查询来查找每个用户的 确认率 。
• 以 任意顺序 返回结果表。

4、示例

输入：

Signups 表：

user_id	time_stamp
3	2020-03-21 10:16:13
7	2020-01-04 13:57:59
2	2020-07-29 23:09:44
6	2020-12-09 10:39:37

Confirmations 表：

user_id	time_stamp	action
3	2021-01-06 03:30:46	timeout
3	2021-07-14 14:00:00	timeout
7	2021-06-12 11:57:29	confirmed
7	2021-06-13 12:58:28	confirmed
7	2021-06-14 13:59:27	confirmed
2	2021-01-22 00:00:00	confirmed
2	2021-02-28 23:59:59	timeout

输出：

user_id	confirmation_rate
6	0.00
3	0.00
7	1.00
2	0.50

解释:

用户 6 没有请求任何确认消息。确认率为 0。
用户 3 进行了 2 次请求，都超时了。确认率为 0。
用户 7 提出了 3 个请求，所有请求都得到了确认。确认率为 1。
用户 2 做了 2 个请求，其中一个被确认，另一个超时。确认率为 1 / 2 = 0.5。

5、代码编写

我的写法

SELECT a.user_id, ROUND(IFNULL(b.confirmation_rate, 0), 2) AS confirmation_rate
FROM Signups a
LEFT JOIN (
    SELECT user_id, SUM(action = 'confirmed')/COUNT(*) AS confirmation_rate
    FROM Confirmations
    GROUP BY user_id
) AS b ON a.user_id = b.user_id

| user_id | confirmation_rate |
| ------- | ----------------- |
| 3       | 0                 |
| 7       | 1                 |
| 2       | 0.5               |
| 6       | 0                 |

网友用 avg 函数和 if 函数组合

SELECT s.user_id, ROUND(AVG(IF(c.action = 'confirmed', 1, 0)), 2) AS confirmation_rate 
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id

| user_id | confirmation_rate |
| ------- | ----------------- |
| 3       | 0                 |
| 7       | 1                 |
| 2       | 0.5               |
| 6       | 0                 |

解析

AVG(IF(c.action = 'confirmed', 1, 0))

这段代码相当于是 计算比率 = 请求被确认的数量 / 总请求数量，
可以理解成 AVG 函数括号里面 是计算 请求被确认的数量，而 AVG 这个函数本身 就是最后要除以 总请求数量

参考

Mysql - 使用 avg() 与 if() 函数计算正确率
SQL 学习8.1 - AVG() 函数计算均值和 if() 函数的使用