做外贸网站需要多少钱,公关就是陪人睡觉吗,海口高端网站建设,ico 代码 wordpress目录 1 题目2 建表语句3 题解 1 题目 有一份用户访问记录表#xff0c;记录用户id和访问时间#xff0c;如果用户访问时间间隔小于60s则认为时一次浏览#xff0c;请合并用户的浏览行为。
样例数据
------------------------
| user_id | access_time |
---------------… 目录 1 题目2 建表语句3 题解 1 题目 有一份用户访问记录表记录用户id和访问时间如果用户访问时间间隔小于60s则认为时一次浏览请合并用户的浏览行为。
样例数据
------------------------
| user_id | access_time |
------------------------
| 1 | 1736337600 |
| 1 | 1736337660 |
| 2 | 1736337670 |
| 1 | 1736337710 |
| 3 | 1736337715 |
| 2 | 1736337750 |
| 1 | 1736337760 |
| 3 | 1736337820 |
| 2 | 1736337850 |
| 1 | 1736337910 |
------------------------2 建表语句 --建表语句
CREATE TABLE user_access_log (user_id INT,access_time BIGINT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t;
--插入数据
insert into user_access_log (user_id,access_time)
values
(1,1736337600),
(1,1736337660),
(2,1736337670),
(1,1736337710),
(3,1736337715),
(2,1736337750),
(1,1736337760),
(3,1736337820),
(2,1736337850),
(1,1736337910);3 题解 1分用户计算出每次点击时间差
select user_id,access_time,last_access_time,access_time - last_access_time as time_diff
from (select user_id,access_time,lag(access_time) over (partition by user_id order by access_time) as last_access_timefrom user_access_log) t执行结果
-------------------------------------------------------
| user_id | access_time | last_access_time | time_diff |
-------------------------------------------------------
| 1 | 1736337600 | NULL | NULL |
| 1 | 1736337660 | 1736337600 | 60 |
| 1 | 1736337710 | 1736337660 | 50 |
| 1 | 1736337760 | 1736337710 | 50 |
| 1 | 1736337910 | 1736337760 | 150 |
| 2 | 1736337670 | NULL | NULL |
| 2 | 1736337750 | 1736337670 | 80 |
| 2 | 1736337850 | 1736337750 | 100 |
| 3 | 1736337715 | NULL | NULL |
| 3 | 1736337820 | 1736337715 | 105 |
-------------------------------------------------------2确认是否是新的访问
select user_id,access_time,last_access_time,if(access_time - last_access_time 60, 1, 0) as is_new_group
from (select user_id,access_time,lag(access_time) over (partition by user_id order by access_time) as last_access_timefrom user_access_log) t执行结果
----------------------------------------------------------
| user_id | access_time | last_access_time | is_new_group |
----------------------------------------------------------
| 1 | 1736337600 | NULL | 0 |
| 1 | 1736337660 | 1736337600 | 1 |
| 1 | 1736337710 | 1736337660 | 0 |
| 1 | 1736337760 | 1736337710 | 0 |
| 1 | 1736337910 | 1736337760 | 1 |
| 2 | 1736337670 | NULL | 0 |
| 2 | 1736337750 | 1736337670 | 1 |
| 2 | 1736337850 | 1736337750 | 1 |
| 3 | 1736337715 | NULL | 0 |
| 3 | 1736337820 | 1736337715 | 1 |
----------------------------------------------------------3得出结果
使用sum()over(partition by …… order by ……)累加计算给出组ID。聚合函数开窗使用order by 计算结果是从分组开始计算到当前行的结果。
这里的技巧需要新建组的时候就给标签赋值1否则0然后累加计算结果在新建组的时候值就会变化根据聚合值分组得到合并结果。
with t_group as(select user_id,access_time,last_access_time,if(access_time - last_access_time 60, 1, 0) as is_new_groupfrom (select user_id,access_time,lag(access_time) over (partition by user_id order by access_time) as last_access_timefrom user_access_log) t)
select user_id,access_time,last_access_time,is_new_group,sum(is_new_group) over (partition by user_id order by access_time asc) as group_id
from t_group执行结果
---------------------------------------------------------------------
| user_id | access_time | last_access_time | is_new_group | group_id |
---------------------------------------------------------------------
| 1 | 1736337600 | NULL | 0 | 0 |
| 1 | 1736337660 | 1736337600 | 1 | 1 |
| 1 | 1736337710 | 1736337660 | 0 | 1 |
| 1 | 1736337760 | 1736337710 | 0 | 1 |
| 1 | 1736337910 | 1736337760 | 1 | 2 |
| 2 | 1736337670 | NULL | 0 | 0 |
| 2 | 1736337750 | 1736337670 | 1 | 1 |
| 2 | 1736337850 | 1736337750 | 1 | 2 |
| 3 | 1736337715 | NULL | 0 | 0 |
| 3 | 1736337820 | 1736337715 | 1 | 1 |
---------------------------------------------------------------------
