Oracle小练2

数据准备：

CREATE TABLE T_2 (
user_id INT,
times VARCHAR(100)
);

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

求每个用户相邻两次浏览时间之差小于3分钟的次数
使用Oracle语法

SELECT t2.user_id, t2.times FROM T_2 t2;

WITH T AS
 (SELECT '1' USER_ID,
         TO_DATE('2020-12-7 21:13:07', 'yyyy-MM-dd hh24:mi:ss') TIMES
    FROM DUAL
  UNION ALL
  SELECT '1' USER_ID,
         TO_DATE('2020-12-7 21:15:26', 'yyyy-MM-dd hh24:mi:ss') TIMES
    FROM DUAL
  UNION ALL
  SELECT '1' USER_ID,
         TO_DATE('2020-12-7 21:17:44', 'yyyy-MM-dd hh24:mi:ss') TIMES
    FROM DUAL
  UNION ALL
  SELECT '2' USER_ID,
         TO_DATE('2020-12-13 21:14:06', 'yyyy-MM-dd hh24:mi:ss') TIMES
    FROM DUAL
  UNION ALL
  SELECT '2' USER_ID,
         TO_DATE('2020-12-13 21:18:19', 'yyyy-MM-dd hh24:mi:ss') TIMES
    FROM DUAL
  UNION ALL
  SELECT '2' USER_ID,
         TO_DATE('2020-12-13 21:20:36', 'yyyy-MM-dd hh24:mi:ss') TIMES
    FROM DUAL
  UNION ALL
  SELECT '3' USER_ID,
         TO_DATE('2020-12-21 21:16:51', 'yyyy-MM-dd hh24:mi:ss') TIME
    FROM DUAL
  UNION ALL
  SELECT '4' USER_ID,
         TO_DATE('2020-12-16 22:22:08', 'yyyy-MM-dd hh24:mi:ss') TIME
    FROM DUAL
  UNION ALL
  SELECT '4' USER_ID,
         TO_DATE('2020-12-2 21:17:22', 'yyyy-MM-dd hh24:mi:ss') TIME
    FROM DUAL
  UNION ALL
  SELECT '4' USER_ID,
         TO_DATE('2020-12-30 15:15:44', 'yyyy-MM-dd hh24:mi:ss') TIME
    FROM DUAL
  UNION ALL
  SELECT '4' USER_ID,
         TO_DATE('2020-12-30 15:17:57', 'yyyy-MM-dd hh24:mi:ss') TIME
    FROM DUAL)
SELECT USER_ID, SUM(CN) CNT
  FROM (SELECT T.*,
               CASE
                   WHEN (TIMES - MAX(TIMES)
                         OVER(PARTITION BY USER_ID ORDER BY TIMES
                              ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) * 24 * 60 < 3 THEN
                    1
                   ELSE
                    0
               END CN
          FROM T)
 GROUP BY USER_ID;

---

我们下期见！拜拜！