感谢出题者，如有冒犯，请与我联系，希望和大家一起学习交流。(目前使用oracle数据库环境编写sql)
题目
表user_time中的字段时user_id，time（用户访问时间）
求每个用户相邻两次浏览时间之差小于三分钟的次数
预计结果如下：
该如何写查询？
读者可以试着自己思考写下，再往下翻... 如有不同解题方式，欢迎大家一起交流。
运用分析函数或者lag函数
sys@ESWIFT> WITH T AS 2 (SELECT '1' USER_ID, 3 TO_DATE('2020-05-26 21:13:07', 'yyyy-MM-dd hh24:mi:ss') TIMES 4 FROM DUAL 5 UNION ALL 6 SELECT '1' USER_ID, 7 TO_DATE('2020-05-26 21:15:26', 'yyyy-MM-dd hh24:mi:ss') TIMES 8 FROM DUAL 9 UNION ALL 10 SELECT '1' USER_ID, 11 TO_DATE('2020-05-26 21:17:44', 'yyyy-MM-dd hh24:mi:ss') TIMES 12 FROM DUAL 13 UNION ALL 14 SELECT '2' USER_ID, 15 TO_DATE('2020-05-12 21:14:06', 'yyyy-MM-dd hh24:mi:ss') TIMES 16 FROM DUAL 17 UNION ALL 18 SELECT '2' USER_ID, 19 TO_DATE('2020-05-12 21:18:19', 'yyyy-MM-dd hh24:mi:ss') TIMES 20 FROM DUAL 21 UNION ALL 22 SELECT '2' USER_ID, 23 TO_DATE('2020-05-12 21:20:36', 'yyyy-MM-dd hh24:mi:ss') TIMES 24 FROM DUAL 25 UNION ALL 26 SELECT '3' USER_ID, 27 TO_DATE('2020-05-21 21:16:51', 'yyyy-MM-dd hh24:mi:ss') TIME 28 FROM DUAL 29 UNION ALL 30 SELECT '4' USER_ID, 31 TO_DATE('2020-05-16 22:22:08', 'yyyy-MM-dd hh24:mi:ss') TIME 32 FROM DUAL 33 UNION ALL 34 SELECT '4' USER_ID, 35 TO_DATE('2020-05-02 21:17:22', 'yyyy-MM-dd hh24:mi:ss') TIME 36 FROM DUAL 37 UNION ALL 38 SELECT '4' USER_ID, 39 TO_DATE('2020-05-30 15:15:44', 'yyyy-MM-dd hh24:mi:ss') TIME 40 FROM DUAL 41 UNION ALL 42 SELECT '4' USER_ID, 43 TO_DATE('2020-05-30 15:17:57', 'yyyy-MM-dd hh24:mi:ss') TIME 44 FROM DUAL) 45 SELECT USER_ID, SUM(CN) CNT 46 FROM (SELECT T.*, 47 CASE 48 WHEN (TIMES - MAX(TIMES) 49 OVER(PARTITION BY USER_ID ORDER BY TIMES 50 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) * 24 * 60 < 3 THEN 51 1 52 ELSE 53 0 54 END CN 55 FROM T) 56 GROUP BY USER_ID 57 / USER_ID CNT ---------- ---------- 1 2 2 1 3 0 4 1
