英文:
PostgreSQL resample 1 minute OHLCV candle data into 5 minute OHLCV candle data
问题
我有一个包含以下列的postgreSQL数据库：
timestamp <bigint> - 以毫秒为单位的Unix时间戳
open <float> - 第一个交易价格
high <float> - 最高交易价格
low <float> - 最低交易价格
close <float> - 最后的交易价格
volume <float> - 在该时间段内交易的总成交量
有时我会查询跨越30+天（43.2k行）的数据。这些查询需要很长时间，因此我想每当我有超过30天的数据时，我将获取5分钟的蜡烛图，这将减少行数约5倍。
基本上，我正在寻找一个执行以下操作的函数（伪代码）：
SELECT
first_value(timestamp),
first_value(open),
max(high),
min(low),
last_value(close),
sum(volume)
WHERE timestamp > {过去某个时间戳} AND timestamp < {当前时间}
GROUP BY floor((timestamp / 1000) / (60*5)) * 5
ORDER BY timestamp
非常感谢任何帮助，已经卡在这个问题上有一段时间了
英文:
I have a postgreSQL database with the following columns:
timestamp <bigint> - Unix timestamp in milliseconds
open <float> - The first traded price
high <float> - The highest traded price
low <float> - THe lowest traded price
close <float> - The last traded price
volume <float> - The total volumed transacted during the time period
Sometimes I'll query data that spans 30+ days (43.2k rows). These queries take a really long time, so I thought whenever I have >30 days of data I'll fetch 5 minute candles instead which would cut down the row count by 5x.
Basically I'm looking for a function that does this (pseudocode):
SELECT
first_value(timestamp),
first_value(open),
max(high),
min(low),
last_value(close),
sum(volume)
WHERE timestamp > {some timestamp in the past} AND timestamp < {current time}
GROUP BY floor((timestamp / 1000) / (60*5)) * 5
ORDER BY timestamp
Any help would be greatly appreciated, been stuck on this one for a while
答案1
得分: 2
使用了一个子查询来获取列：minute5。在它上面进行了分组和排序。要获取第一个值，使用array_agg进行排序，并通过方括号取值。对于'close'也是同样的方式，但是方向相反。
以下是代码的翻译：
SELECT
min(timestamp) AS 时间戳,
(array_agg(open ORDER BY timestamp))[1] AS 开盘价,
max(high) AS 最高价,
min(low) AS 最低价,
(array_agg(close ORDER BY timestamp DESC))[1] AS 收盘价,
sum(volume) 成交量
FROM
(SELECT *, floor((timestamp/1000) / (60*5)) * 5 AS minute5
FROM stock
ORDER BY timestamp
) t
GROUP BY
minute5
ORDER BY
minute5
;
这是链接，创建表格并获取数据（添加了一个dt列，用于可视化查看每行的日期和时间）。
英文:
Used a subquery to get the column: minute5. On it made grouping and sorting. To get the first value, 'open' use array_agg with sorting and took the value through square brackets. The same with 'close', but in the other direction.
SELECT
min(timestamp) AS timestamp,
(array_agg(open ORDER BY timestamp))[1] AS open,
max(high) AS high,
min(low) AS low,
(array_agg(close ORDER BY timestamp DESC))[1] AS close,
sum(volume) volume
FROM
(SELECT*, floor((timestamp/1000) / (60*5)) * 5 AS minute5
FROM stock
ORDER BY timestamp
) t
GROUP BY
minute5
ORDER BY
minute5
;
Here's the link: creating a table and getting data (added a dt column with a type to visually see what date and time for each row).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论