#oracle
#Oracle
Вопрос:
Используя Oracle 12c, мне нужно запустить скрипт для существующей сводной таблицы проектов. В сводной таблице есть проект, дата начала и дата окончания. Мне нужно разбить эти данные на количество дней в месяц для каждого проекта.
Примером может служить проект A с датой начала 2/10/2016 и датой окончания 3/10/2016. Мой конечный результат для этого примера должен быть:
Project A, February, 19
Project A, March, 10
Это было проще, поскольку некоторые даты могут занимать 2 или 3 месяца. Это не кажется слишком сложным, но по какой-то причине у меня возникают проблемы с тем, чтобы обдумать это и обдумать. У кого-нибудь есть быстрое и простое решение для этого? Я хотел бы запустить это как оператор SQL, но также будет работать сценарий PL / SQL.
Комментарии:
1. Пожалуйста, напишите, что вы пробовали до сих пор
Ответ №1:
В этом решении мы не предполагаем никаких предварительных знаний о охватываемом периоде времени. Кроме того, это решение не использует объединения (что может быть важно для производительности).
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
Ответ №2:
Если вы можете сделать предположение о максимальном количестве дней для проекта (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')
Это немного сложнее для обработки разных случаев, но более эффективно, учитывая, что оно работает на уровне месяца, а не на уровне дня
Комментарии:
1. Похоже, что это очень близко. Я думаю, что это может сделать это. Спасибо.
Ответ №3:
Я думаю, вам нужно что-то вроде:
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 опубликовал ответ, пока я печатал свой ответ, и, хотя наши основные методы одинаковы, мой не использует аналитическую функцию для определения разницы в количестве дней. Вы можете найти или не найти их ответ более эффективным, чем мой — вы должны протестировать оба, чтобы увидеть, какой из них лучше всего работает с вашими данными.