Первая и последняя даты в блоке непрерывных дат

ProgramBox

Первая и последняя даты в блоке непрерывных дат

Post author:admin
Запись опубликована:19 марта, 2023
Post category:Вопросы по программированию

#sql #oracle

#sql #Oracle

Вопрос:

Мне нужно выяснить, как захватить эти блоки в моих данных. В настоящее время у меня есть запрос, который фиксирует первую и последнюю дату для каждого дня. Блоки в каждый день должны быть разделены. Я хочу зафиксировать минимальную и максимальную дату для каждого блока, технологии, дня и количества блоков (30-минутных сегментов) с первой даты до последнего дня. Спасибо за ответы!

Ответ №1:

Вы можете увидеть, с чего начинаются группы, используя lag() , а затем использовать совокупную сумму и агрегацию. Функции даты и времени различаются в разных базах данных, но идея заключается в:

 select min(datedate), max(datedate), tech
from (select t.*,
             sum(case when datedate >= datedate - interval '30' minute then 0 else 1 end) over
                 (partition by tech order by datedate) as grp
      from (select t.*,
                   lag(datedate) over (partition by tech order by datedate) as prev_datedate
            from t
           ) t
     ) t
group by grp, tech;

Это предполагает, что у вас есть отдельная серия для каждой «технологии», но я не знаю, так ли это на самом деле.

1. Гордон, Oracle sql не нравится это: datedate — интервал ’30 минут’ Что здесь происходит? Извините, мне не хватает навыков в расширенном sql

2. @user3642767 . . . Помечайте свои вопросы базой данных, которую вы используете. Я исправил тег и скорректировал ответ.

3. Гордон, я обновил интервал: datedate — интервал ’30’ минут, после чего данные по-прежнему группируют все данные вместе.

4. Спасибо, Гордон. С вашим кодом я понял, как заставить его работать.

Ответ №2:

Я не знаю, что предложенное решение сработало для вас. Но это не для меня. Итак, я придумал свой собственный, используя мощный match_recognize от 12c. Дайте мне знать, если это то, что вам нужно.

Для создания ваших образцов данных.

 create table tab as
with inputs(datedate, tech, sched_start, sched_end ) as
(
    select to_date('2020-09-24 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-24 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-24 17:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-24 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-24 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-24 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-24 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-24 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-24 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-25 12:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 12:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 12:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 13:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 13:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 13:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 13:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 14:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 14:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 14:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 14:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 14:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 14:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 15:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-25 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-25 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-25 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-25 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-28 15:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 15:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 16:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 16:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 16:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 16:30:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 16:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    
    select to_date('2020-09-29 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 12:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 13:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-29 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 13:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 13:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-29 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:00:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss') from dual union all
    select to_date('2020-09-29 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), 'crawford', to_date('2020-09-28 17:30:30', 'yyyy-mm-dd hh24:mi:ss'), to_date('2020-09-28 18:00:30', 'yyyy-mm-dd hh24:mi:ss') from dual
    
)
select * from inputs;

И решение:

 select * from tab
match_recognize
(
    partition by tech
    order by datedate
    measures first(datedate) as min_date,
             last(datedate) as max_date,
             count(*) as total,
             trunc(datedate) as dt
    pattern ( a b*)
    define  b as datedate <= prev(datedate)   numtodsinterval(30, 'minute')
);

Вывод: