1934.确认率（avg的妙用）
表: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
解答
select user_id, round(ifnull(avg(action = 'confirmed'), 0), 2) confirmation_rate from Signups left join Confirmations using(user_id) group by user_id精髓：
avg(action = 'confirmed')
等同于
SUM(IF(action = 'confirmed', 1, 0)) / COUNT(action)
180.连续出现的数字
表：Logs
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ 在 SQL 中，id 是该表的主键。 id 是一个自增列。
找出所有至少连续出现三次的数字。
返回的结果表中的数据可以按任意顺序排列。
结果格式如下面的例子所示：
示例 1:
输入：Logs 表： +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+输出：Result 表： +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+解释：1 是唯一连续出现至少三次的数字。
解答：
差值法
SELECT DISTINCT num AS ConsecutiveNums FROM ( SELECT num, ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS rn, ROW_NUMBER() OVER (ORDER BY id) AS global_rn FROM Logs ) t GROUP BY num, (global_rn - rn) -- 连续段标识 HAVING COUNT(*) >= 3; -- 想查 6 次就改成 6差值 "妙" 在哪
- 在同一数字连续出现的一段里，每来一行
rn和global_rn都同步 +1，所以global_rn - rn恒定不变； - 一旦数字发生切换，
rn被重置回 1，而global_rn继续往前走，差值就跳到新值。
所以这个差值天然就是 "第几段连续区间的段号"—— 同一段的行差值相同，不同段的差值必然不同。举例：表格
| id | num | global_rn | rn | global_rn − rn |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 0 |
| 2 | 1 | 2 | 2 | 0 |
| 3 | 1 | 3 | 3 | 0 |
| 4 | 2 | 4 | 1 | 3 |
| 5 | 2 | 5 | 2 | 3 |
| 6 | 1 | 6 | 4 | 2 |
| 7 | 1 | 7 | 5 | 2 |
| 8 | 1 | 8 | 6 | 2 |
GROUP BY num, 差值后得到 3 个岛：数字 1 的 id 1–3（差 0）、数字 2 的 id 4–5（差 3）、数字 1 的 id 6–8（差 2）。同一数字分属两个岛（1 出现了两次，各 3 连）也互不干扰。
