以任意时间间隔检索聚合 [英] Retrieve aggregates for arbitrary time intervals

问题描述

这是我到目前为止创建每日酒吧的查询：

This is the query I have so far, to create daily bars:

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));

将小时更改为分钟或天会给我对应于这些单位的条形。

Changing 'hour' to 'min' or 'day' would give me the bars corresponding to these units.

但是，如果我要5分钟或15分钟吧？ date_trunc（）不支持这些，我正在寻找一种优雅的方法。

However, what if I want 5 min or 15 min bars? date_trunc() doesn't support these and I'm looking for a nice elegant way to do it.

推荐答案

以15分钟为间隔，以您的示例为基础：

For 15-minute intervals, building on your example:

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分钟。

在任何时间段内，在任何固定时间间隔内提供更多的通用解决方案：

A more generic solution for any regular time intervals, across any period of time:

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;

更多答案的相关答案：

• 在Rails + Postgres中按任意时间间隔对记录进行计数的最佳方法


• 在每个GROUP BY组中选择第一行？

• Best way to count records by arbitrary time intervals in Rails+Postgres
• Select first row in each GROUP BY group?

这篇关于以任意时间间隔检索聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！