查询PostgreSQL的开-高-低-关闭(OHLC)报告 [英] Querying PostgreSQL for Open-High-Low-Close (OHLC) report

问题描述

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

I am trying to query a table in PostgreSQL 8.4.2 server for to get open-high-low-close data. The table and my first query are below.

问题:有没有一种方法可以像下面的示例查询中那样使用子查询来获得相同的结果?也许使用FIRST_VALUE()或LAST_VALUE()窗口方法?

Question: Is there a way to get the same results without using subqueries as in the example query below? Perhaps using FIRST_VALUE() or LAST_VALUE() window methods?

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

推荐答案

我想获得次日解决方案.这似乎运作良好.

I would like to get sub-day resolution. This appears to work well.

SELECT
  contract_id
, the_minute
, open
, high
, low
, close
FROM 
(
  SELECT
    contract_id
  , TO_CHAR(ts, 'YYMMDDHH24MI') AS the_minute
  , MIN(price) OVER w            AS low
  , MAX(price) OVER w            AS high
  , LAST_VALUE(price) OVER w     AS open   -- Note the window is in reverse (first value comes last)
  , FIRST_VALUE(price) OVER w    AS close  -- Note the window is in reverse (last value comes first)
  , RANK() OVER w                AS the_rank
  FROM fill
  WINDOW w AS (PARTITION BY contract_id, TO_CHAR(ts, 'YYMMDDHH24MI') ORDER BY fill_id DESC)
) AS inr
WHERE the_rank = 1
ORDER BY 1, 2;

谢谢你，斯科特.您的回答帮助我获得了以下解决方案.

Thank you, Scott. You answer helped me get to the following solution.

这篇关于查询PostgreSQL的开-高-低-关闭(OHLC)报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！