使用 SQL 将报价数据转换为烛台 (OHLC) [英] Convert tick data to candlestick (OHLC) with SQL

问题描述

我有一个带列的表格:

timeInMilliseconds |价格

我想从中创建 OHLC(开盘价、最高价、最低价、收盘价)蜡烛图.这基本上是将某个时间段(假设每 1 分钟)的所有价格组合在一起，然后选择它们的最小值、最大值以及第一个和最后一个价格.

And I want to create OHLC (Open, High, Low, Close) candles from it. That is basically group all the prices from some period of time (let's say every 1 minute) and select the min, max and first and last price of them.

到目前为止我已经创建了这个查询:

I have created this query so far:

SELECT
  MIN(price) as low,
  MAX(price) as  high,
  FLOOR(timeInMilliseconds/(1000*60)) as open_time
FROM ticks
GROUP BY FLOOR(timeInMilliseconds/(1000*60))

这是可行的，但问题是开盘(第一)和收盘(最后)价格.
有没有办法让它们在同一个查询中(有效地)?

And that works, but the problem is the Open (first) and Close (last) price.
Is there someway to get them in the same query (efficiently)?

推荐答案

您需要找到每个时间段的最大和最小时间，然后将您的表 JOIN 加入它们以获得 这些时间的价格值:

You need to find the maximum and minimum times for each time period and then JOIN your table to them to get the price values for those times:

SELECT t1.price AS open,
       m.high,
       m.low,
       t2.price as close,
       open_time
FROM (SELECT MIN(timeInMilliseconds) AS min_time,
             MAX(timeInMilliseconds) AS max_time,
             MIN(price) as low,
             MAX(price) as high,
             FLOOR(timeInMilliseconds/(1000*60)) as open_time
      FROM ticks
      GROUP BY open_time) m
JOIN ticks t1 ON t1.timeInMilliseconds = min_time
JOIN ticks t2 ON t2.timeInMilliseconds = max_time

dbfiddle 演示

这篇关于使用 SQL 将报价数据转换为烛台 (OHLC)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！