with t as(
/* 1й период активности */
select to_date('2010.01','yyyy.mm') d1, to_date('2010.03','yyyy.mm') d2, 'client1' client from dual
union all
select to_date('2010.02','yyyy.mm') , to_date('2010.04','yyyy.mm'),'client1' from dual
union all
select to_date('2010.03','yyyy.mm'), to_date('2010.05','yyyy.mm'),'client1' from dual
union all
/* 2й период активности */
select to_date('2011.01','yyyy.mm') d1, to_date('2011.03','yyyy.mm') d2, 'client1' client from dual
union all
select to_date('2011.02','yyyy.mm') , to_date('2011.04','yyyy.mm'),'client1' from dual
)
select client, dt dt_1, fv dt_2
from
(select client, dt, sm,
first_value(decode(sm, 0, dt) ignore nulls) over (partition by client order by dt rows between 1 following and unbounded following) fv,
lag(sm, 1, 0) over (partition by client order by dt) l
from
(select client, dt,
sum(decode(vtype, 'd1', 1, -1)) over (partition by client order by dt) sm
from
(select * from t)
unpivot (dt for vtype in (d1 as 'd1', d2 as 'd2'))
)
)
where l = 0;
CLIENT DT_1 DT_2
------- ---------- ----------
client1 01.01.2010 01.05.2010
client1 01.01.2011 01.04.2011
План выполнения
----------------------------------------------------------
Plan hash value: 400566494
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 400 | 15 (60)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7AAB_38CE4C63 | | | | |
| 3 | UNION-ALL | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 9 | VIEW | | 10 | 400 | 5 (20)| 00:00:01 |
| 10 | WINDOW BUFFER | | 10 | 310 | 5 (20)| 00:00:01 |
| 11 | VIEW | | 10 | 310 | 5 (20)| 00:00:01 |
| 12 | WINDOW SORT | | 10 | 220 | 5 (20)| 00:00:01 |
|* 13 | VIEW | | 10 | 220 | 4 (0)| 00:00:01 |
| 14 | UNPIVOT | | | | | |
| 15 | VIEW | | 5 | 135 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7AAB_38CE4C63 | 5 | 135 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("L"=0)
13 - filter("unpivot_view_014"."DT" IS NOT NULL)
Статистика
----------------------------------------------------------
59 recursive calls
8 db block gets
32 consistent gets
1 physical reads
852 redo size
512 bytes sent via SQL*Net to client
363 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
|