获取表中连续区间

获取表中连续区间

场景 1
前端日志 记录用户访问功能情况
用户会点击 功能A 操作一段时间 然后进入 功能B 操作一段时间 再 进入A操作一段时间
日志记录如下

时间	用户	功能	事项
时间1	用户A	功能A	操作事项X 第一次访问
时间2	用户A	功能A	操作事项X 第一次访问
时间3	用户A	功能A	操作事项X 第一次访问
时间4	用户A	功能B	操作事项X
时间5	用户A	功能B	操作事项X
时间6	用户A	功能B	操作事项X
时间7	用户A	功能B	操作事项X
时间8	用户A	功能A	操作事项X 第二次访问
时间9	用户A	功能A	操作事项X 第二次访问
时间10	用户A	功能A	操作事项X 第二次访问
时间11	用户A	功能A	操作事项X 第二次访问

---

为了获取在功能A上用了多少时间 （第一次访问 + 第二次访问）
或者 获取访问了多少次 (2次)
需要将日志中访问功能A 两次单独识别出来 单一 partition by 用户 功能 无法区分两次访问
网上搜的 且有效的方法
步骤一

SELECT 时间,
       用户,
       功能,
	   -- 在同一用户下 按照时间排序
       ROW_NUMBER() OVER(partition by 用户 ORDER BY 时间) AS rn,
	   -- 在同一用户，同一功能下 按照时间排序  相同功能 会按照时间顺序逐个 +1 前提是同一个用户下
       ROW_NUMBER() OVER(PARTITION BY 用户,功能 ORDER BY 时间) AS SN,
	   
	   -- 两者向减
	   -- 都是按照时间排序 但是略有不同 rn纯按照时间排序   sn 是功能内按照时间排序 
	   -- 两者相减  就会使  同一功能下 的差值是一样的(都是按照时间排序的) 
	   -- 如果功能A中有其他功能混入(中间又访问了功能B) RN正常随时间 +1 但是 SN 中partition by 用户，功能
	   -- 遇到功能B 的时候又开始按照功能B的序号往下排 
	   -- 当再遇到 功能A的时候 因为中间插入了几行功能B  RN 就比上次遇到 功能A的时候 大了几个数  在 - sn的时候就与之前
	   -- 上次遇到功能A的时候差值不一样了 根据同一功能 的差值不同 就可以区分不同访问次数
      ROW_NUMBER() OVER(partition by 用户 ORDER BY 时间)  - ROW_NUMBER() OVER(PARTITION BY 用户，功能  ORDER BY 时间) AS QJ
FROM 日志表 
ORDER BY 时间;

---

时间	用户	功能	事项	RN	SN	RN -SN
时间1	用户A	功能A	操作事项X 第一次访问A	1	1	0
时间2	用户A	功能A	操作事项X 第一次访问A	2	2	0
时间3	用户A	功能A	操作事项X 第一次访问A	3	3	0
时间4	用户A	功能B	操作事项X 第一次访问B	4	1	3
时间5	用户A	功能B	操作事项X 第一次访问B	5	2	3
时间6	用户A	功能B	操作事项X 第一次访问B	6	3	3
时间7	用户A	功能B	操作事项X 第一次访问B	7	4	3
时间8	用户A	功能A	操作事项X 第二次访问A	8	4(是A3+1)	4
时间9	用户A	功能A	操作事项X 第二次访问A	9	5	4
时间10	用户A	功能A	操作事项X 第二次访问A	10	6	4
时间11	用户A	功能A	操作事项X 第二次访问A	11	7	4

---

最终脚本

select min(时间) as beg_date, -- 该区间 最小时间 及 开始时间
       max(时间) as end_date, -- 该区间 最大时间 及 结束时间
       用户,              
       功能,
       count(*) as "连续次数"  -- 该区间 日志表记录行数
from(SELECT 时间,
            用户,
            功能,
            ROW_NUMBER() OVER(partition by 用户 ORDER BY 时间) AS rn,
            ROW_NUMBER() OVER(PARTITION BY 用户,功能 ORDER BY 时间) AS SN,
            ROW_NUMBER() OVER(partition by 用户 ORDER BY 时间)  - ROW_NUMBER() OVER(PARTITION BY 用户，功能  ORDER BY 时间) AS QJ
       FROM 日志表 
      ORDER BY 时间)  
group by 
用户,功能,QJ;  -- 获取同一用户下各个区间功能的数据  必须分组中 把 功能,QJ都分组 才能确保 是连续区间