题目：

表user_time中的字段时user_id，time（用户访问时间）

image

求每个用户相邻两次浏览时间之差小于三分钟的次数 预计结果如下：

image

参考答案：

数据库版本：Server version: 8.0.20 MySQL Community Server - GPL

建表语句

create table dailytest_20200526
(
    user_id int,
    time    timestamp
);

数据准备

insert into dailytest_20200526 values(1,'2020-05-07 21:13:07');
insert into dailytest_20200526 values(1,'2020-05-07 21:15:26');
insert into dailytest_20200526 values(1,'2020-05-07 21:17:44');
insert into dailytest_20200526 values(2,'2020-05-13 21:14:06');
insert into dailytest_20200526 values(2,'2020-05-13 21:18:19');
insert into dailytest_20200526 values(2,'2020-05-13 21:20:36');
insert into dailytest_20200526 values(3,'2020-05-21 21:16:51');
insert into dailytest_20200526 values(4,'2020-05-16 22:22:08');
insert into dailytest_20200526 values(4,'2020-05-02 21:17:22');
insert into dailytest_20200526 values(4,'2020-05-30 15:15:44');
insert into dailytest_20200526 values(4,'2020-05-30 15:17:57');

查询逻辑

SELECT
       user_id,
       CNT
FROM (
      SELECT
           user_id,
            count(1) AS cnt
      FROM (
               SELECT user_id,
                      last_time,
                      time,
                      TimeStampDiff(SECOND, last_time, time) AS gap_time
               FROM (
                        SELECT user_id,
                               time,
                               LAG(time, 1) OVER (PARTITION BY user_id ORDER BY time) AS last_time
                        FROM dailytest_20200526) AA) BB
      WHERE BB.gap_time < 180
        GROUP BY user_id
      UNION ALL
      SELECT
             user_id,
             0 AS CNT
      FROM dailytest_20200526
        GROUP BY user_id
        HAVING COUNT(1) = 1) CC
ORDER BY CC.user_id;

附：
题目来源：https://mp.weixin.qq.com/s/jcoEaul0SUk7TZMQN708hA