Получение дней между двумя датами для нескольких идентификаторов

Это дополнительный вопрос от Как получить список месяцев между двумя заданными датами с помощью запроса? действительно. (Я подозреваю, что это потому, что я не совсем понимаю логику соединений по уровням!)

У меня есть список таких данных

ID | START_DATE  | END_DATE
 1 | 01-JAN-2018 | 20-JAN-2018
 2 | 13-FEB-2018 | 20-MAR-2018
 3 | 01-MAR-2018 | 07-MAR-2018

и то, что я хочу попробовать, - это список со всеми днями между датой начала и окончания для каждого идентификатора.

Так, например, я хочу список, который дает

ID | DATE
 1 | 01-JAN-2018
 1 | 02-JAN-2018
 1 | 03-JAN-2018 
...
 1 | 19-JAN-2018
 1 | 20_JAN-2018
 2 | 13-FEB-2018
 2 | 14-FEB-2018
 2 | 15-FEB-2018 
...

и т.п.

То, что я пытался сделать, это адаптировать один из ответов по приведенной выше ссылке следующим образом.

select id
, trunc(start_date+((level-1)),'DD') 
from (
  select id
  , start_date
  , end_date
  from blah
 ) 
connect by level <= ((trunc(end_date,'DD')-trunc(start_date,'DD'))) + 1

что дает мне то, что я хочу, но затем целый ряд повторяющихся дат, как будто это похоже на декартово соединение. Есть ли что-то простое, что мне нужно добавить, чтобы исправить это?


sql oracle oracle12c hierarchical-query
person C. Coles    schedule 04.07.2018    source источник
comment
Это потому, что это декартово соединение - вы вообще не ограничиваете на основе идентификатора, поэтому он пытается генерировать строки на основе всех предыдущих начальных (исходных) строк. В качестве примечания: ваши данные показывают инклюзивную верхнюю границу желаемых результатов - в долгосрочной перспективе гораздо лучше использовать эксклюзивную верхнюю границу (и обратите внимание, что принятые также используют эксклюзивную). Это особенно важно в Oracle из-за того, что тип DATE также включает время.   -  person Clockwork-Muse    schedule 04.07.2018

Ответы (2)


arrow_upward
2
arrow_downward

Мне нравятся рекурсивные CTE:

with cte as (
      select id, start_dte as dte, end_dte
      from blah
      union all
      select id, dte + 1, end_dte
      from cte
      where dte < end_dte
     )
select *
from cte
order by id, dte;

Это стандартный синтаксис ANSI, который работает и в некоторых других базах данных.

person Gordon Linoff    schedule 04.07.2018
comment
Привет, Гордон, большое спасибо - я никогда раньше не использовал рекурсивный CTE. Боже, мне нужно выучить так много трюков с SQL :) - person C. Coles; 04.07.2018

arrow_upward
0
arrow_downward

Иерархический запрос, который вы пытались выполнить, должен включать id = prior id в предложение connect-by, но, поскольку это вызывает циклы с несколькими исходными строками, вам также необходимо включить вызов недетерминированной функции, такой как dbms_random.value:

select id, start_date + level - 1 as day
from blah
connect by level <= end_date - start_date + 1
and prior id = id
and prior dbms_random.value is not null

С вашими демонстрационными данными в CTE вы получите 63 строки:

with blah (ID, START_DATE, END_DATE) as (
            select 1, date '2018-01-01', date '2018-01-20' from dual
  union all select 2, date '2018-02-13', date '2018-03-20' from dual
  union all select 3, date '2018-03-01', date '2018-03-07' from dual
)
select id, start_date + level - 1 as day
from blah
connect by level <= end_date - start_date + 1
and prior id = id
and prior dbms_random.value is not null;

        ID DAY       
---------- ----------
         1 2018-01-01
         1 2018-01-02
         1 2018-01-03
...
         1 2018-01-19
         1 2018-01-20
         2 2018-02-13
         2 2018-02-14
...
         3 2018-03-05
         3 2018-03-06
         3 2018-03-07

Вам не нужно trunc() даты, если они не относятся к полуночи, что кажется маловероятным в этом случае, и даже в этом случае может не быть необходимости, если только конечная дата имеет более позднее время (например, 23:59:59) .

Однако рекурсивный CTE во многих отношениях более интуитивно понятен, по крайней мере, если вы понимаете их основную идею; так что я бы, вероятно, тоже использовал подход Гордона. Могут быть различия в производительности и в том, работают ли они вообще для больших объемов данных (или сгенерированных строк), но для большого количества данных стоит сравнить разные подходы, чтобы в любом случае найти наиболее подходящий/эффективный.

person Alex Poole    schedule 04.07.2018
comment
Большое спасибо, Алекс, очень полезно иметь пример, к которому я могу вернуться и попрактиковаться! - person C. Coles; 04.07.2018