Блог

Используя Oracle 12c, мне нужно запустить скрипт для существующей сводной таблицы проектов. В сводной таблице есть проект, дата начала и дата окончания. Мне нужно разбить эти данные на количество дней в месяц для каждого проекта.

Примером может служить проект A с датой начала 2/10/2016 и датой окончания 3/10/2016. Мой конечный результат для этого примера должен быть:

 Project A, February, 19
Project A, March, 10
 

Это было проще, поскольку некоторые даты могут занимать 2 или 3 месяца. Это не кажется слишком сложным, но по какой-то причине у меня возникают проблемы с тем, чтобы обдумать это и обдумать. У кого-нибудь есть быстрое и простое решение для этого? Я хотел бы запустить это как оператор SQL, но также будет работать сценарий PL / SQL.

В этом решении мы не предполагаем никаких предварительных знаний о охватываемом периоде времени. Кроме того, это решение не использует объединения (что может быть важно для производительности).

 with
-- begin test data (this section can be deleted)
     inputs ( project, start_date, end_date ) as (
       select 'A', date '2014-10-03', date '2014-12-15' from dual union all
       select 'B', date '2015-03-01', date '2015-03-31' from dual union all
       select 'C', date '2015-11-30', date '2016-03-01' from dual
     ),
-- end test data; solution begins here (it includes the word "with" from the first line)
     prep ( project, end_date, dt ) as (
       select project, end_date, start_date   from inputs union all
       select project, end_date, end_date   1 from inputs union all
       select project, end_date, add_months( trunc(start_date, 'mm'), level )
         from inputs
         connect by add_months (trunc(start_date, 'mm'), level) <= end_date
                and prior project = project
                and prior sys_guid() is not null
     ),
     computations ( project, dt, month, day_count ) as (
       select project, dt, to_char(dt, 'Mon-yyyy'),
              lead(dt) over (partition by project order by dt) - dt
       from   prep
       where  dt <= end_date   1
     )
select project, month, day_count
from   computations
where  day_count > 0
order by project, dt
;
 

ВЫВОД:

 PROJECT MONTH    DAY_COUNT
------- -------- ---------
A       Oct-2014        29
A       Nov-2014        30
A       Dec-2014        15
B       Mar-2015        31
C       Nov-2015         1
C       Dec-2015        31
C       Jan-2016        31
C       Feb-2016        29
C       Mar-2016         1

 9 rows selected 
 

Если вы можете сделать предположение о максимальном количестве дней для проекта (1000 в моем примере), вы можете использовать следующее:

 with yourTable(project, startDate, endDate) as 
(
    select 'Project a' as project,
           date '2016-02-10' as startDate,
           date '2016-03-10' as endDate
    from dual
    UNION ALL
    select 'Project XX',
           date '2016-01-01',
           date '2016-01-10'
    from dual
)
select project, to_char(startDate   n, 'MONTH'), count(1)
from yourTable
  inner join (
                select level n
                from dual
                connect by level <= 1000
             )
    on (startDate   n <= endDate)        
group by project, to_char(startDate   n, 'MONTH')  
 

Часть с подключением ПО используется в качестве генератора дат, предполагая, что каждый проект длится не более 1000 дней; внешний запрос использует генератор дат для разделения строки проекта на множество строк, по одной на каждый день между начальной и конечной датой, а затем агрегирует по месяцам и проектам для полученияпостройте выходные данные.

Немного другой способ, основанный на месяцах, а не на днях, может быть:

 select project, to_char(add_months(startDate, n ), 'MONTH'),
       case
        when trunc(add_months(startDate, n ), 'MONTH') = trunc(add_months(endDate, n ), 'MONTH')
            then endDate - startDate  1
        when trunc(add_months(startDate, n ), 'MONTH') <= startDate
            then last_day(add_months(startDate, n)) - startDate
        when last_day(add_months(startDate, n )) >= endDate
            then endDate - trunc(add_months(startDate, n ), 'MONTH')  1      
        else
            last_day(add_months(startDate, n )) - trunc(last_day(add_months(startDate, n )), 'MONTH')
       end as numOfDays       
from yourTable
  inner join (
                select level -1 n
                from dual
                connect by level <= 1000
             )
    on trunc(add_months(startDate, n ), 'MONTH') <= trunc(endDate, 'MONTH') 
 

Это немного сложнее для обработки разных случаев, но более эффективно, учитывая, что оно работает на уровне месяца, а не на уровне дня

Я думаю, вам нужно что-то вроде:

 WITH sample_data AS (SELECT 'A' PROJECT, to_date('10/02/2016', 'dd/mm/yyyy') start_date, to_date('10/03/2016', 'dd/mm/yyyy') end_date FROM dual UNION ALL
                     SELECT 'B' PROJECT, to_date('10/02/2016', 'dd/mm/yyyy') start_date, to_date('10/06/2016', 'dd/mm/yyyy') end_date FROM dual UNION ALL
                     SELECT 'C' PROJECT, to_date('10/02/2016', 'dd/mm/yyyy') start_date, to_date('18/02/2016', 'dd/mm/yyyy') end_date FROM dual)
SELECT PROJECT,
       to_char(add_months(trunc(start_date, 'mm'), LEVEL -1), 'fmMonth yyyy', 'nls_date_language=english') mnth,
       CASE WHEN trunc(end_date, 'mm') = add_months(trunc(start_date, 'mm'), LEVEL -1)
                 THEN end_date
            ELSE add_months(trunc(start_date, 'mm'), LEVEL) -1
       END - CASE WHEN trunc(start_date, 'mm') = add_months(trunc(start_date, 'mm'), LEVEL -1)
                 THEN start_date   1
            ELSE add_months(trunc(start_date, 'mm'), LEVEL -1)
       END   1 num_days
FROM   sample_data
CONNECT BY PRIOR PROJECT = PROJECT
           AND PRIOR sys_guid() IS NOT NULL
           AND add_months(trunc(start_date, 'mm'), LEVEL -1) <= TRUNC(end_date, 'mm');

PROJECT MNTH             NUM_DAYS
------- -------------- ----------
A       February 2016          19
A       March 2016             10
B       February 2016          19
B       March 2016             31
B       April 2016             30
B       May 2016               31
B       June 2016              10
C       February 2016           8
 

При этом используется многорядный метод подключения по уровням (наличие and prior sys_guid() is not null позволяет подключению по циклически перебирать каждую строку отдельно) для перебора каждой строки проекта в таблице sample_data (предположительно, у вас уже есть информация о проекте в таблице, поэтому вам не нужен подзапрос sample_dataвообще; вы могли бы просто ссылаться на свою таблицу непосредственно в основном SQL).

Затем мы сравниваем месяц начальной даты с месяцем строки, генерируемой connect by, и если это тот же месяц, то мы знаем, что нам нужно использовать начальную дату, в противном случае мы используем первое число месяца сгенерированной строки; мы делаем то же самое для конца строки.дата.

Таким образом, теперь мы можем вычесть одно из другого и внести коррективы, чтобы сделать расчет правильным. Возможно, вам придется настроить это самостоятельно, если вам нужно, чтобы начальная и конечная даты одного и того же дня считались как 1 день, а не 0 — вероятно, потребуется дополнительная инструкция case, чтобы учесть, когда начальная и конечная даты находятся в одном месяце.

Использование этого подхода не ограничит продолжительность вашего проекта; он может быть сколь угодно длинным.

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