Как сделать агрегацию с окном «внахлест»?

Question

[Игорь Колонтарь]

Имеется набор данных. Упрощенно - это определенная дата и значение на эту дату.
Не могу сообразить, каким образом рассчитать агрегацию данных для каждой даты таким образом, чтобы группировка была не только по этой дате, но и по дополнительным датам -14 дней. Т.е. получается некая группировка с условием, которое эту партицию расширяет. При этом эти окна могут идти внахлест, т.е. одно и то же значение может участвовать при расчете агрегации на разные даты.
См. иллюстрацию, надеюсь так будет понятнее.
Посоветуйте пожалуйста, как сделать группировку или count over partition by по таким окнам? Или где про это почитать.

Comments

[Константин Цветков]

Укажите тег вашей СУБД.

[Freeman]

Константин Цветков, оконные выражения (windowing clause) — это стандарт SQL:99 и SQL:03.

Answer 1

[Freeman]

Если поддерживается вашей СУБД, нужно использовать rows between (беру по вашему примеру):

count() over (partition by date rows between date and date - 14)

Могу ошибаться. Давно не использовал SQL на таком уровне, детали уже подзабыл...

Comments

[Игорь Колонтарь]

Спасибо! Ваш совет направил в верную сторону.
В итоге я сделал следующим образом для агрегации по одному полю (как в вопросе):

SELECT DISTINCT
 
date,
sum(case when check = 1 then 1 else 0 end)
over (
order by date
RANGE BETWEEN
  interval '14' day preceding
  and
  current row
) as overlap
 
from TABLE;

В реальной задаче мне потребовалась группировка по партиции из нескольких полей (с учетом даты Т-14), сделал так:

select distinct
   
    COLUMN_1,
    COLUMN_2,
    ....
    COLUMN_N
    date_report,
   
    
    nvl(
        sum(
            case
              when MARKER = 1 then 1
            end
            ) over (
                    partition by
                    COLUMN_1,
                    COLUMN_2,
                    ....,
                    COLUMN_N
                    date
                    order by
                    date                                                          
                    range between
                    interval '14' day preceding
                    and
                    current row
                    )
        ,0) as NEW_COLUMN

Агрегацию делаем по партиции, сортировку только по дате.
Это сработало для меня.

Answer 2

[Константин Цветков]

Посоветуйте пожалуйста, как сделать группировку

Никак. Вы не указали по каким критериям выделены группы (картинки SQL не понимает). Сумма по группе подразумевает, что записи сгруппированы. Если нужно участие одной записи в нескольких суммах, делайте подзапрос.

попадаются строки не только этой даты, но и несколько ранних дат

Для каждой уникальной даты сделайте отдельный подзапрос.

SELECT DISTINCT [Table].[Date], 
  (SELECT Count(*) FROM [Table] AS [ts] 
      WHERE ts.[Date] BETWEEN [Table].[Date] AND DATEADD(d, -14, [Table].[Date]) 
            AND ts.[Check] = 1) AS CountCheck 
  FROM [Table]

Comments

[Игорь Колонтарь]

Спасибо за неравнодушие. Картинкой я хочу передать желаемый результат. А проблема в том, каким образом в group by или partition by поместить условие отбора, где полем группировки является дата, но при этом туда попадаются строки не только этой даты, но и несколько ранних дат.
Что-то вроде count over (partition by date where date between date and (date - 14))).

Answer 3

[Артем Черепахин]

Если я правильно понял задачу, то есть в SQL оконные функции LAG и LEAD, погуглите их.

Comments

[Игорь Колонтарь]

Спасибо, изучу и отпишу о результате.