Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Сложный запрос sql  [new]
Slawa_programs
Guest
Есть таблица calls с несколькими миллионами строк.

Пример нескольких строк, где dt - дата, duration - длительность в секундах, cost - стоимость, a и b некоторые произвольные атрибуты.

DTDURATIONCOSTAB
01.01.2011 10:00:00203012
01.01.2011 11:00:002412
01.01.2011 11:00:028812
01.01.2011 11:00:1060012
01.01.2011 12:00:355012
01.01.2011 12:00:40101012
01.01.2011 11:00:0060021
01.01.2011 12:00:00301021
01.01.2011 02:40:055032
01.01.2011 02:40:10402332
01.01.2011 22:34:2020632
01.01.2011 22:34:4010032


with calls as (
select to_date('01.01.2011 10:00:00','dd.mm.yyyy hh24:mi:ss') dt, 20 duration, 30 cost, 1 a, 2 b from dual union all
select to_date('01.01.2011 11:00:00','dd.mm.yyyy hh24:mi:ss'), 2, 4, 1, 2 from dual union all
select to_date('01.01.2011 11:00:02','dd.mm.yyyy hh24:mi:ss'), 8, 8, 1, 2 from dual union all
select to_date('01.01.2011 11:00:10','dd.mm.yyyy hh24:mi:ss'), 60, 0, 1, 2 from dual union all
select to_date('01.01.2011 12:00:35','dd.mm.yyyy hh24:mi:ss'), 5, 0, 1, 2 from dual union all
select to_date('01.01.2011 12:00:40','dd.mm.yyyy hh24:mi:ss'), 10, 10, 1, 2 from dual union all
select to_date('01.01.2011 02:40:05','dd.mm.yyyy hh24:mi:ss'), 5, 0, 3, 2 from dual union all
select to_date('01.01.2011 02:40:10','dd.mm.yyyy hh24:mi:ss'), 40, 23, 3, 2 from dual union all
select to_date('01.01.2011 22:34:20','dd.mm.yyyy hh24:mi:ss'), 20, 6, 3, 2 from dual union all
select to_date('01.01.2011 22:34:40','dd.mm.yyyy hh24:mi:ss'), 10, 0, 3, 2 from dual union all
select to_date('01.01.2011 11:00:00','dd.mm.yyyy hh24:mi:ss'), 60, 0, 2, 1 from dual union all
select to_date('01.01.2011 12:00:00','dd.mm.yyyy hh24:mi:ss'), 30, 10, 2, 1 from dual 
)

Требуется сгруппировать записи, у которых атрибуты A и B равны и время начала DT строчки равно (DT+DURATION/24/3600) предыдущей строчки. DT взять минимальный для группы, а DURATION и COST суммировать.

Т.е. получить с помощью SQL запроса такой результат:

DTDURATIONCOSTAB
01.01.2011 10:00:00203012
01.01.2011 11:00:00701212
01.01.2011 12:00:35151012
01.01.2011 11:00:0060021
01.01.2011 12:00:00301021
01.01.2011 02:40:05452332
01.01.2011 22:34:2030632


Я пробовал различные комбинации lead/lag и других функций, но ничего не получилось пока. Уже почти готов запихнуть все это в коллекцию и провернуть в PL/SQL.

Заранее спасибо.
27 янв 11, 00:55    [10137593]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос sql  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
набросок, работающий на этих данных
select min(dt) dt, sum(duration) sduration, sum(cost) scost, a, b
from
(
  select d.*, sum(bgr)over(partition by a, b order by dt) gr
  from
  (
    select c.*, 
           case when abs(dt - lag(dt)over(partition by a, b order by dt) 
                            - lag(duration)over(partition by a, b order by dt)/24/3600) < 0.00000001 
                then 0 else 1 end bgr
    from calls c
  ) d
)
group by gr, a, b
27 янв 11, 01:17    [10137613]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос sql  [new]
AmKad
Member

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

with s as (
select to_date('01.01.2011 10:00:00','dd.mm.yyyy hh24:mi:ss') dt, 20 duration, 30 cost, 1 a, 2 b from dual union all
select to_date('01.01.2011 11:00:00','dd.mm.yyyy hh24:mi:ss'), 2, 4, 1, 2 from dual union all
select to_date('01.01.2011 11:00:02','dd.mm.yyyy hh24:mi:ss'), 8, 8, 1, 2 from dual union all
select to_date('01.01.2011 11:00:10','dd.mm.yyyy hh24:mi:ss'), 60, 0, 1, 2 from dual union all
select to_date('01.01.2011 12:00:35','dd.mm.yyyy hh24:mi:ss'), 5, 0, 1, 2 from dual union all
select to_date('01.01.2011 12:00:40','dd.mm.yyyy hh24:mi:ss'), 10, 10, 1, 2 from dual union all
select to_date('01.01.2011 02:40:05','dd.mm.yyyy hh24:mi:ss'), 5, 0, 3, 2 from dual union all
select to_date('01.01.2011 02:40:10','dd.mm.yyyy hh24:mi:ss'), 40, 23, 3, 2 from dual union all
select to_date('01.01.2011 22:34:20','dd.mm.yyyy hh24:mi:ss'), 20, 6, 3, 2 from dual union all
select to_date('01.01.2011 22:34:40','dd.mm.yyyy hh24:mi:ss'), 10, 0, 3, 2 from dual union all
select to_date('01.01.2011 11:00:00','dd.mm.yyyy hh24:mi:ss'), 60, 0, 2, 1 from dual union all
select to_date('01.01.2011 12:00:00','dd.mm.yyyy hh24:mi:ss'), 30, 10, 2, 1 from dual 
)
select new_id, min(dt) dt, sum(duration) duration, sum(cost) cost, max(a) a, max(b) b
from
   (select dt, duration, cost, a, b,
    last_value(case when is_first = 1 then id end ignore nulls) over (partition by a, b order by dt) new_id
    from
       (select case when dt = lag(dt) over (partition by a, b order by dt) + lag(DURATION/24/3600) over (partition by a, b  order by dt) then 0
               else 1
               end is_first, s.*
        from
           (select rownum id, s.* 
            from s
           ) s
       )    
   )    
group by new_id   
order by a, dt
 
27 янв 11, 01:23    [10137618]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос sql  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
_Nikotin, AmKad, такие варианты пройдут только, если нет конференц-связи или звонков на удержании, т.к. в случае с ними между связанными звонками может вклиниться "левый" звонок и через предыдущий искать будет нельзя.

зы. Хотел сделать через collect, но нарвался на пятничная задачка - убить себя одним SELECT-ом и настроение испортилось...
27 янв 11, 06:37    [10137744]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос sql  [new]
Slawa_programs
Guest
_Nikotin

AmKad


Огромное спасибо.

Буду сегодня пробывать на реальных данных.

xtender
_Nikotin, AmKad, такие варианты пройдут только, если нет конференц-связи или звонков на удержании, т.к. в случае с ними между связанными звонками может вклиниться "левый" звонок и через предыдущий искать будет нельзя.


У таких записей будут разные атрибуты A и B.
27 янв 11, 11:48    [10139224]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить