我将子查询与分析结合使用,以识别和分组连续范围:
sql> WITH test_data AS (
2 SELECT 0 s, 2 dur FROM dual UNION ALL --# 鈻犫枲
3 SELECT 2 , 2 FROM dual UNION ALL --# 鈻犫枲
4 SELECT 10 , 1 FROM dual UNION ALL --# 鈻?
5 SELECT 13 , 4 FROM dual UNION ALL --# 鈻犫枲鈻犫枲
6 SELECT 15 , 4 FROM dual --# 鈻犫枲鈻犫枲
7 )
8 SELECT MIN(s) "begin", MAX(s + dur) "end"
9 FROM (SELECT s, dur, SUM(gap) over(ORDER BY s) my_group
10 FROM (SELECT s, dur,
11 CASE
12 WHEN lag(s + dur) over(ORDER BY s) >= s - 5 THEN
13 0
14 ELSE
15 1
16 END gap
17 FROM test_data
18 ORDER BY s))
19 GROUP BY my_group;
begin end
---------- ----------
0 4
10 19