Скользящее окно

[FS]

А есть ли какой-то удобный / предпочтительный способ получения агрегатов скользящим окном? Например, есть таблица:

date, idServer, idProcess

и необходимо получить на выходе отчет вида:

Дата, Сервер, Число уникальных процессов за 3 дня

Первое, что приходит в голову - сделать N запросов вида:

select date, idServer, uniq(idProcess) from log where date >= :date - 2 and date <= :date group by date, idServer

по запросу на каждый из дней входящих в запрос.

Второй вариант - использовать arrayJoin, что-то вроде:

select fakeDate, idServer, uniq(idProcess) from (select arrayJoin([date, date + 1, date + 2]) as fakeDate, idServer, idProcess from log where date >= :reportStart and date <= :reportEnd) group by fakeDt, idServer

Но возможно, есть более оптимальный способ?

[man...@gmail.com]

Напрямую поддержки скользящих окон нет*.

Первый вариант можно сделать оптимальнее. Округлить дату до трёх дней и сделать GROUP BY по округлённой дате.
Сделать три таких запроса со смещениями 0, 1, 2.

Как округлить дату. Привести её к UInt16, обернуть в функцию intDiv для целочисленного деления, домножить обратно, и привести к Date:

toDate(intDiv(toUInt16(date), 3) * 3)
toDate(intDiv(toUInt16(date + 1), 3) * 3 - 1)
toDate(intDiv(toUInt16(date + 2), 3) * 3 - 2)

Результаты можно объединить с помощью UNION ALL.

Запрос с arrayJoin тоже работает.
Вместо [date, date + 1, date + 2]
можно написать date + arrayJoin(range(3))
потому что range(3) вернёт массив [0, 1, 2].

Проверьте экспериментально, какой вариант лучше.


* По идее, здесь нужны "аналитические функции" из SQL 2003. Они не поддерживаются.
У нас есть некоторый задел, чтобы получить похожую функциональность в некоторых частных случаях.
Например, для одного заказчика мы реализовали функцию runningAccumulate (не документирована).
Она позволяет взять столбец с состояниями агрегатной функции и выполнить объединение этих состояний с накоплением.
Использование этой функции довольно ограничено...