SQL面试题之根据时间间隔分组

JJJJJJJJia

已于 2024-09-19 13:04:36 修改

阅读量146

点赞数 9

分类专栏： SQL 文章标签： sql 数据库

于 2024-09-11 10:01:00 首次发布

本文链接：https://blog.csdn.net/JJJJJJJJia/article/details/142129025

版权

SQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

如下为电商公司用户访问时间数据
id ts(秒)
1001 17523641234
1001 17523641256
1002 17523641278
1001 17523641334
1002 17523641434
1001 17523641534
1001 17523641544
1002 17523641634
1001 17523641638
1001 17523641654
把相同用户连续的访问记录如果时间间隔小于 60 秒的记录，分为同一个组
结果提示如下：
id ts(秒) groups
1001 17523641234 1
1001 17523641256 1
1001 17523641334 2
1001 17523641534 3
1001 17523641544 3
1001 17523641638 4
1001 17523641654 4
1002 17523641278 1
1002 17523641434 2
1002 17523641634 3

解题思路：

第一步：利用lag函数求出用户当前访问记录与上一访问记录的差值。如果差值<=60,记为0，否则记为1.这个flag标记列用作下面的断点。

第二步：对每个用户按照访问记录排序并对flag列进行累加求和。由于60秒之内的都是0，所以累加后还是0.一旦有间隔超过60秒的就会加1.

第三步：由于结果中的groups是从1开始的，所以对累加的结果再加1即可。

代码如下：

select
	a.id
	,a.ts
	,sum(flag)over(partition by a.id order by a.ts)+1 as groups  --对flag列累加求和即可得出分组情况，+1是为了使得分组最小值从1开始
from(--按照用户分组，将当前访问记录-上一个访问记录，如果<=60秒，记为0；否则记为1。flag列可作为分组的断点
	select 
		id
		,ts
		,case when ts-lag(id)over(partition by id order ts)<=60 then 0 else 1 end as flag
	from table
	) a

结果演示：（由于题主无法连接数据库，故采用Excel演示）