Postgresql SQL GROUP BY временной интервал с произвольной точностью (до миллисекунд)

Question

Postgresql SQL GROUP BY временной интервал с произвольной точностью (до миллисекунд)

Мои данные измерений хранятся в следующей структуре:

CREATE TABLE measurements(
measured_at TIMESTAMPTZ,
val INTEGER
);

Я уже знаю, что с помощью

(А) date_trunc('hour',measured_at)

А ТАКЖЕ

(Б) generate_series

Я бы смог агрегировать свои данные:

microseconds,
milliseconds
.
.
.

Но можно ли агрегировать данные за 5 минут или, скажем, произвольное количество секунд? Можно ли агрегировать измеренные данные за произвольное кратное количество секунд?

Мне нужны данные, агрегированные по разным временным разрешениям, чтобы подать их в FFT или AR-модель, чтобы увидеть возможные сезонности.

56

postgresql group-by

Источник

user1612798 20 авг '12 в 21:40

10 ответов

Другие вопросы по тегам postgresql group-by

user562459 20 авг '12 в 22:59 2012-08-20 22:59 · Answer 1 · 2012-08-20 22:59

Вы можете сгенерировать таблицу "сегментов", добавив интервалы, созданные generate_series(). Этот оператор SQL сгенерирует таблицу пятиминутных интервалов за первый день (значение min(measured_at)) в ваших данных.

