给定时间/间隔以计算每个分组数据中的开/高/低/收盘价 [英] Given time/interval to calculate open/high/low/close value in each grouped data

问题描述

假设原始数据为：

  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秒，输出数据应如下（它们按秒分组）：

With a sample time of 1 second, the output data should be as following (they are grouped by second):

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

• 开盘价表示价格组中最早的数据


• 关闭表示组中最新数据的价格


• 卷表示组中的总和（卷）


• Open means the price of the earliest data in the group
• Close means the price of the lastest data in the group
• Volume means the sum(Volume) in the group

采样单位的变化范围为 1秒，5秒，1分钟，1小时，1天，...

The sampling unit from varying from 1 second, 5 sec, 1 minute, 1 hour, 1 day, ...

现在我可以通过以下方式获得高，低，音量以下SQL：

Now I can get the High, Low, Volume by the following 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

但是如何打开 ，

But how to get the open, close value in each group based on the above query?

推荐答案

您可以使用窗口功能与 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;

自定义窗口框架的解释：

Explanation for the custom window frame:



• 如何在窗口函数中使用环形数据结构


• PostgreSQL窗口功能：通过比较进行分区


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

直接关闭：



• 在每个GROUP BY组中选择第一行？


• Select first row in each GROUP BY group?

这篇关于给定时间/间隔以计算每个分组数据中的开/高/低/收盘价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！