每日leetcode

1934. 确认率 - 力扣（LeetCode）

题目

表: 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')。

用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。

编写一个SQL查询来查找每个用户的 确认率 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例1:

输入：
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。

思路

1. 先以Signups表为左表左连接Confirmations表，连接键为user_id，以正确展示所有注册用户情况。
2. 将连接后的表以user_id做group by然后提取user_id以及计算confirmed数/请求数的比例（缺省补0，保留两位小数）。

代码实现

# Write your MySQL query statement below
select s.user_id, round(ifnull(count(if(c.action="confirmed", 1, null))/count(c.action), 0), 2) as confirmation_rate
from Signups as s left join Confirmations as c
on s.user_id = c.user_id
group by s.user_id

知识积累

• 带条件的统计：count(if(condition, 为真的处理，为假的处理))——因为外层是做count，只有为null才不会统计，如果是sum填0即可。

题解

• 这里如果不用count的话似乎会简便一些，因为count是统计条目数，如果不加if那么加上判断条件也没有用，但是如果用sum或者avg的话就可以直接加判断条件在里面了。
• SUM方法的复现：

• # Write your MySQL query statement below
  select s.user_id, round(ifnull(sum(if(c.action="confirmed", 1, 0))/count(c.action), 0), 2) as confirmation_rate
  from Signups as s left join Confirmations as c
  on s.user_id = c.user_id
  group by s.user_id

• 如果在avg中加入判断条件，缺省时会认为符合条件为1，不符合条件为0，所以avg相当于浓缩了sum/count的那个步骤。
• AVG方法的复现：

• # Write your MySQL query statement below
  select s.user_id, round(ifnull(avg(c.action="confirmed"), 0), 2) as confirmation_rate
  from Signups as s left join Confirmations as c
  on s.user_id = c.user_id
  group by s.user_id