Mysql刷题笔记 12.22 求每个用户相邻两次浏览时间之差小于三分钟的次数 两种方法

-- 12.22 求每个用户相邻两次浏览时间之差小于三分钟的次数

T1221
+---------+---------------------+
| user_id | times               |
+---------+---------------------+
|       1 | 2020-12-07 21:13:07 |
|       1 | 2020-12-07 21:15:26 |
|       1 | 2020-12-07 21:17:44 |
|       2 | 2020-12-13 21:14:06 |
|       2 | 2020-12-13 21:18:19 |
|       2 | 2020-12-13 21:20:36 |
|       3 | 2020-12-21 21:16:51 |
|       4 | 2020-12-16 22:22:08 |
|       4 | 2020-12-02 21:17:22 |
|       4 | 2020-12-30 15:15:44 |
|       4 | 2020-12-30 15:17:57 |
+---------+---------------------+
结果：
+---------+------+
| user_id | cnt  |
+---------+------+
|       1 |    2 |
|       2 |    1 |
|       3 |    0 |
|       4 |    1 |
+---------+------+

方法一: 
1. 首先两张相同的表 按照相同的id 做左连接 找到符合条件的相邻两次时间小于三分钟的
2. 对每个用户进行分组 计算满足条件的次数 
3. 为了让user_id = 3的也显示出来 再做一个左连接 如果是null 则返回0

select t4.user_id,IFNULL(t3.c,0) as cnt
from T1222 t4 left join (
  select t1.user_id,count(t1.times)as c
	from t1222 t1 left join t1222 t2 
	on t1.user_id = t2.user_id
	where t1.times < t2.times and DATE_ADD(t1.times,INTERVAL 3 MINUTE) > t2.times
  group by t1.user_id
)t3
on t4.user_id = t3.user_id
group by t4.user_id;
------------------------------------------------------
select t1.user_id,sum(case when DATE_ADD(t1.times,INTERVAL 3 MINUTE) > t2.times 
					  and t1.times < t2.times
                      then 1 else 0 end) as cnt
from t1222 t1 left join t1222 t2 
on t1.user_id = t2.user_id
group by t1.user_id

方法二:
1. 先创建一个视图 运用窗口函数 按照user分组 按照时间排序 产生排序数字rn
2. 对这两个视图做左连接 要求比较的时间是连续的 避免了方法1中全部连接
3. 计算两个相邻时间的差值 为cn
4. 统计差值小于3的个数

create view tt as
(select row_number() over(partition by user_id order by times) rn,user_id,times
from T1222 group by user_id,times)

-- mysql 的TIMESTAMPDIFF（MINUTE,starttime,endtime）函数获得时间差，
-- 得到的可以是DAY/天，HOUR/小时，MINUTE/分钟，SECOND/秒。
-- 其中starttime为时间小的那个时间，endtime为时间大的时间。

select c.user_id,sum(case when c.cn < 3 then 1 else 0 end) cnt
from (
	select a.user_id,
  ABS(TIMESTAMPDIFF(minute,IFNULL(a.times,'1970-01-01 00:00:00'),IFNULL(b.times,'1970-01-01 00:00:00'))) cn
  from tt as a left join tt as b 
  on a.rn = b.rn+1 and a.user_id = b.user_id
)c
group by c.user_id;