当前位置：首页 > article >正文

MySQL高阶1939-主动请求确认消息的用户

article 2024/11/16 3:36:23

题目

准备数据

分析数据

总结

题目

编写 SQL 查询以查找在 24 小时窗口内两次请求确认消息的用户的 ID。两个正好相隔 24 小时的消息被认为是在窗口内。该操作不会影响答案，只会影响请求时间。

以任意顺序返回结果表。

准备数据

Create table If Not Exists Signups (user_id int, time_stamp datetime)
Create table If Not Exists Confirmations (user_id int, time_stamp datetime, action ENUM('confirmed','timeout'))
    Truncate table Signups
    insert into Signups (user_id, time_stamp) values ('3', '2020-03-21 10:16:13')
    insert into Signups (user_id, time_stamp) values ('7', '2020-01-04 13:57:59')
    insert into Signups (user_id, time_stamp) values ('2', '2020-07-29 23:09:44')
    insert into Signups (user_id, time_stamp) values ('6', '2020-12-09 10:39:37')
    Truncate table Confirmations
    insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-01-06 03:30:46', 'timeout')
    insert into Confirmations (user_id, time_stamp, action) values ('3', '2021-01-06 03:37:45', 'timeout')
    insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-12 11:57:29', 'confirmed')
    insert into Confirmations (user_id, time_stamp, action) values ('7', '2021-06-13 11:57:30', 'confirmed')
    insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-01-22 00:00:00', 'confirmed')
    insert into Confirmations (user_id, time_stamp, action) values ('2', '2021-01-23 00:00:00', 'timeout')
    insert into Confirmations (user_id, time_stamp, action) values ('6', '2021-10-23 14:14:14', 'confirmed')
    insert into Confirmations (user_id, time_stamp, action) values ('6', '2021-10-24 14:14:13', 'timeout')

confirmations表

signups表

分析数据

第一步:利用开窗函数range函数确定窗口范围.

select
    user_id,
    count(*) over(
        partition by user_id
        order by time_stamp
        range between interval 24 hour preceding and current row
        ) cnt
from confirmations;

第二步:筛选出24小时以内的数据

select distinct user_id
from (
         select
             user_id,
             count(*) over(
                 partition by user_id
                 order by time_stamp
                 range between interval 24 hour preceding and current row
                 ) cnt
         from confirmations
     ) tmp
where cnt>1;