Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Вывести не хватающие данные  [new]
alectr
Member

Откуда: Solar system
Сообщений: 137
Добрый вечер!

Прошу помощи в решении следующей задачи.

Есть данные:

with t as
(
select 152417 id, to_date('20.08.2011','DD.MM.YYYY') dt, 'A' param from dual union all
select 152417, to_date('18.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 152417, to_date('17.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 251269, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 251269, to_date('19.08.2011','DD.MM.YYYY'), 'C' from dual union all
select 251269, to_date('18.08.2011','DD.MM.YYYY'), 'D' from dual union all
select 251269, to_date('17.08.2011','DD.MM.YYYY'), 'M' from dual union all
select 377791, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 377791, to_date('17.08.2011','DD.MM.YYYY'), 'G' from dual
)
select * from t

IDDTPARAM
15241720.08.2011A
15241718.08.2011B
15241717.08.2011B
25126920.08.2011A
25126919.08.2011C
25126918.08.2011D
25126917.08.2011M
37779120.08.2011A
37779117.08.2011G


Нужно получить следующее:

IDDTPARAM
15241719.08.2011B
37779119.08.2011G
37779118.08.2011G


То есть нужно получить пропущенные строчки, которое определяются разрывом по полю DT для соответствующих значений ID. Значение поля PARAM нужно вытягивать из меньшего по дате так же по соответствующему ID.
Мин и Макс дату (для определения разрывов) можно рассматривать 17.08.2011 и 20.08.2011 в рамках данного примера.

Заранее спасибо!
25 авг 11, 18:36    [11180800]     Ответить | Цитировать Сообщить модератору
 Re: Вывести не хватающие данные  [new]
-2-
Member

Откуда:
Сообщений: 15330
(select id, min(param) keep (dense_rank first order order by dt) from данные group by id) cross join (select date '2011-08-17'-1+level from dual connect by level<=3)
minus
select id, param, dt from данные
25 авг 11, 18:55    [11180901]     Ответить | Цитировать Сообщить модератору
 Re: Вывести не хватающие данные  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
with t as
(
select 152417 id, to_date('20.08.2011','DD.MM.YYYY') dt, 'A' param from dual union all
select 152417, to_date('18.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 152417, to_date('17.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 251269, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 251269, to_date('19.08.2011','DD.MM.YYYY'), 'C' from dual union all
select 251269, to_date('18.08.2011','DD.MM.YYYY'), 'D' from dual union all
select 251269, to_date('17.08.2011','DD.MM.YYYY'), 'M' from dual union all
select 377791, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 377791, to_date('17.08.2011','DD.MM.YYYY'), 'G' from dual
)
,z as(
select t.id,t.dt,t.param,nvl(lead(dt) over (partition by id order by dt)-dt,1) xx from t
order by id,dt)
,mx as (select level lvl from dual connect by level <= (select max(xx) from z))
select z.id,z.dt+lvl-1,z.param,lvl
from z,mx
where lvl<=xx
order by z.id,z.dt+lvl-1
25 авг 11, 19:01    [11180943]     Ответить | Цитировать Сообщить модератору
 Re: Вывести не хватающие данные  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
+
with t as
(
select 152417 id, to_date('20.08.2011','DD.MM.YYYY') dt, 'A' param from dual union all
select 152417, to_date('18.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 152417, to_date('17.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 251269, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 251269, to_date('19.08.2011','DD.MM.YYYY'), 'C' from dual union all
select 251269, to_date('18.08.2011','DD.MM.YYYY'), 'D' from dual union all
select 251269, to_date('17.08.2011','DD.MM.YYYY'), 'M' from dual union all
select 377791, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 377791, to_date('17.08.2011','DD.MM.YYYY'), 'G' from dual
)
,t1 as(
select 
       id
       ,dt
       ,param
       ,case when dt+1 != lead(dt)over(partition by id order by dt) 
             then dt+1
        end group_start
       ,case when dt+1 != lead(dt)over(partition by id order by dt) 
             then lead(dt)over(partition by id order by dt) - 1
        end group_end
from t
)
select t1.*
from t1
     ,table(
            cast(
                  multiset(
                          select level 
                          from dual 
                          connect by level<=t1.group_end-t1.group_start+1
                          ) as sys.odcinumberlist
                )
           ) t
where group_start is not null
25 авг 11, 19:11    [11181001]     Ответить | Цитировать Сообщить модератору
 Re: Вывести не хватающие данные  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
+ поправил
with t as
(
select 152417 id, to_date('20.08.2011','DD.MM.YYYY') dt, 'A' param from dual union all
select 152417, to_date('18.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 152417, to_date('17.08.2011','DD.MM.YYYY'), 'B' from dual union all
select 251269, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 251269, to_date('19.08.2011','DD.MM.YYYY'), 'C' from dual union all
select 251269, to_date('18.08.2011','DD.MM.YYYY'), 'D' from dual union all
select 251269, to_date('17.08.2011','DD.MM.YYYY'), 'M' from dual union all
select 377791, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
select 377791, to_date('17.08.2011','DD.MM.YYYY'), 'G' from dual
)
,t1 as(
select 
       id
       ,dt
       ,param
       ,case when dt+1 != lead(dt)over(partition by id order by dt) 
             then dt+1
        end group_start
       ,case when dt+1 != lead(dt)over(partition by id order by dt) 
             then lead(dt)over(partition by id order by dt) - 1
        end group_end
from t
)
select t1.id
       ,t1.dt+t2.column_value dt
       ,t1.param
from t1
     ,table(
            cast(
                  multiset(
                          select level 
                          from dual 
                          connect by level<=t1.group_end-t1.group_start+1
                          ) as sys.odcinumberlist
                )
           ) t2
where group_start is not null
25 авг 11, 19:18    [11181028]     Ответить | Цитировать Сообщить модератору
 Re: Вывести не хватающие данные  [new]
-2-
Member

Откуда:
Сообщений: 15330
select *
from t
model
reference диапазон
   on (select 0 rid, date '2011-08-17' mindt, date '2011-08-20' maxdt from dual) 
   dimension by (rid) 
   measures     (mindt, maxdt) 
main данные
   partition by (id)
   dimension by (dt)
   measures     (param, param былparam, dt былdt)
   rules (
      param[for dt from диапазон.mindt[0] to диапазон.maxdt[0] increment 1]
         =  presentv(param[cv()], param[cv()],
                     min(param) keep (dense_rank first order by былdt nulls last)[any]
                    )
)
order by 1, 2;

ID                     DT                        PARAM БЫЛPARAM БЫЛDT                     
---------------------- ------------------------- ----- -------- ------------------------- 
152417                 17.08.2011 00:00          B     B        17.08.2011 00:00          
152417                 18.08.2011 00:00          B     B        18.08.2011 00:00          
152417                 19.08.2011 00:00          B                                        
152417                 20.08.2011 00:00          A     A        20.08.2011 00:00          
251269                 17.08.2011 00:00          M     M        17.08.2011 00:00          
251269                 18.08.2011 00:00          D     D        18.08.2011 00:00          
251269                 19.08.2011 00:00          C     C        19.08.2011 00:00          
251269                 20.08.2011 00:00          A     A        20.08.2011 00:00          
377791                 17.08.2011 00:00          G     G        17.08.2011 00:00          
377791                 18.08.2011 00:00          G                                        
377791                 19.08.2011 00:00          G                                        
377791                 20.08.2011 00:00          A     A        20.08.2011 00:00          

 12 rows selected 
25 авг 11, 20:32    [11181292]     Ответить | Цитировать Сообщить модератору
 Re: Вывести не хватающие данные  [new]
alectr
Member

Откуда: Solar system
Сообщений: 137
Спасибо всем за потраченное время, завтра буду тестить :)
25 авг 11, 20:42    [11181326]     Ответить | Цитировать Сообщить модератору
 Re: Вывести не хватающие данные  [new]
AmKad
Member

Откуда:
Сообщений: 5222
alectr,

+ При уникальности даты в рамках id
set autotrace on explain stat;

with t as
(select 152417 id, to_date('20.08.2011','DD.MM.YYYY') dt, 'A' pr from dual union all
 select 152417, to_date('18.08.2011','DD.MM.YYYY'), 'B' from dual union all
 select 152417, to_date('17.08.2011','DD.MM.YYYY'), 'B' from dual union all
 select 251269, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
 select 251269, to_date('19.08.2011','DD.MM.YYYY'), 'C' from dual union all
 select 251269, to_date('18.08.2011','DD.MM.YYYY'), 'D' from dual union all
 select 251269, to_date('17.08.2011','DD.MM.YYYY'), 'M' from dual union all
 select 377791, to_date('20.08.2011','DD.MM.YYYY'), 'A' from dual union all
 select 377791, to_date('17.08.2011','DD.MM.YYYY'), 'G' from dual)
select id, dt, pr
from
 (select id, dt, pr, mx
  from t
  model
  partition by (id)
  dimension by (dt - min(dt) over (partition by id) rn)
  measures (dt, pr, max(dt) over (partition by id) mx)
  rules
  (dt[for rn from 0 to mx[0] - dt[0] increment 1] = dt[0] + cv(rn),
   pr[for rn from 0 to mx[0] - dt[0] increment 1] = pr[0]
  )
 )
where mx is null
order by id, dt;

        ID DT         P
---------- ---------- -
    152417 19.08.2011 B
    377791 18.08.2011 G
    377791 19.08.2011 G


План выполнения
----------------------------------------------------------
Plan hash value: 1792928794

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     9 |   306 |    20  (10)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     9 |   306 |    20  (10)| 00:00:01 |
|*  2 |   VIEW              |      |     9 |   306 |    19   (6)| 00:00:01 |
|   3 |    SQL MODEL ORDERED|      |     9 |   144 |    19   (6)| 00:00:01 |
|   4 |     WINDOW SORT     |      |     9 |   144 |    19   (6)| 00:00:01 |
|   5 |      VIEW           |      |     9 |   144 |    18   (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 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MX" IS NULL)


Статистика
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        607  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed
26 авг 11, 09:52    [11182594]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить