SQL每日一题(20210319)

题目

有如下一张表T0319

希望得到如下结果：

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

测试数据

CREATE TABLE T0319 (
user_id INT,
times DATETIME
)

INSERT INTO T0319 VALUES (1,'2020-12-7 21:13:07');
INSERT INTO T0319 VALUES (1,'2020-12-7 21:15:26');
INSERT INTO T0319 VALUES (1,'2020-12-7 21:17:44');
INSERT INTO T0319 VALUES (2,'2020-12-13 21:14:06');
INSERT INTO T0319 VALUES (2,'2020-12-13 21:18:19');
INSERT INTO T0319 VALUES (2,'2020-12-13 21:20:36');
INSERT INTO T0319 VALUES (3,'2020-12-21 21:16:51');
INSERT INTO T0319 VALUES (4,'2020-12-16 22:22:08');
INSERT INTO T0319 VALUES (4,'2020-12-2 21:17:22');
INSERT INTO T0319 VALUES (4,'2020-12-30 15:15:44');
INSERT INTO T0319 VALUES (4,'2020-12-30 15:17:57');

参考答案

官方答案:

WITH CTE AS (SELECT ROW_NUMBER() over (PARTITION BY user_id ORDER BY times) NUM
                  , USER_ID
                  , TIMES
             FROM T0319)
SELECT t.USER_ID
     , SUM(CASE WHEN T.diff < 4 THEN 1 ELSE 0 END) AS cnt
FROM (SELECT c1.USER_ID
           , c1.times
           , ABS(
            DATEDIFF(MINUTE, ISNULL(c1.times, '1970-01-01 00:00:00'), ISNULL(c2.times, '1970-01-01 00:00:00'))) AS diff
      FROM CTE c1
               LEFT JOIN CTE c2 ON c1.NUM + 1 = c2.NUM AND c1.USER_ID = c2.USER_ID) t
GROUP BY t.USER_ID

考点: ROW_NUMBER() over(PARTITION BY）、DATEDIFF()、SUM(CASE WHEN)

参考:SQL每日一题(20210319)