Блог

Мне нужно суммировать значения по диапазону дат в нескольких столбцах. Каждый диапазон дат равен одной неделе месяца. Это может быть короче 7 дней, если это начало месяца или конец месяца. Например, у меня есть даты для февраля:

 my_user        my_date        my_value
A              01.02.2019     100
A              02.02.2019     150
B              01.02.2019     90
Z              28.02.2019     120
  

Как я могу получить формат диапазона дат, такой как показано ниже?

 my_user   01/02-03/02   04/02-10/02   11/02-17/02  18/02-24/02  25/02-28/02
A              250      0             0            0            0 
B              90       0             0            0            0 
Z              0        0             0            0            120 
  

Есть предложения? Спасибо!

Вы можете сделать это:

 select *
  from (
    select to_char(dt, 'iw') - to_char(trunc(dt, 'month'), 'iw')   1 wk, usr, val from t)
  pivot (sum(val) for wk in (1, 2, 3, 4, 5, 6))
  

^{ДЕМОНСТРАЦИЯ}

 USR          1          2          3          4          5          6
--- ---------- ---------- ---------- ---------- ---------- ----------
A          250                                             
B                                            90            
Z                                                      120
  

Номера заголовков — это недели месяца. Максимум может быть 6, если месяц начинается в конце недели и длится более 28 дней.

Аналогичным образом вы можете найти первый и последний день каждой недели, если это необходимо, но вы не можете поместить их в заголовки, или, по крайней мере, нелегко.

Редактировать:

можно определить определенный диапазон дат с помощью pivot, просто как две даты? Например, мне нужно суммировать значения с 5 декабря 2018 года по 4 января 2019 года, с 5 января 2019 года по 4 февраля 2019 года, с 5 марта 2019 года по 4 апреля 2019 года

ДА. Все зависит от того, как мы считаем первую и следующие недели. Здесь:

 to_char(dt, 'iw') - to_char(trunc(dt, 'month'), 'iw')   1
  

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

 (to_char(dt, 'iw') - to_char(date '2019-03-05', 'iw'))   1
  

или

 (to_char(dt, 'iw') - to_char((select min(dt) from data), 'iw'))   1
  

Правка 2:

Однако есть одна проблема. Когда определенный пользователем период содержит два или более лет. to_date(..., 'iw') работает нормально в течение одного года, но для двух мы получаем значения 51, 52, 01, 02… Мы должны как-то справиться с этим, например, как здесь:

 with t(dt1, dt2) as (select date '2018-12-16', date '2019-01-15' from dual)
select min(dt) mnd, max(dt) mxd, iw, row_number() over (order by min(dt)) rn
  from (select dt1   level - 1 dt, to_char(dt1   level - 1, 'iw') iw
          from t connect by level -1 <= dt2 - dt1)
  group by iw
  

что дает нам:

 MND         MXD         IW         RN
----------- ----------- -- ----------
2018-12-16  2018-12-16  50          1
2018-12-17  2018-12-23  51          2
2018-12-24  2018-12-30  52          3
2018-12-31  2019-01-06  01          4
2019-01-07  2019-01-13  02          5
2019-01-14  2019-01-15  03          6
  

В первой строке у нас есть определенные пользователем диапазоны дат. Затем я выполнил иерархический запрос, перебрав все даты в присвоении диапазона week , затем сгруппировал по этой неделе, нашел даты начала и окончания для каждой недели и присвоил номер строки rn , который в дальнейшем может использоваться pivot.

Теперь вы можете просто объединить свои входные данные с этим запросом, давайте назовем его weeks :

 from data join weeks on dt between mnd and mxd
  

и сделать сводку. Но для более длительных периодов вам нужно определить, сколько недель может быть, и указать их в предложении pivot in (1, 2, 3, 4...) . Вы также можете добавить псевдонимы, если вам нужно:

 pivot ... for rn in (1 week01, 2 week02... 12 week12)
  

Нет простого способа избежать перечисления их вручную. Если вам это нужно, пожалуйста, поищите oracle dynamic pivot в SO, там уже есть сотни похожих вопросов. 😉