检索任意时间间隔的聚合

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

将"hour"更改为"min"或"day"会得到与这些单位对应的条形图。

但是，如果我想要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;

小提琴

要包含空的bin（没有匹配的行），您仍然需要将D_3 LEFT JOIN添加到如下网格中。

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;

相关：

• 按任意时间间隔计数行的最佳方式
• 选择每个GROUP BY组中的第一行