将OHLC-Stockmarket数据分为多个时间范围 - Mysql [英] Group OHLC-Stockmarket Data into multiple timeframes - Mysql

问题描述

我需要将股票市场的1分钟数据与{名称，日期时间，开盘价，最高价，最低价，收盘价，成交量}分成不同的时间范围，即。 MYSQL上的5分钟/ 15分钟/ 60分钟。在sqlfiddle上构建的架构 - http://sqlfiddle.com/#!2/91433 。

我找到了一个链接 - 使用T-SQL将OHLC-Stockmarket数据分组到多个时间范围，对MSSQL也有类似的需求。

我尝试关注链接 - http://briansteffens.com/2011/ 07/19 / row_number-partition-and-over-in-mysql / ，以获得mysql中的row_number（）分区，以解决问题。

我是sql的新手，任何人都可以请我指出正确的方向吗？

解决方案

  select min（a.mydate），max （a.myhigh）为高，min（a.mylow）为低，
 min（当rn_asc = 1，然后a.myopen结束时）为开启，
 min（rn_desc = 1，则b .myclose end）作为关闭
 
从（
 
选择
 @i：= if（（@ lastdate）！=（Floor（unix_timestamp（mydate）/ 300 ）），1，@i + 1）作为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 
 
） （$ @ 
 
 
 
 
 @j：= if（（@ lastdate1）！=（Floor（unix_timestamp（mydate）/ 300）），1 ，@j + 1）作为rn_desc，
 mydate，myclose，
 @ lastdate1：​​=（Floor（unix_t imestamp（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））
  

最难的部分是获得特定时间间隔的打开和关闭。我正在做'高，低，开'的'内部连接'和'关闭'''日期'。我可以通过更改（Floor（unix_timestamp（mydate）/ 300））中的分母来切换时间间隔。目前，只要它能够工作，就不用担心它的表现：）。

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?

解决方案

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 :).

这篇关于将OHLC-Stockmarket数据分为多个时间范围 - Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！