sql - 查询 PostgreSQL 的 Open-High-Low-Close (OHLC) 报告

Question

我正在尝试查询 PostgreSQL 8.4.2 服务器中的表以获取开高低收数据。该表和我的第一个查询如下。

问题:有没有办法在不使用子查询的情况下获得与下面示例查询相同的结果？也许使用 FIRST_VALUE() 或 LAST_VALUE() 窗口方法？

-- FIRST ATTEMPT AT OHLC
SELECT
  contract_id
, TO_CHAR(ts, 'YYMMDDHH24MI')
, (SELECT price FROM fill minF WHERE minF.fill_id = MIN(f.fill_id)) AS open
, MAX(f.price) AS high
, MIN(f.price) AS low
, (SELECT price FROM fill maxF WHERE maxF.fill_id = MAX(f.fill_id)) AS close
FROM fill f
GROUP BY 1,2
ORDER BY 1,2;

-- SIMPLIFIED DDL
CREATE TABLE fill
(
    contract_id SEQUENCE PRIMARY KEY
,   ts          TIMESTAMP
,   price       NUMERIC(10,4)
);

Answer 1

您已将您的分组精确到分钟。我假设这是错误的，因为这些通常是在白天完成的。如果我错了，你必须改回来。

SELECT DISTINCT contract_id, ts::date,
  min(price) OVER w,
  max(price) OVER w,
 first_value(price) OVER w, 
 last_value(price) OVER w
FROM fill
WINDOW w AS (PARTITION BY contract_id, ts::date ORDER BY ts)
ORDER BY 1,2