一、题目
有一份用户访问记录表，记录用户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 |+----------+--------------+
二、分析
1.首先对每个用户的访问时间排序，计算出时间差，考察的是开窗函数lag();
2.对时间差进行判断，确认是否需要新建一个组；
3.然后使用sum()的开窗函数，累加小计，赋予组ID；
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1.分用户计算出每次点击时间差；
selectuser_id,access_time,last_access_time,access_time-last_access_time as time_difffrom(selectuser_id,access_time,lag(access_time)over(partition by user_id order by access_time) as last_access_timefrom user_access_log) t
查询结果
2.确认是否需要新建一个组；
selectuser_id,access_time,last_access_time,if(access_time-last_access_time>=60,1,0) as is_new_groupfrom(selectuser_id,access_time,lag(access_time)over(partition by user_id order by access_time) as last_access_timefrom user_access_log) t
查询结果
3.使用sum()over(partition by ** order by **)累加计算，给出组ID。聚合函数开窗使用order by 计算结果是从分组开始计算到当前行的结果，这里的技巧：需要新建组的时候就给标签赋值1，否则0，然后累加计算结果在新建组的时候值就会变化
with t_group as(selectuser_id,access_time,last_access_time,if(access_time-last_access_time>=60,1,0) as is_new_groupfrom(selectuser_id,access_time,lag(access_time)over(partition by user_id order by access_time) as last_access_timefrom user_access_log) t)selectuser_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_idfrom t_group
查询结果
四、建表语句和数据插入
--建表语句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);
点点关注不迷路，天天学习有进步
分享↓收藏↓点赞↓再看↓
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
请登录后发表评论
登录
全部评论
