Нахождение частей "многодневного" диапазона времени и даты, попадающего в / за пределами дневного диапазона (-ов) переменной даты-времени

Question

Как решить эту проблему в Oracle SQL?

1. У меня есть столбцы Event_Min_DateTime (например, 01/06/2017 7:30:00) и Event_Max_datetime (например, 03/06/2017 18:30:00) Event_ID (например, ev001). Таким образом, вычисляемый столбец Event_minutes может быть создан как (Event_Max_datetime - Event_Min_DateTime) * 24 * 60.

2. У меня также есть столбцы Daily_Window_Start (переменная TimeStamp Value) и Daily_Window_End (переменная TimeStamp Value) Daily_Date (последовательно каждый день). Таким образом, вычисляемый столбец Daily_Window_minutes можно рассчитать как (Daily_Window_End - Daily_Window_Start) * 24 * 60. Обратите внимание, что Daily_Window_minutes всегда будет <=1440.

Теперь, как рассчитать столбцы Daily_portion_within (часть Event_minutes, попадающих в Daily_Window_minutes) и Daily_portion_outside (часть Event_minutes, попадающих вне Daily_Window_minutes, но в пределах той же Daily_Date)?

Answer 1

Этот пример должен помочь:

select trunc(d1) as day, inside, (d2 - d1) * 24 * 60 - inside as outside 
  from (select e.*, d.*,
               case when d2 < e1 or e2 < d1 then 0
                    else least(e2, d2) - greatest(e1, d1)
               end * 24 * 60 as inside
          from e cross join d)

Данные:

create table e (id number(3), e1 date, e2 date);
insert into e values(1, timestamp '2017-06-01 07:30:00', timestamp '2017-06-03 18:30:00');

create table d (d1 date, d2 date);
insert into d values(timestamp '2017-06-01 06:00:00', timestamp '2017-06-01 15:30:00');
insert into d values(timestamp '2017-06-02 09:30:00', timestamp '2017-06-02 22:30:00');
insert into d values(timestamp '2017-06-03 12:05:00', timestamp '2017-06-03 19:45:00');
insert into d values(timestamp '2017-06-04 15:00:00', timestamp '2017-06-04 16:30:00');

Результат:

DAY             INSIDE    OUTSIDE
----------- ---------- ----------
2017-06-01         480         90
2017-06-02         780          0
2017-06-03         385         75
2017-06-04           0         90

Answer 2

Из вопроса неясно, может ли Daily_Window_Start быть> Event_Min_DateTime, поэтому здесь я решил написать только для тех случаев, когда это не так. Кроме того, типы данных столбцов не очевидны из вашего вопроса, и преобразование не применяется. Вы можете написать свои запросы в похожих строках для Daily_Window_Start > Event_Min_DateTime, как указано ниже. Однако здесь есть много предположений.

   SELECT
           CASE
                  WHEN e. event_max_datetime <= d.daily_window_end THEN (e.event_max_datetime - e.event_min_datetime)*24*60
                  ELSE (d.daily_window_end                                                    - e.event_min_datetime)*24*60
           END daily_portion_within ,
           CASE
                  WHEN e. event_max_datetime <= d.daily_window_end THEN NULL
                  ELSE ( e.event_max_datetime - d.daily_window_end)*24*60
           END daily_portion_outside
    FROM   (
                  SELECT Trunc(event_max_datetime) dt ,
                         event_min_datetime ,
                         event_max_datetime
                  FROM   EVENTS ) EVENTS e,
           daily_window d
    WHERE  e.dt = trunc(d.dt)