MaximaXXL, спасибо бальшущее, работает отлично, но с небольшой неточностью, берем упрощенный набор данных:
1. Исходный вариант (тяжелый):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
SQL> WITH t0 AS (SELECT 5 ID, TO_DATE('01082000', 'ddmmyyyy') beg_date, TO_DATE('01112000', 'ddmmyyyy') end_date FROM dual UNION ALL
2 SELECT 6 ID, TO_DATE('01072000', 'ddmmyyyy') beg_date, TO_DATE('01012002', 'ddmmyyyy') end_date FROM dual),
3 t1 AS (SELECT MIN(beg_date) date_from,
4 MAX(end_date) date_to
5 FROM t0),
6 t3 AS (SELECT date_from + LEVEL - 1 dat
7 FROM t1
8 CONNECT BY LEVEL < date_to - date_from + 2),
9 t4 AS (SELECT dat, LISTAGG(ID, ', ') WITHIN GROUP(ORDER BY id) id_list
10 FROM t3,
11 t0
12 WHERE t3.dat BETWEEN t0.beg_date AND t0.end_date
13 GROUP BY dat)
14 SELECT MIN(dat) date_from,
15 MAX(dat) date_to,
16 id_list
17 FROM (SELECT t5.*, SUM(sog) OVER(ORDER BY dat) grp
18 FROM (SELECT t4.*,
19 CASE WHEN id_list <> LAG(id_list) OVER(ORDER BY dat) THEN 1 END sog
20 FROM t4) t5)
21 GROUP BY grp, id_list
22 ORDER BY date_from,
23 date_to
24 ;
DATE_FROM DATE_TO ID_LIST
----------- ----------- --------------------------------------------------------------------------------
01.07.2000 31.07.2000 6
01.08.2000 01.11.2000 5, 6
02.11.2000 01.01.2002 6
Вариант 1:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SQL> with t0 (id,beg_date, end_date) as(select 5, to_date('01.08.2000','dd.mm.yyyy') , to_date('01.11.2000','dd.mm.yyyy') from dual union all
2 select 6, to_date('01.07.2000','dd.mm.yyyy') , to_date('01.01.2002','dd.mm.yyyy') from dual )
3 ,t1 as (select to_date('10.02.1999','dd.mm.yyyy') vStart_date, to_date('10.02.2001','dd.mm.yyyy') vEnd_date from dual)
4 , t3 as(select Date_Line Start_D, lead(Date_Line) over(ORDER by Date_Line) End_D
5 from (SELECT distinct decode(cBeg,1,Beg_date,End_Date) Date_Line
6 from t0, t1, (select level cBeg from dual connect by level = 2)
7 where end_date >= vStart_date
8 and beg_date <= vEnd_date
9 order by Date_line))
10 SELECT Start_D, End_D, listagg(id,', ') within group (order by id)
11 from t0, t3
12 where end_date >= Start_D
13 and beg_date <= End_D
14 and End_D is not null
15 group by Start_D, End_D
16 ;
START_D END_D LISTAGG(ID,',')WITHINGROUP(ORD
----------- ----------- --------------------------------------------------------------------------------
01.07.2000 01.08.2000 5, 6
01.08.2000 01.11.2000 5, 6
01.11.2000 01.01.2002 5, 6
Вариант 2 (корректированный первый):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SQL> with t0 (id,beg_date, end_date) as(select 5, to_date('01.08.2000','dd.mm.yyyy') , to_date('01.11.2000','dd.mm.yyyy') from dual union all
2 select 6, to_date('01.07.2000','dd.mm.yyyy') , to_date('01.01.2002','dd.mm.yyyy') from dual )
3 ,t1 as (select to_date('10.02.1999','dd.mm.yyyy') vStart_date, to_date('10.02.2001','dd.mm.yyyy') vEnd_date from dual)
4 , t3 as(select Date_Line Start_D, lead(Date_Line) over(ORDER by Date_Line) End_D
5 from (SELECT distinct decode(cBeg,1,greatest(Beg_date,vStart_date),least(End_Date,vEnd_date)) Date_Line
6 from t0, t1, (select level cBeg from dual connect by level = 2)
7 where end_date >= vStart_date
8 and beg_date <= vEnd_date
9 order by Date_line))
10 select min(Start_D), max(End_D), List_id
11 from (SELECT Start_D, End_D, listagg(id,', ') within group (order by id) List_id
12 from t0, t3
13 where end_date >= Start_D
14 and beg_date <= End_D
15 and End_D is not null
16 group by Start_D, End_D)
17 group by List_id
18 ;
MIN(START_D) MAX(END_D) LIST_ID
------------ ----------- --------------------------------------------------------------------------------
01.07.2000 10.02.2001 5, 6
Вариант 3:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SQL> with t0 (id,beg_date, end_date) as(select 5, to_date('01.08.2000','dd.mm.yyyy') , to_date('01.11.2000','dd.mm.yyyy') from dual union all
2 select 6, to_date('01.07.2000','dd.mm.yyyy') , to_date('01.01.2002','dd.mm.yyyy') from dual)
3 ,t1 as (select to_date('10.02.1999','dd.mm.yyyy') vStart_date, to_date('10.02.2099','dd.mm.yyyy') vEnd_date from dual)
4 , t3 as(select Date_Line Start_D, lead(Date_Line) over(ORDER by Date_Line) End_D
5 from (SELECT distinct decode(cBeg,1,greatest(Beg_date,vStart_date),least(End_Date,vEnd_date)) Date_Line
6 from t0, t1, (select level cBeg from dual connect by level = 2)
7 where end_date >= vStart_date
8 and beg_date <= vEnd_date
9 order by Date_line))
10 SELECT Start_D, End_D, listagg(id,', ') within group (order by id) List_id
11 from t0, t3
12 where end_date >= Start_D
13 and beg_date <= End_D
14 and End_D is not null
15 group by Start_D, End_D;
START_D END_D LIST_ID
----------- ----------- --------------------------------------------------------------------------------
01.07.2000 01.08.2000 5, 6
01.08.2000 01.11.2000 5, 6
01.11.2000 01.01.2002 5, 6
То есть ни один из трех последних не дает тот же результат, что исходный вариант.
Но работает конечно несравнимо быстрее, немного поправили:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with t0 (id,beg_date, end_date) as (select 5 id, to_date('01082000', 'ddmmyyyy') beg_date, to_date('01112000', 'ddmmyyyy') end_date from dual union all
select 6 id, to_date('01072000', 'ddmmyyyy') beg_date, to_date('01012002', 'ddmmyyyy') end_date from dual)
,t1 as (select to_date('10.02.1999','dd.mm.yyyy') vStart_date, to_date('10.02.2099','dd.mm.yyyy') vEnd_date from dual)
,t3 as (select Date_Line Start_D, lead(Date_Line) over(ORDER by Date_Line) End_D
from (select distinct decode(cBeg,1, greatest(Beg_date,vStart_date), least(End_Date,vEnd_date)) Date_Line
from t0, t1, (select level cBeg from dual connect by level = 2)))
select Start_D, End_D, listagg(id,', ') within group (order by id) List_id
from t0, t3
==>where beg_date <= Start_D
==>and end_date >= End_D
and End_D is not null
group by Start_D, End_D
Немного лучше, но до исходного результата пока тоже не дотягивает, надо как-то хитрее формировать пограничные даты что ли.
stax..интересная фича, можно спрашивать на собеседованиях
select 2 ++ (-2) from dual;
Я б даже на такой вопрос ответил бы неверно (но это наверно баян, да еще может и из доки )) |
