7

I need to group stockmarket "1min" data with {Name, DateTime, Open, High, Low, Close, Volume} into different timeframes ie. "5mins/15mins/60mins" on MYSQL. Schema built on sqlfiddle - http://sqlfiddle.com/#!2/91433.

I found a link - Group OHLC-Stockmarket Data into multiple timeframes with T-SQL with similar requirement for MSSQL.

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

I am a newbie to sql, can anyone please point me in the right direction?

Community
  • 1
  • 1
Sri
  • 81
  • 1
  • 5
  • Do you actually have a problem here, or are you just stating what you've done so far? – Bojangles Sep 21 '12 at 10:43
  • @JamWaffles, thanks for looking into this. I do not have a solution for the problem. I want an SQL query to convert the "1min" ohlc data to "5min" intervals. – Sri Sep 21 '12 at 14:22

4 Answers4

16

I know this is an old question, but look at this much "simpler" solution. There is a trick for open and close price. You might 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

Gravy
  • 12,264
  • 26
  • 124
  • 193
Andrej Mikulik
  • 569
  • 5
  • 14
  • Great idea to avoid multiple queries! Thanks a lot – iMacTia Nov 25 '14 at 09:46
  • what does `period` contains? is it just string? – Sisir May 25 '15 at 14:36
  • It's been long time ago. But when I am looking at it now: It's integer/string provided from outside. The query used string enclosed by ". Stackoverflow removed those quotes. Period is concatenated with the rest of the query string... i.e. from SQL perspective it's constant. You can put 300 instead of "+period+" and you will have 5 minutes hardcoded. – Andrej Mikulik Jun 01 '15 at 16:55
1

Finally resolved the issue with 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 ))

Toughest part was to get the Open and Close for the "Specific Time Intervals". I am doing an inner join of 'high,low,open' with 'close' on 'date'. I can switch the time intervals by changing the denominator in (Floor(unix_timestamp(mydate)/300 )). Currently not worried about the performance as long as it works :).

Sri
  • 81
  • 1
  • 5
0

Query has error, change MIN to MAX for Close 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
Ruslan
  • 1
  • 1
0

I found that the solution of @Ruslan doesn't work in latest versions of MySQL so posting a version that works:

select 
date_add(str_to_date(date_format(ev.startdatetime, '%Y-%m-%d'),'%Y-%m-%d'), interval 
 hour(ev.startdatetime)*60 + floor(minute(ev.startdatetime)/5)*5 minute) timeframe
 , sum(ev.volume) volume
 , min(ev.startdatetime) mintime
 , substring_index(group_concat(open),',',1) open
 , max(high) as high 
 , min(low) as low
 , substring_index(group_concat(close),',',-1) close
from es1min_v ev
group by 
date_add(str_to_date(date_format(ev.startdatetime, '%Y-%m-%d'),'%Y-%m-%d'), interval 
 hour(ev.startdatetime)*60 + floor(minute(ev.startdatetime)/5)*5 minute)
Oraculo
  • 46
  • 5