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

Ответ 1

Вы можете создать таблицу "ведер", добавив интервалы, созданные 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;

Ответ 2

У 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

Ответ 3

Ниже приведены ведра любого размера, даже если они не хорошо сочетаются с хорошей минутой/часом/любой границей. Значение "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 aligned_measured_at" по мере необходимости.

Ответ 4

Как насчет

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

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

Ответ 5

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

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. Я не предполагаю, что это обязательно лучший (или даже хороший подход), но это то, с чем я работаю (по крайней мере в данный момент) на панели инструментов.

Ответ 6

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

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_tabe.*
from   your_table
right join intervals on time_range && das_interval
order by das_interval;

Ответ 7

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

Ответ 8

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

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

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