将不同时间点的登录状态记录转化为不同时间段的相同登录状态SQL求解

题目

有不同时间点的登录状态记录表state_log如下

请使用sql将其转化为如下表的不同时间段的相同登录状态记录

思路分析：

此类问题需要用到lag或lead函数取上下行对应的数据，然后对前后结果做比较打标签（0或1），再对标签所在列做累计求和，最后开窗按照累计值分组取时间最大最小值与状态值

代码实现：

SELECT min(stime) stime_start,max(stime) stime_end,max(state) state
from(select stime,state,stage_flag,sum(stage_flag) over(rows between unbounded preceding and current row) as accumulatefrom(select stime,state,if(state = pre_state, 0, 1) as stage_flagfrom(select stime,state,lag(state,1,1) over() pre_statefrom state_log) t1) t2) t3
group by accumulate;

代码结果分析

step1：

t1表核心点在lag(state,1,1) over() pre_state ，含义是取state列的结果，向上取1行，取不到时候设定默认值为1。
注：
lag(col,n,DEFAULT)用于统计窗口内往上第n行值
第一个参数为列名
第二个参数为往上第n行(可选，默认为1)
第三个参数为默认值(当往上第n行为NULL时候，取默认值，如不指定，则为NULL)
结果：

step2：
if(state = pre_state, 0, 1) as stage_flag
对比state与pre_state的结果，相同的赋值0
t2表结果：

step3
sum(stage_flag) over(rows between unbounded preceding and current row) as accumulate
开窗求累积到当前行的结果值
t3表结果：

step4
按累计值分组，求最终结果：