sql - 给定时间/间隔以计算每个分组数据中的开盘价/最高价/最低价/收盘价

Question

假设原始数据是:

  Timestamp   High Low Volume
  10:24.22345 100   99  10
  10:24.23345 110   97  20
  10:24.33455 97    89  40
  10:25.33455 60    40  50
  10:25.93455 40    20  60

采样时间为1秒，输出数据如下(按秒分组):

  Timestamp   Open Close High  Low Volume 
  10:24         82   83   110   89  70     
  10:25         50   40   60    20  110    

• Open表示组内最早数据的价格
• Close表示组内最新数据的价格
• Volume 表示组内总和(Volume)

采样单位从 1 秒、5 秒、1 分钟、1 小时、1 天...

现在我可以通过以下 SQL 获取高、低、成交量:

SELECT date_trunc(\'#{interval}\', ticktime) AS ticktime_stamp,
       max(bid_price) as high,
       min(bid_price) as low,
       sum(bid_volume) as volume,
       max(product_type) as product_type
FROM   czces
WHERE  ticktime >=  \'#{begin_time}\'::timestamp
AND  ticktime <  \'#{end_time}\'::timestamp
AND  product_type =\'#{product_type}\'
GROUP  BY 1
ORDER  BY ticktime_stamp ASC

但是如何根据上述查询得到各组中的open,close值呢？

Answer 1

你可以使用 window functions与 DISTINCT ON 相结合:

SELECT DISTINCT ON (1)
       date_trunc('#{interval}', ticktime) AS ticktime_stamp
     , max(bid_price)         OVER w AS high
     , min(bid_price)         OVER w AS low
     , sum(bid_volume)        OVER w AS volume
     , max(product_type)      OVER w AS product_type
     , min(product_type)      OVER w AS product_type
     , first_value(bid_price) OVER w AS open
     , last_value(bid_price)  OVER w AS close
FROM   czces
WHERE  ticktime >= '#{begin_time}'::timestamp
AND    ticktime <  '#{end_time}'::timestamp
AND    product_type ='#{product_type}'
WINDOW w AS (PARTITION BY date_trunc('#{interval}', ticktime) ORDER BY ticktime
             ROWS BETWEEN UNBOUNDED PRECEDING
                      AND UNBOUNDED FOLLOWING)
ORDER  BY 1;

自定义窗框说明:

• How to use a ring data structure in window functions
• PostgreSQL window function: partition by comparison

DISTINCT ON 的解释:

• Select first row in each GROUP BY group?