найти перекрывающие диапазоны

Dima Ry
Дата: 17.03.2011 13:10:02
Уважаемые знатоки!!
Условия задачи такие: - клиент может заключить один и более договоров.
Один договор имеет дату начала и дату окончания. Периоды от разных договоров могут перекрываться произвольным образом. Надо выяснить периоды активности клиента. То есть периоды, когда был заключен хотя бы один договор

Например :
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


/* ... Nй период активности ....*/

/* ... подобные записи для других клиентов....*/

)

select * from t

должен дать
2010.01 2010.05 client1
2011.01 2011.04 client1
Elic
Дата: 17.03.2011 13:36:38
Ключевые слова для поиска какие использовал?
ORA__SQL
Дата: 17.03.2011 15:03:23
Dima Ry,
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
),
t2 AS (
SELECT *
FROM (
SELECT d1,
       LAG(d1,1,d1) OVER(ORDER BY d1 DESC) d2
FROM (
select d1
  from t
UNION
select d2
  from t
) ) z
WHERE EXISTS (SELECT NULL FROM t t1 WHERE t1.d1<=z.d1 AND t1.d2>=z.d2)
),
t3 AS 
(
SELECT ROWNUM rn, d1,d2,(CASE WHEN d2<>d1_bef OR d1_bef IS NULL THEN ROWNUM  ELSE NULL END) gr
FROM (
SELECT d1,d2,LAG(d1) OVER(ORDER BY d1 DESC) d1_bef
FROM t2
ORDER BY d1,d2
))
SELECT MIN(d1) d1,MAX(d2) d2
FROM (
SELECT d1,d2,(SELECT nvl(MAX(gr),1) FROM t3 mx WHERE mx.rn<t3.rn) gr
  FROM t3
)
GROUP BY gr
ORDER BY d1,d2
orawish
Дата: 17.03.2011 15:19:58
оффт (прошу пардону за стёб - ничего личного, ну не выдержал я )

картина уныния в граде национальной (и какой!) славы
.. То есть периоды, когда был заключен хотя бы один договор..


а по поиску - start_of_group поищите
AmKad
Дата: 17.03.2011 15:31:13
Dima Ry,

+ 11+
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
Dima Ry
Дата: 17.03.2011 16:03:31
Хотел тоже посмеятся, но не осилил такого свертонкого юмора.

orawish
оффт (прошу пардону за стёб - ничего личного, ну не выдержал я )

картина уныния в граде национальной (и какой!) славы
.. То есть периоды, когда был заключен хотя бы один договор..


а по поиску - start_of_group поищите