mysql - 将 OHLC-Stockmarket 数据分组为多个时间范围 - Mysql

Question

我需要将股票市场“1 分钟”数据与 {Name, DateTime, Open, High, Low, Close, Volume} 分组到不同的时间范围内，即。 MYSQL 上的“5 分钟/15 分钟/60 分钟”。基于 sqlfiddle 构建的架构 - http://sqlfiddle.com/#!2/91433 .

我找到了一个链接 - Group OHLC-Stockmarket Data into multiple timeframes with T-SQL对 MSSQL 有类似的要求。

我试着点击链接 - http://briansteffens.com/2011/07/19/row_number-partition-and-over-in-mysql/ , 得到row_number(), over, partition in mysql 解决问题。

我是 sql 的新手，谁能给我指明正确的方向？

Answer 1

我知道这是一个老问题，但看看这个“更简单”的解决方案。开盘价和收盘价有一个技巧。你可能会喜欢它。

SELECT
  FLOOR(MIN(`timestamp`)/"+period+")*"+period+" AS timestamp,
  SUM(amount) AS volume,
  SUM(price*amount)/sum(amount) AS wavg_price,
  SUBSTRING_INDEX(MIN(CONCAT(`timestamp`, '_', price)), '_', -1) AS `open`,
  MAX(price) AS high,
  MIN(price) AS low,
  SUBSTRING_INDEX(MAX(CONCAT(`timestamp`, '_', price)), '_', -1) AS `close`
FROM transactions_history -- this table has 3 columns (timestamp, amount, price)
GROUP BY FLOOR(`timestamp`/"+period+")
ORDER BY timestamp  

周期以秒为单位