Как в posgresql получить среднее в разных интервалах?
Добрый день.
Имеется следующая таблица
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 |
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; |
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 минуты. Есть ли способ избежать столь громоздкой конструкции? И сделать запрос оптимизированым? Результаты поиска не дали понятных ответов.
Дополнительно:
Ответы:
По интервалам в 1 год/месяц/день/час/минуту:
select date_trunc('hour', e.created), avg(e.value) from table e group by 1 order by 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-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) - следующий день ... |
[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 |
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
не особо проще вашего, как видите
- Добрый день.
Да про некоторые вопросы я и не задумывался.
Интервал может быть любой, но всегда старт соответсует текущему времени (то есть это не обязательно начало дня), то есть произвольное время.
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для того чтобы получить среднее значение в разных интервалах в PostgreSQL, вы можете воспользоваться функцией `CASE` в сочетании с агрегатной функцией `AVG`.
Прежде всего, вам необходимо определить интервалы, по которым вы хотите вычислить среднее значение. Для этого вы можете использовать конструкцию `CASE`, которая позволит вам разделить данные на группы в зависимости от их значения.
Пример запроса:
SELECT CASE WHEN column_name 100 AND column_name <= 200 THEN '101-200' ELSE '201+' END AS interval, AVG(column_name) AS average_value FROM table_name GROUP BY interval;
В данном примере мы разделили данные на три интервала: 0-100, 101-200 и 201+. Вы можете изменить условия в конструкции `CASE` в соответствии с вашими требованиями.
После этого мы используем функцию `AVG` для вычисления среднего значения в каждом из интервалов. Затем с помощью `GROUP BY` мы группируем результаты по интервалам.
Таким образом, вы сможете получить среднее значение в различных интервалах в PostgreSQL, используя конструкцию `CASE` и агрегатную функцию `AVG`.