Запрос агрегированных данных с заданным временем выборки

Предположим, мои необработанные данные:

Timestamp   High Low Volume
10:24.22345 100   99  10
10:24.23345 110  97   20
10:24.33455 97    89  40
10:25.33455 60    40  50
10:25.93455 40    20  60

При шаге расчета 1 секунда выходные данные должны быть следующими (добавить дополнительный столбец):

Timestamp   High Low Volume Count
10:24       110   89  70     3
10:25       60    20  110    2

Единица выборки варьируется от 1 second, 5 sec, 1 minute, 1 hour, 1 day, ...

Как быстро запросить выборочные данные в базе данных PostgreSQL с помощью Rails?

Я хочу заполнить весь интервал, получив ошибку

ERROR:  JOIN/USING types bigint and timestamp without time zone cannot be matched

SQL

SELECT 
       t.high,
       t.low
FROM 
(

  SELECT generate_series(
    date_trunc('second', min(ticktime)) ,
    date_trunc('second', max(ticktime)) ,
    interval '1 sec'
  ) FROM czces  AS g (time)

  LEFT JOIN
  (
    SELECT 
      date_trunc('second', ticktime) AS time ,
      max(last_price) OVER w AS high ,
      min(last_price) OVER w AS low 
   FROM czces
   WHERE product_type ='TA' AND contract_month = '2014-08-01 00:00:00'::TIMESTAMP 
     WINDOW w AS (
      PARTITION BY date_trunc('second', ticktime)
      ORDER BY ticktime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
      )
  ) t USING (time)



  ORDER BY 1 
) AS t ;

postgresql ruby-on-rails sql generate-series financial
person newBike    schedule 08.12.2014    source источник
comment
Вы хотите ровно одну строку на точку выборки? Или нет строки, если данных нет вообще? И ваш "Timestamp" не является действительным timestamp, а просто time. Это предназначено?   -  person Erwin Brandstetter    schedule 08.12.2014

Ответы (1)


arrow_upward
1
arrow_downward

Просто используйте date_trunc() перед агрегированием. Работает для основных единиц времени 1 секунда, 1 минута, 1 час, 1 день, но не для 5 секунд. Произвольные интервалы немного сложнее, см. ссылку ниже!

SELECT date_trunc('second', timestamp) AS timestamp -- or minute ...
     , max(high) AS high, min(low) AS low, sum(volume) AS vol, count(*) AS ct
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

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

SELECT g.timestamp, t.high, t.low, t.volume, t.ct
FROM  (SELECT generate_series(date_trunc('second', min(timestamp))
                             ,date_trunc('second', max(timestamp))
                             ,interval '1 sec') AS g (timestamp) -- or minute ...
LEFT JOIN  (
   SELECT date_trunc('second', timestamp) AS timestamp           -- or minute ...
        , max(high) AS high, min(low) AS low, sum(volume) AS vol, count(*) AS ct
   FROM   tbl
   GROUP  BY 1
   ) t USING (timestamp)
ORDER  BY 1;

LEFT JOIN имеет важное значение.

Для произвольных интервалов:

Примечание: не используйте timestamp в качестве имени столбца. Это базовое имя типа и зарезервированное слово в стандартном SQL. Это также вводит в заблуждение для данных, которые на самом деле не являются отметкой времени.

person Erwin Brandstetter    schedule 08.12.2014
comment
Простите, а что если я хочу дополнительно получить первую запись и последнюю запись в каждой группе. - person newBike; 10.12.2014
comment
@poc: Пожалуйста, задайте новый вопрос как новый вопрос. Вы всегда можете сослаться на это для контекста. - person Erwin Brandstetter; 10.12.2014