SQL Server : OHLC的逐个刻度数据
SQL Server : OHLC的逐个刻度数据
提问于 2017-11-13 17:27:38
有没有人能帮我做个SQL查询，在1分钟的时间间隔内把一分一秒的数据转换成OHLC (Open\High\Low\Close)？我能够获得高和低的数据，但在打开和关闭时遇到了困难。
我的样本数据如下所示：
idc ServerDateTime iDateTime sSymbol cAsk cBid Spread
---------------------------------------------------------------------------
2539581 2017-11-13 00:14:56.357 1510539296 EURUSD 1.16473 1.16460 0.00013
2539582 2017-11-13 00:14:56.373 1510539296 EURUSD 1.16475 1.16461 0.00014
2539583 2017-11-13 00:14:56.423 1510539296 EURUSD 1.16476 1.16462 0.00014
2539584 2017-11-13 00:14:56.520 1510539296 EURUSD 1.16477 1.16463 0.00014
2539585 2017-11-13 00:14:56.643 1510539296 EURUSD 1.16478 1.16463 0.00015
2539586 2017-11-13 00:14:58.207 1510539298 EURUSD 1.16478 1.16464 0.00014
2539587 2017-11-13 00:14:59.477 1510539299 EURUSD 1.16477 1.16464 0.00013
2539588 2017-11-13 00:15:00.337 1510539300 EURUSD 1.16477 1.16463 0.00014
2539589 2017-11-13 00:15:00.747 1510539300 EURUSD 1.16478 1.16463 0.00015
2539590 2017-11-13 00:15:00.873 1510539300 EURUSD 1.16477 1.16463 0.00014
2539591 2017-11-13 00:15:01.510 1510539301 EURUSD 1.16477 1.16464 0.00013有一些类似的问题和答案，但这些是针对python和MySQL的。
回答 1
Stack Overflow用户
发布于 2017-11-14 15:15:06
我终于找到了我在这个链接中找到的解决方案。https://data.stackexchange.com/stackoverflow/query/61772/new
select min(ServerDateTime) as DateTime
, max(cBid) as Highest
, min(cBid) as Lowest
, min(case when rn_asc = 1 then [cBid] end) as first
, min(case when rn_desc = 1 then [cBid] end) as Last
from (
select row_number() over (
partition by cast(cast(ServerDateTime as float) * 60 * 24 as int)
order by ServerDateTime) as rn_asc
, row_number() over (
partition by cast(cast(ServerDateTime as float) * 60 * 24 as int)
order by ServerDateTime desc) as rn_desc
, *
from dbo.MT4TICK
) as SubQueryAlias
group by
cast(cast(ServerDateTime as float) * 60 * 24 as int)
order by DateTime页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47260911
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号