Concatenate sets of intervals
Thursday, 5 May, 2011
Leave a comment
CLEAR;
with t as (
select 11 AS ID, 1 AS val1, 'B' AS val2 from dual union all
select 12,1,'A' from dual union ALL
select 13,1,'A' from dual union all
select 14,1,'B' from dual union ALL
select 15,1,'A' from dual UNION ALL
select 16,1,'B' from dual union ALL
select 17,1,'B' from dual union ALL
select 18,1,'A' from dual union ALL
select 19,2,'B' from dual union all
select 20,2,'A' from dual union ALL
select 21,2,'B' from dual union ALL
select 22,2,'A' from dual union ALL
select 23,2,'B' from dual union ALL
select 24,2,'A' from dual union ALL
select 25,1,'B' from dual union ALL
select 26,1,'A' from dual
),
tt AS(select t.*,
case when lag (val2) over (partition by val1 order by id) || val2 in ('A', 'BA') then 1 end as start_of_group,
case when lead(val2) over (partition by val1 order by id) || val2 in ('B', 'AB') then 1 end as end_of_group
from t),
ttt AS (SELECT tt.*, COUNT(start_of_group) over(PARTITION BY val1 ORDER BY id) AS group_no FROM tt),
tttt AS (SELECT row_number() over(ORDER BY val1, group_no) AS rn,
val1,
t1.ID AS id1,
t2.ID AS id2,
t1.val2 AS VAL2A,
t2.val2 AS VAL2B
FROM (SELECT * FROM ttt WHERE start_of_group IS NOT NULL) t1
FULL JOIN (SELECT * FROM ttt WHERE end_of_group IS NOT NULL) t2
USING (val1, group_no)
ORDER BY rn)
SELECT * FROM tttt;

Recent Comments