使用T-SQL将OHLC-Stockmarket数据归入多个时间范围 [英] Group OHLC-Stockmarket Data into multiple timeframes with T-SQL
问题描述
我正在使用SQL Server 2008 R2,并且需要创建按时间间隔分组的新表。
这些数据是来自股市指数的数据。我有1分钟的时间间隔的数据,现在我需要5,10,15,30,45,60分钟的时间间隔。我的主要关键是时间戳。
我的问题是:如何查询1分钟数据表以返回按特定时间间隔分组的数据,例如5分钟间隔。
查询必须返回该特定组中的最高,最低,最后和第一个值,最重要的还是组中时间戳的最后一个条目。
我对SQL语言非常陌生,并且尝试过在网络上发现大量代码,但是我无法完全返回想要的结果。 b
$ b
数据:
TimeStamp |打开|高|低|关闭
2012-02-17 15:15:0 | 102 | 110 | 100 | 105
2012-02-17 15:16:0 | 106 | 112 | 105 | 107
2012-02-17 15:17:0 | 2012-02-17 15:18:0 | 105 | 109 | 104 | 106
2012-02-17 15:19:0 | 107 | 112 | 107 | 112
2012-02-17 15:20:0 | 115 | 125 | 115 | 124
<
时间戳|打开|高|低|关闭
2012-02-15:19:0 | 102 | 125 | 98 | 124
2012-02-15:24:0 | 115. | .... | ... | ...
2012-02-15:29:0 | .... | .... | ... | ...
c $ c>,你会得到很多天。如果你乘以 24 * 12
,你会得到5分钟的间隔数。所以如果你分组:
cast(cast(timestamp as float)* 24 * 12 as int)
您可以每五分钟完成一次汇总:
选择min(timestamp)
,max(high)作为最高
,min(低)作为最低
从@t
组
cast(cast(timestamp as float)* 24 * 12 as int)
寻找第一个和最后一个行在SQL Server中很棘手。以下是使用 row_number
的一种方法:
$ b
select min(timestamp)
,max(high)作为最高
,min(低)作为最低
,min(当rn_asc = 1,然后[open]结束时)作为第一个
,min(当rn_desc (
)通过cast(cast(timestamp as float)* 24 * 12 as int)
选择row_number()(
分区)
按时间戳顺序排序)作为rn_asc
,row_number()over(
分区通过cast(cast(timestamp as float)* 24 * 12 as int)
order by timestamp desc)as rn_desc
,*
from @t
)as SubQueryAlias
group by
cast(cast(timestamp as float)* 24 * 12 as int)
code>
以下是 SE Data的工作示例。
I'm using SQL Server 2008 R2 and need to create new tables grouped in Time intervals.
The data is data from a stock market index. I have the data in 1 minute intervals, now i need them in 5,10,15,30,45,60...minute intervals. My primary key is the time stamp.
My question is: how to query the 1 minute data table to return data that is grouped by a specific time interval for example 5 minute intervals.
The query must return the Highest, Lowest, Last and First values in that particular group and most importantly also the last entry of the time-stamp in the group.
I'm very new to the SQL language and have tried numerous code found on the net, but i cant get to exactly return the desired results.
Data:
TimeStamp | Open | High | Low | Close
2012-02-17 15:15:0 | 102 | 110 |100 |105
2012-02-17 15:16:0 |106 |112 |105 |107
2012-02-17 15:17:0 | 106 |110 |98 |105
2012-02-17 15:18:0 |105 |109 |104 |106
2012-02-17 15:19:0 |107 |112 |107 |112
2012-02-17 15:20:0 |115 |125 |115 |124
Desired Query Result (5 minutes):
Timestamp |Open|High|Low|Close
2012-02-15:19:0 |102 |125 |98 |124
2012-02-15:24:0 |115.|....|...|...
2012-02-15:29:0 |....|....|...|...
When you convert a datetime
to a float
, you get a number of days. If you multiply that by 24 * 12
, you get a number of 5 minute intervals. So if you group on:
cast(cast(timestamp as float) * 24 * 12 as int)
you can do aggregates per five minutes:
select min(timestamp)
, max(high) as Highest
, min(low) as Lowest
from @t
group by
cast(cast(timestamp as float) * 24 * 12 as int)
Finding the first and the last row is tricky in SQL Server. Here's one way using row_number
:
select min(timestamp)
, max(high) as Highest
, min(low) as Lowest
, min(case when rn_asc = 1 then [open] end) as first
, min(case when rn_desc = 1 then [close] end) as Last
from (
select row_number() over (
partition by cast(cast(timestamp as float) * 24 * 12 as int)
order by timestamp) as rn_asc
, row_number() over (
partition by cast(cast(timestamp as float) * 24 * 12 as int)
order by timestamp desc) as rn_desc
, *
from @t
) as SubQueryAlias
group by
cast(cast(timestamp as float) * 24 * 12 as int)
Here's a working example at SE Data.
这篇关于使用T-SQL将OHLC-Stockmarket数据归入多个时间范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!