SQL> set autot on explain stat
SQL> alter session set nls_date_format='dd.mm.yyyy';
Сеанс изменен.
SQL> with tb as(
2 select sysdate dt, 1 id, sysdate + 1 dat from dual union all
3 select sysdate + 1 dt, 1 id, sysdate + 2 dat from dual union all
4 select sysdate + 2 dt, 1 id, sysdate + 3 dat from dual union all
5 select sysdate + 3 dt, 2 id, sysdate + 4 dat from dual union all
6 select sysdate + 4 dt, 2 id, sysdate + 5 dat from dual union all
7 select sysdate + 5 dt, 1 id, sysdate + 6 dat from dual union all
8 select sysdate + 6 dt, 2 id, sysdate + 7 dat from dual union all
9 select sysdate + 7 dt, 1 id, sysdate + 8 dat from dual union all
10 select sysdate + 8 dt, 1 id, sysdate + 9 dat from dual union all
11 select sysdate + 9 dt, 1 id, sysdate + 10 dat from dual union all
12 select sysdate + 10 dt, 2 id, sysdate + 11 dat from dual)
13 select id, min(dt) d1, max(dat) d2
14 from
15 (select dt, id, dat,
16 row_number() over (order by dt) -
17 row_number() over (partition by id order by dt) rn
18 from tb
19 )
20 group by id, rn;
ID D1 D2
---------- ---------- ----------
1 15.03.2011 18.03.2011
2 21.03.2011 22.03.2011
2 25.03.2011 26.03.2011
2 18.03.2011 20.03.2011
1 20.03.2011 21.03.2011
1 22.03.2011 25.03.2011
6 строк выбрано.
План выполнения
----------------------------------------------------------
Plan hash value: 803537873
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 374 | 25 (12)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 374 | 25 (12)| 00:00:01 |
| 2 | VIEW | | 11 | 374 | 24 (9)| 00:00:01 |
| 3 | WINDOW SORT | | 11 | 231 | 24 (9)| 00:00:01 |
| 4 | WINDOW SORT | | 11 | 231 | 24 (9)| 00:00:01 |
| 5 | VIEW | | 11 | 231 | 22 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 13 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 15 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 16 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 17 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Статистика
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
689 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
------------------------------------------------------------------------------
SQL> with tb as(
2 select sysdate dt, 1 id, sysdate + 1 dat from dual union all
3 select sysdate + 1 dt, 1 id, sysdate + 2 dat from dual union all
4 select sysdate + 2 dt, 1 id, sysdate + 3 dat from dual union all
5 select sysdate + 3 dt, 2 id, sysdate + 4 dat from dual union all
6 select sysdate + 4 dt, 2 id, sysdate + 5 dat from dual union all
7 select sysdate + 5 dt, 1 id, sysdate + 6 dat from dual union all
8 select sysdate + 6 dt, 2 id, sysdate + 7 dat from dual union all
9 select sysdate + 7 dt, 1 id, sysdate + 8 dat from dual union all
10 select sysdate + 8 dt, 1 id, sysdate + 9 dat from dual union all
11 select sysdate + 9 dt, 1 id, sysdate + 10 dat from dual union all
12 select sysdate + 10 dt, 2 id, sysdate + 11 dat from dual)
13 select id, dt, d2
14 from
15 (select id, dt, dcd,
16 nvl(first_value(decode(dcd, 1, lg) ignore nulls) over (order by dt rows between 1 following and unbounded following),
17 dat
18 ) d2
19 from
20 (select dt, id, dat,
21 lag(dat) over (order by dt) lg,
22 decode(lag(id) over (order by dt), id, 0, 1) dcd
23 from tb
24 )
25 )
26 where dcd = 1;
ID DT D2
---------- ---------- ----------
1 15.03.2011 18.03.2011
2 18.03.2011 20.03.2011
1 20.03.2011 21.03.2011
2 21.03.2011 22.03.2011
1 22.03.2011 25.03.2011
2 25.03.2011 26.03.2011
6 строк выбрано.
План выполнения
----------------------------------------------------------
Plan hash value: 3539063114
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 264 | 23 (5)| 00:00:01 |
|* 1 | VIEW | | 11 | 264 | 23 (5)| 00:00:01 |
| 2 | WINDOW BUFFER | | 11 | 363 | 23 (5)| 00:00:01 |
| 3 | VIEW | | 11 | 363 | 23 (5)| 00:00:01 |
| 4 | WINDOW SORT | | 11 | 231 | 23 (5)| 00:00:01 |
| 5 | VIEW | | 11 | 231 | 22 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 12 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 13 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 14 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 15 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 16 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 17 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DCD"=1)
Статистика
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
698 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> |