这是查询我到目前为止,创建每日吧:
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()
不支持这些,我期待一个不错的优雅的方式来做到这一点。
对于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;
相关答案与更多的解释:
- 最好的办法,以计算任意时间间隔记录的Rails + Postgres的
- 选择由组中每组第一排?