Получение агрегатов за произвольные интервалы времени

Это запрос, который у меня есть до сих пор, для создания ежедневных баров:

SELECT DISTINCT date_trunc('hour',t) AS date,
min(price) OVER w,
max(price) OVER w,
first_value(price) OVER w,
last_value(price) OVER w
FROM ticker
WINDOW w AS (PARTITION BY date_trunc('hour',t));

Изменение «часа» на «мин» или «день» дало бы мне столбцы, соответствующие этим единицам.

Однако что, если мне нужны 5-минутные или 15-минутные бары? date_trunc() не поддерживает их, и я ищу хороший элегантный способ сделать это.


postgresql sql generate-series
person user1533456    schedule 18.07.2012    source источник

Ответы (1)


arrow_upward
7
arrow_downward

Для 15-минутных интервалов, основываясь на вашем примере:

SELECT DISTINCT
     , date_trunc('hour', t) AS h
     , floor(EXTRACT(minute FROM t) / 15) AS m15
     , min(price) OVER w
     , max(price) OVER w
     , first_value(price) OVER w
     , last_value(price) OVER w
FROM   ticker
WINDOW w AS (PARTITION BY date_trunc('hour', t)
                        , floor(extract(minute FROM t) / 15));

Работает тоже 5 минут.

Более общее решение для любых регулярных интервалов времени в любой период времени:

WITH x AS (
    SELECT t1, t1 + interval '5min' AS t2
    FROM   generate_series(timestamp '2012-07-18 00:00'
                         , timestamp '2012-07-18 23:55'
                         , interval '5 min') AS t1
    )
SELECT DISTINCT ON (1)
       x.t1
     , min(price)         OVER w
     , max(price)         OVER w
     , first_value(price) OVER w
     , last_value(price)  OVER w
FROM   x
JOIN   ticker y ON y.t >= x.t1  -- use LEFT JOIN to include empty intervals
               AND y.t <  x.t2  -- don't use BETWEEN
WINDOW w AS (PARTITION BY x.t1)
ORDER  BY x.t1;

Связанные ответы с дополнительным объяснением:

person Erwin Brandstetter    schedule 18.07.2012
comment
Привет, что означают x и y? - person newBike; 10.12.2014
comment
@poc: x — это имя CTE, y — это псевдоним таблицы для ticker, объявленный здесь: JOIN ticker y, что является сокращением от JOIN ticker AS y. - person Erwin Brandstetter; 10.12.2014