SQL—相邻间隔问题

SQL—相邻间隔问题

<题目>

给定一张用户浏览时间表user_pv_time，表中包含两个字段，分别是用户【user_id】和用户访问时间【user_time】

<问题>

求每个用户相邻两次浏览时间之差小于3min的次数

<解题思路>

思路一：

既然是求每个用户XX，最后一定要按照用户id进行分组；又要求是相邻两次浏览时间，所以要对每个用户的浏览时间排序号，利用序号的差值为1即可判断是相邻；

SELECT a.user_id, COUNT(*) AS pv_count
FROM (SELECT user_id, user_time, ROW_NUMBER() OVER (partition by user_idorder by user_time) AS rnFROM user_pv_time) AS a
LEFT JOIN (SELECT user_id, user_time, ROW_NUMBER() OVER (partition by user_idorder by user_time) AS rnFROM user_pv_time) AS b
ON a.user_id = b.user_id 
WHERE   AS signed) -  AS signed) = 1 
AND TIMESTAMPDIFF(MINUTE, a.user_time, b.user_time) < 3
GROUP BY user_id