sql重复时长问题,如何去重详细讲解

重复时长问题描述:
访问某个会话未结束时,又开始另一个会话,会话时长会出现覆盖住,交叉,相互独立的情况,我们想统计去重后的时长,以下方案可以实现

根据测试数据看出,有严重的时长重复,实际想统计的时长是60+30+110=200

实现代码如下

select
    id
	--4. 将每个时间段去重后的时长相加,得到总时长
  , sum(stage_duration)
from
    (
        select
            id
		  --3. 每个组取最大的end_time-最小的start_time即为这个连续时间段的去重时长
          , max(end_time) - min(start_time) stage_duration
        from
            (
                select
                    id
                  , start_time
                  , end_time
				  --2. 将打好标记的每个连续时间段分组,利用sum() over()开窗区分开每个连续时间段,
                  , sum(stage_flag) over(partition by id order by
                                         start_time) stage_flag
                from
                    (
                        select
                            id
                          , start_time
                          , end_time
						  --1. 将时间断开的打标记,即当前条的开始时间大于上一条的结束时间,认定为时长不连续,出现空隙,打标记
                          , if ( start_time > max(end_time) over( partition by id order by
                                                                 start_time,end_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) ,1,0 ) stage_flag
                        from
                            ( table ) t1
                    )
                    it1
            )
            tb1
        group by
            id
          , stage_flag
    )
    ot1
group by
    id
;

整体思路:

1. 将时间断开的打标记,即当前条的开始时间大于上一条的结束时间,认定为时长不连续,出现空隙,打标记
2. 将打好标记的每个连续时间段分组,利用sum() over()开窗区分开每个连续时间段,
3. 每个组取最大的end_time-最小的start_time即为这个连续时间段的去重时长
4. 将每个时间段去重后的时长相加,得到总时长

详细过程图示