select 
  (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
  (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
from generate_series(0, (24*60), 5) n

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

with five_min_intervals as (
  select 
    (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
    (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
  from generate_series(0, (24*60), 5) n
)
select f.start_time, f.end_time, avg(m.val) avg_val 
from measurements m
right join five_min_intervals f 
        on m.measured_at >= f.start_time and m.measured_at < f.end_time
group by f.start_time, f.end_time
order by f.start_time

Группировка по произвольному количеству секунд аналогична - используйте date_trunc(),

Более общее использование generate_series () позволяет избежать угадывания верхнего предела для пятиминутных сегментов. На практике вы, вероятно, построите это как представление или функцию. Вы можете получить лучшую производительность из базовой таблицы.

select 
  (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
  (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
from generate_series(0, ((select max(measured_at)::date - min(measured_at)::date from measurements) + 1)*24*60, 5) n;

user1002025 23 фев '13 в 09:28 2013-02-23 09:28 · Answer 2 · 2013-02-23 09:28

У Catcall отличный ответ. Мой пример использования демонстрирует наличие фиксированных сегментов - в этом случае 30-минутные интервалы, начинающиеся в полночь. Это также показывает, что в первой версии Catcall может быть сгенерировано одно дополнительное ведро и как его устранить. Я хотел ровно 48 ведер в день. В моей задаче наблюдения имеют отдельные столбцы даты и времени, и я хочу усреднить наблюдения в течение 30-минутного периода в течение месяца для ряда различных служб.

with intervals as (
    select
        (n||' minutes')::interval as start_time, 
        ((n+30)|| ' minutes')::interval as end_time
    from generate_series(0, (23*60+30), 30) n
)
select i.start_time, o.service, avg(o.o)
from
observations o right join intervals i
on o.time >= i.start_time and o.time < i.end_time
where o.date between '2013-01-01' and '2013-01-31'
group by i.start_time, i.end_time, o.service
order by i.start_time

user849239 10 мар '15 в 03:48 2015-03-10 03:48 · Answer 3 · 2015-03-10 03:48

Как насчет

SELECT MIN(val), 
EXTRACT(epoch FROM measured_at) / EXTRACT(epoch FROM INTERVAL '5 min') AS int 
FROM measurements 
GROUP BY int

где '5 мин' может быть любым выражением, поддерживаемым INTERVAL

16

Источник

user849239 10 мар '15 в 03:48

user6464308 20 июл '21 в 17:53 2021-07-20 17:53 · Answer 4 · 2021-07-20 17:53

Начиная с PostgreSQL v14, вы можете использовать date_bin функция для этого:

      SELECT date_bin(
          INTERVAL '5 minutes',
          measured_at,
          TIMSTAMPTZ '2000-01-01'
       ),
       sum(val)
FROM measurements
GROUP BY 1;

12

Источник

user6464308 20 июл '21 в 17:53

user1576886 02 ноя '15 в 23:52 2015-11-02 23:52 · Answer 5 · 2015-11-02 23:52

Следующее даст вам ведра любого размера, даже если они не очень хорошо сочетаются с хорошей минутой / часом / любой границей. Значение "300" предназначено для 5-минутной группировки, но можно заменить любое значение:

select measured_at, 
       val, 
       (date_trunc('seconds', (measured_at - timestamptz 'epoch') / 300) * 300 + timestamptz 'epoch') as aligned_measured_at
from measurements;

Затем вы можете использовать любую нужную вам совокупность вокруг "val" и использовать "group by align_measured_at" по мере необходимости.

user359035 07 июн '16 в 16:20 2016-06-07 16:20 · Answer 6 · 2016-06-07 16:20

Это основано на ответе Майка Шерилла, за исключением того, что он использует интервалы меток времени вместо отдельных столбцов начала / конца.

with intervals as (
    select tstzrange(s, s + '5 minutes') das_interval
    from (select generate_series(min(lower(time_range)), max(upper(time_rage)), '5 minutes') s
          from your_table) x)
select das_interval, your_table.*
from   your_table
right join intervals on time_range && das_interval
order by das_interval;

user2100028 08 дек '14 в 18:48 2014-12-08 18:48 · Answer 7 · 2014-12-08 18:48

Я хотел посмотреть на данные за последние 24 часа и посчитать вещи с почасовым шагом. Я запустил решение Cat Recall, которое довольно изящно. Однако это связано с данными, а не только с тем, что произошло за последние 24 часа. Поэтому я провел рефакторинг и в итоге получил что-то довольно близкое к решению Джулиана, но с большим количеством CTE. Так что это своего рода брак из 2 ответов.

WITH interval_query AS (
    SELECT (ts ||' hour')::INTERVAL AS hour_interval
    FROM generate_series(0,23) AS ts
), time_series AS (
    SELECT date_trunc('hour', now()) + INTERVAL '60 min' * ROUND(date_part('minute', now()) / 60.0) - interval_query.hour_interval AS start_time
    FROM interval_query
), time_intervals AS (
    SELECT start_time, start_time + '1 hour'::INTERVAL AS end_time
    FROM time_series ORDER BY start_time
), reading_counts AS (
    SELECT f.start_time, f.end_time, br.minor, count(br.id) readings
    FROM beacon_readings br
    RIGHT JOIN time_intervals f
                    ON br.reading_timestamp >= f.start_time AND br.reading_timestamp < f.end_time AND br.major = 4
    GROUP BY f.start_time, f.end_time, br.minor
    ORDER BY f.start_time, br.minor
)
SELECT * FROM reading_counts

Обратите внимание, что любое дополнительное ограничение, которое я хотел в конечном запросе, должно быть сделано в RIGHT JOIN, Я не предполагаю, что это обязательно лучший (или даже хороший подход), но это то, с чем я работаю (по крайней мере, на данный момент) на панели инструментов.

user11858359 16 апр '20 в 22:50 2020-04-16 22:50 · Answer 8 · 2020-04-16 22:50

Расширение Timescale для PostgreSQL дает возможность группировать по произвольным временным интервалам. Функция называетсяtime_bucket() и имеет тот же синтаксис, что и date_trunc()функция, но в качестве первого параметра принимает интервал вместо точности времени. Здесь вы можете найти его документацию по API. Это пример:

SELECT
  time_bucket('5 minutes', observation_time) as bucket,
  device_id,
  avg(metric) as metric_avg,
  max(metric) - min(metric) as metric_spread
FROM
  device_readings
GROUP BY bucket, device_id;

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

user252556 31 авг '17 в 10:17 2017-08-31 10:17 · Answer 9 · 2017-08-31 10:17

Я взял обобщение всего вышеперечисленного, чтобы попытаться придумать что-то немного более простое в использовании;

create or replace function interval_generator(start_ts timestamp with TIME ZONE, end_ts timestamp with TIME ZONE, round_interval INTERVAL)
    returns TABLE(start_time timestamp with TIME ZONE, end_time timestamp with TIME ZONE) as $$
BEGIN
return query
        SELECT
            (n)       start_time,
            (n + round_interval) end_time
        FROM generate_series(date_trunc('minute', start_ts), end_ts, round_interval) n;
END
$$
    LANGUAGE 'plpgsql';

Эта функция является временной меткой абстракции ответа Майка, которая (IMO) делает вещи немного чище, особенно если вы генерируете запросы на стороне клиента.

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

with intervals as (select * from interval_generator(NOW() - INTERVAL '24 hours' , NOW(), '30 seconds'::INTERVAL))
select f.start_time, m.session_id, m.metric, min(m.value) min_val, avg(m.value) avg_val, max(m.value) max_val
from ts_combined as m
inner JOIN intervals f
    on m.time >= f.start_time and m.time < f.end_time
GROUP BY f.start_time, f.end_time, m.metric, m.session_id
ORDER BY f.start_time desc

(Также для моих целей я добавил еще несколько полей агрегации)

user95382 20 авг '12 в 21:45 2012-08-20 21:45 · Answer 10 · 2012-08-20 21:45

Возможно, вы можете extract(epoch from measured_at) и что из этого выйдет?

2

Источник

user95382 20 авг '12 в 21:45