sql - 检索任意时间间隔的聚合

Question

这是我到目前为止的查询，用于创建每日柱状图:

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() 不支持这些，我正在寻找一种优雅的方式来实现它。

Answer 1

Postgres 14 或更新版本:

Postgres 14 添加了函数 date_bin() .
现在这适用于任何 间隔。 15 分钟示例:

SELECT DISTINCT ON (1)
       date_bin('15 min', t, timestamp '2012-07-18 00:00') AS bin
     , min(price)        OVER w  AS min_prize
     , max(price)        OVER w  AS max_prize
     , price                     AS first_price
     , last_value(price) OVER w  AS last_price
FROM   ticker
WINDOW w AS (PARTITION BY 1 ORDER BY t)
ORDER  BY 1, t;

fiddle

要包含空箱(没有匹配的行)，您仍然需要LEFT JOIN 到如下网格。

Postgres 13 或更早版本

任意分钟数。 15 分钟示例:

SELECT DISTINCT ON (1, 2)
       date_trunc('hour', t) AS hour
     , floor(EXTRACT(minute FROM t) / 15) AS quarter
     , min(price) OVER w AS min_prize
     , max(price) OVER w AS max_prize
     , price AS first_price
     , last_value(price) OVER w AS last_price
FROM   ticker
WINDOW w AS (PARTITION BY 1, 2 ORDER BY t)
ORDER  BY 1, 2, t;

---

一个更通用的解决方案，适用于任何固定时间间隔、跨越任何时间段，包括空箱(无匹配行):

SELECT DISTINCT ON (grid.bin)
       grid.bin
     , min(price)        OVER w  AS min_prize
     , max(price)        OVER w  AS max_prize
     , price                     AS first_prize
     , last_value(price) OVER w  AS last_prize
FROM   generate_series(timestamp '2012-07-18 00:00'  -- your time range here
                     , timestamp '2012-07-18 01:15'
                     , interval  '5 min') grid(bin)
LEFT   JOIN ticker t ON t.t >= grid.bin                     -- use JOIN to exclude empty intervals
                    AND t.t <  grid.bin + interval '5 min'  -- don't use BETWEEN
WINDOW w AS (PARTITION BY grid.bin ORDER BY t)
ORDER  BY grid.bin, t.t;

相关:

• Best way to count rows by arbitrary time intervals
• Select first row in each GROUP BY group?