每x行数选择最大值和最小值-postgresql [英] select max and min values every x amount of rows-postgresql
问题描述
我正在尝试从tick数据开始在PostgreSQL中创建OHLC条。我想每1000个滴答声或每500个滴答声创建一个小节。或每X笔价格变动。
I'm trying to create OHLC bars in postgresql starting from tick data. I want to create bars every 1000 ticks or every 500 ticks. Or every X amount of ticks.
我要保存的数据库具有买价/卖价和时间戳。我知道我可以进行分组并按时间戳进行分组，但是所需的输出使用的是刻度的数量。
The database I'm saving has the bid/ask and a timestamp. I know that I could do a groupby and group them by timestamp, but the desired output is using the amount of ticks.
一个刻度由时间戳（即出价）组成价格和要价。
One tick is composed by a timestamp, a bid price and an ask price.
报价数据库看起来像这样：
The tick database looks something like this:
-------------------------------------------------
| date | bid | ask |
|2020-03-20 19:33:56.044533 | 1.06372 | 1.06384 |
|2020-03-20 19:33:37.205241 | 1.06372 | 1.06384 |
|2020-03-20 19:33:54.943593 | 1.06372 | 1.06383 |
|2020-03-20 19:33:55.183255 | 1.06372 | 1.06384 |
我想对每X个刻度线进行分组以创建此输出：
I would like to group every X amounts of ticks to create this output:
---------------------------------------------------------------------------
| date | open | high | low | close |
|2020-03-20 19:33:56.044533 | 1.06372 | 1.07104 | 1.06001 | 1.06579 |
那是1支蜡烛。这些数字来自出价列。开盘价是记录的第一个价格，收盘价是记录的最后一个价格，最高价和最低价是这X个滴答中记录的最高价和最低价。
That is 1 candle. The numbers came from the bid column. The open price is the first price registered, the close price is the last price registered and the high and low are the max and min prices registered in those X ticks.
所以，如果X为1000，并假设索引从0开始，那么OHLC价格如下：
-开盘：索引0处的价格
-高：索引0和999 $ b $之间的最高价格b-低点：索引0和999之间的最低价格
-收盘：索引999处的价格
So, if X is 1000 and assuming that the index starts from 0, the OHLC prices would be as follows: - open: price at index 0 - high: max price between index 0 and 999 - low : min price between index 0 and 999 - close: price at index 999
前1000个刻度。然后在接下来的1000个滴答声中创建下一个蜡烛。
-开盘：指数1000的价格
-高：指数1000和1999之间的最大价格
-低：指数1000和1999之间的最小价格
-收盘：指数1999
That is for the first 1000 ticks. Then the next candles is created by the next following 1000 ticks. - open: price at index 1000 - high: max price between index 1000 and 1999 - low : min price between index 1000 and 1999 - close: price at index 1999
我该怎么实现？
谢谢！
推荐答案
您可以使用 row_number（）和算术运算来汇总固定行数：
You can aggregate fixed numbers of rows using row_number() and arithmetic:
select min(date),
(array_agg(bid order by seqnum asc))[1] as open,
(array_agg(bid order by seqnum desc))[1] as close,
min(bid) as min_bid, max(bid) as max_bid
from (select t.*, row_number() over (order by date) as seqnum
from ticks t
) t
group by floor((seqnum - 1) / 500);
此操作使用 hack来打开 和 close -通过使用数组。
This uses "hack" to get the open and close -- by using arrays.
这篇关于每x行数选择最大值和最小值-postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
