OHLC-Stockmarket Group Multiple Timeframe Data - Mysql

Question

I need to group the "1min" data with the stock market from {Name, DateTime, Open, High, Low, Close, Volume} into different timeframes, i.e. "5 minutes / 15 minutes / 60 minutes" in MYSQL. The schema built on sqlfiddle is http://sqlfiddle.com/#!2/91433 .

I found a link - OHLC-Stockmarket Data group in several timeframes with T-SQL with a similar requirement for MSSQL.

I tried to click on the link - http://briansteffens.com/2011/07/19/row_number-partition-and-over-in-mysql/ to get row_number (), over, section in mysql to solve the problem.

I'm new to sql, can anyone point me in the right direction?

Answer 1

Finally, the problem was fixed by the following mysql query:

select min(a.mydate),max(a.myhigh) as high,min(a.mylow) as low, min(case when rn_asc = 1 then a.myopen end) as open, min(case when rn_desc = 1 then b.myclose end) as close from( select @i := if((@lastdate) != (Floor(unix_timestamp(mydate)/300 )), 1, @i + 1) as rn_asc, mydate, myhigh, mylow, myopen, myclose, @lastdate := (Floor(unix_timestamp(mydate)/300 )) from onemindata_1, (select @i := 0) vt1, (select @lastdate := null) vt2 order by mydate ) a inner join( select @j := if((@lastdate1) != (Floor(unix_timestamp(mydate)/300 )), 1, @j + 1) as rn_desc, mydate,myclose, @lastdate1 := (Floor(unix_timestamp(mydate)/300 )) from onemindata_1, (select @j := 0) vt1, (select @lastdate1 := null) vt2 order by mydate desc )b on a.mydate=b.mydate group by (Floor(unix_timestamp(a.mydate)/300 )) 

The hardest part was getting Open and Close for the "Specific Time Intervals". I am doing the inner join "high, low, open" from "close" to "date". I can switch the time intervals by changing the denominator to (Floor (unix_timestamp (mydate) / 300)). Currently not worried about performance while it works :).

Answer 2

I know this is an old question, but look at this much more “simple” solution. There is a trick for open and closed prices. You may like it.

 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 

period is in seconds

Answer 3

The request has an error, change the MIN value to MAX for the closing price:

 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