Как в posgresql получить среднее в разных интервалах?

Question

[tolka12300]

Добрый день.

Имеется следующая таблица

id | created             | value
1  | 2021-01-01 10:00:00 | 1
2  | 2021-01-01 10:01:00 | 10
3  | 2021-01-01 10:02:00 | 33
4  | 2021-01-01 10:03:00 | 3 
....
N  | 2021-09-01 13:13:00 | 40

где за несколько лет указано количество в минуту. (Данные кое-где пропускаются по разным причинам).

Я хочу получить среднее значение в разных интервалах - от 10 минут до месяцев и лет.
Пока что я написал следующую функцию:

select to_created(CONCAT( 
  extract('year' from e."created"), '-',
  extract('month' from e."created"), '-', 
  extract('day' from e."created"), '-', ' ',
  extract('hour' from e."created"), ':',
  FLOOR(extract('minute' from e."created") / 3) * 3, ':',
  extract('second' from e."created")
 ), 'YYYY-MM-DD hh24:mi:ss') as tmp, AVG(e.value) avg_v from table e group by tmp order by tmp asc;

здесь например получаю по интервалам в 3 минуты. Есть ли способ избежать столь громоздкой конструкции? И сделать запрос оптимизированым? Результаты поиска не дали понятных ответов.

Answer 1

[galaxy]

По интервалам в 1 год/месяц/день/час/минуту:

select date_trunc('hour', e.created), avg(e.value) from table e group by 1 order by 1

(вместо hour - year/month/day ...)

Но вообще задача у вас не совсем четко определена: интервал начинается в начале соответствующего календарного периода или необязательно? Т.е. если группируем по дням, дни идут с 0:00:00 по 23:59:59 или, скажем, взяли произвольный стартовый момент (пусть 2019-08-03 14:25:44) и от него считаем (т.е. дни с 14:25:44 по 14:25:43 следующего дня)?
Это еще более значимо для случая периодов типа 7 часов, или 23 минуты. Например, считая с начала какого-то дня периоды по 7 часов могут идти так:

[00:00-07:00)
[07:00-14:00)
[14:00-21:00)
[21:00-00:00) - следующий день
[00:00-07:00) - следующий день

или так:

[00:00-07:00)
[07:00-14:00)
[14:00-21:00)
[21:00-03:00) - следующий день
[03:00-10:00) - следующий день
...

для первого случая как-то так:

select date_trunc('day', e.created) + (trunc(extract('hour' from e.created) / 7) * 7 || ' hour')::interval, avg(e.value) from table e group by 1 order by 1

не особо проще вашего, как видите

Comments

[tolka12300]

Добрый день.
Да про некоторые вопросы я и не задумывался.
Интервал может быть любой, но всегда старт соответсует текущему времени (то есть это не обязательно начало дня), то есть произвольное время.