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继续往前走差值就跳到新值。所以这个差值天然就是 第几段连续区间的段号—— 同一段的行差值相同不同段的差值必然不同。举例表格idnumglobal_rnrnglobal_rn − rn1111021220313304241352523616427175281862GROUP BY num, 差值后得到 3 个岛数字 1 的 id 1–3差 0、数字 2 的 id 4–5差 3、数字 1 的 id 6–8差 2。同一数字分属两个岛1 出现了两次各 3 连也互不干扰。