郑州影视公司招聘_海口网约车驾驶员资格证怎么办理_seo站长工具_公司员工培训内容有哪些

目录

题目

准备数据

分析数据

总结

---

题目

编写 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 Signupsinsert 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 Confirmationsinsert 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函数确定窗口范围.

selectuser_id,count(*) over(partition by user_idorder by time_stamprange between interval 24 hour preceding and current row) cnt
from confirmations;

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

select distinct user_id
from (selectuser_id,count(*) over(partition by user_idorder by time_stamprange between interval 24 hour preceding and current row) cntfrom confirmations) tmp
where cnt>1;

总结

• ROWS是根据分区数据排序之后，每一行的 row_number 确定每行关联的 window frame 范围。
• RANGE是根据分区数据排序之后，每一行的排序列的值确定每行关联的 window frame 范围。