<题目>
给定一张用户浏览时间表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_id
order by user_time) AS rn
FROM user_pv_time) AS a
LEFT JOIN (SELECT user_id, user_time,
ROW_NUMBER() OVER (partition by user_id
order by user_time) AS rn
FROM user_pv_time) AS b
ON a.user_id = b.user_id
WHERE CAST(b.rn AS signed) - CAST(a.rn AS signed) = 1
AND TIMESTAMPDIFF(MINUTE, a.user_time, b.user_time) < 3
GROUP BY user_id