Группировать (иногда непоследовательно) период дней в Oracle SQL

Я ищу запрос, который занимает в основном последовательные дни и группирует их вместе в зависимости от того, действительно ли они последовательные, и от значения, принадлежащего этой дате. Я использую Oracle версии 11g.

Вот некоторые примеры данных:

date          value
2012-01-01    2000
2012-01-02    2000 //(there is no data for Jan 03 for example)
2012-01-04    2000
2012-01-05    5000
2012-01-06    5000
2012-01-07    5000
2012-01-08    2000
2012-01-09    2000
2012-01-10    2000

(это результат довольно большого запроса)

То, что я ищу, сгруппировало бы эти дни в такие периоды:

from_date   to_date     value
2012-01-01  2012-01-02  2000
2012-01-04  2012-01-04  2000
2012-01-05  2012-01-07  5000
2012-01-08  2012-01-10  2000

Нам удалось сформулировать запрос, который делает то, что я хочу, но это не очень эффективный способ, и я почти уверен, что существует что-то лучшее/более элегантное. Это то, что я использую сейчас:

with temp_table as (
select a.pk_date DATE1, c.pk_date DATE2, a.volume VOL1
from dm_2203 a, dm_2203 c
where a.volume = c.volume
  and a.pk_date <= c.pk_date
  and not exists (select 1 from dm_2203 b
                  where a.volume = b.volume
                    and a.pk_date = b.pk_date+1)
  and not exists (select 1 from dm_2203 d
                  where c.volume = d.volume
                    and c.pk_date = d.pk_date-1)  )
select * from temp_table y
where date2-date1+1 = (select count(*)
                       from dm_2203 z
                       where z.pk_date between y.date1 and y.date2
                         and y.vol1 = z.volume)
order by 1;

У кого-нибудь есть идея, как сделать это быстрее и без всех объединений? Спасибо!


sql date oracle gaps-and-islands
person Xuntar    schedule 22.03.2012    source источник
comment
Какая это версия Оракла?   -  person    schedule 22.03.2012
comment
Я не понимаю логики желаемого результата. Почему эти две строки являются результатом?   -  person Lamak    schedule 22.03.2012
comment
@Lamak: набор из двух строк не является желаемым результатом - набор из четырех строк является желаемым результатом.   -  person    schedule 22.03.2012
comment
@Lamak: я удалил строки, чтобы быть более ясным, но на самом деле: это был не желаемый результат, это то, что я получаю при использовании обычного предложения group by   -  person Xuntar    schedule 22.03.2012
comment
Благодарю за разъяснение.   -  person Lamak    schedule 22.03.2012
comment
@MarkBannister: извините, что не добавил это раньше к вопросу. Это версия 11g   -  person Xuntar    schedule 22.03.2012
comment
Похоже, он пытается показать объем для каждой полосы данных там, где он неизменен — где день X+1 имеет то же значение, что и день X.   -  person Adam Musch    schedule 22.03.2012

Ответы (2)


arrow_upward
3
arrow_downward

Я думаю, что это должно работать, а также быть достаточно эффективным (он должен попасть на стол только один раз)

create table t ( d date, v number);

insert into t values (trunc(sysdate), 100);
insert into t values (trunc(sysdate+2), 100);
insert into t values (trunc(sysdate+3), 100);
insert into t values (trunc(sysdate+4), 100);
insert into t values (trunc(sysdate+5), 200);
insert into t values (trunc(sysdate+6), 200);
insert into t values (trunc(sysdate+7), 200);
insert into t values (trunc(sysdate+8), 100);

select min(d), max(d), v
from (
    select d, v, 
        sum( gc) over (partition by v order by d) g
    from (
        select d, v, 
            (case (d - lag(d) over ( partition by v order by d) )
                when 1 then 0
                else 1
             end) gc
        from t
    )
) group by v, g
order by min(d), v

Обратите внимание: если вы хотите эффективно запустить логику на подмножестве ваших данных, вы должны добавить предложение where в самый внутренний выбор. В противном случае у оракула возникнут проблемы с использованием любого индекса.

person Jens Schauder    schedule 22.03.2012
comment
Очень хорошо. Вот SQL Fiddle, использующий тестовые данные и структуру таблицы OP с использованием вашего кода. - person Conrad Frix; 22.03.2012
comment
Спасибо за ссылку на SQL Fiddle. Потрясающий инструмент. - person Jens Schauder; 22.03.2012
comment
@JensSchauder: Это выглядит очень красиво, и сейчас я пытаюсь адаптировать его для своего запроса. На самом деле у меня есть еще несколько полей, кроме даты и значения, но общая идея должна быть такой же. Спасибо уже! - person Xuntar; 22.03.2012
comment
@Xuntar, Jens Спасибо, на самом деле я впервые использовал его. Я наткнулся на это вчера в этом for-specific">Метавопрос - person Conrad Frix; 22.03.2012
comment
@JensSchauder: Что-то очень странное происходит, когда я проверяю это на своих реальных данных. Я получаю сообщение об ошибке ORA-00932: несовместимые типы данных: ожидаемый ИНТЕРВАЛ ДЕНЬ ДО ВТОРОЙ получил ЧИСЛО, когда я добавляю g в группу по функции. Без g ошибки нет, но и результат, конечно, неверный. Теперь пытаюсь понять, почему это происходит... - person Xuntar; 22.03.2012
comment
@JensSchauder: очевидно, что сохранение дат в виде меток времени дает ошибку при сравнении с 1. Вместо этого мне пришлось сравнивать с NUMTODSINTERVAL(1, 'DAY'). Спасибо за ваше решение! - person Xuntar; 22.03.2012

arrow_upward
0
arrow_downward

Пытаться:

with cte as (
select pk_date from_date, pk_date to_date, volume
from dm_2203 d
where not exists
(select null from dm_2203 e 
 where d.volume = e.volume and d.pk_date-1 = e.pk_date)
union all
select c.pk_date from_date, d.pk_date to_date, c.volume
from cte c
join dm_2203 d on d.volume = c.volume and d.pk_date = c.pk_date+1)
select from_date, max(to_date) to_date, volume "value"
from cte
group by from_date, volume
person Community    schedule 22.03.2012