mssql server,有以下基础交易数据,存在表A里面,ID是一个自增长字段
ID datatime price amount
1 2013-07-21 21:17 1.2 11
2 2013-07-21 21:17 1.3 15
........
100 2013-07-21 21:30 1.25 30某一分钟可能有几条数据,也许没有数据现在需要查询某个时间段内、以某单位时间为间隔内的 起始价格, 最高价格,最低价格,结束价格,
单位期间内的amount总量比如要查询21:17分开始,以5分钟为一个间隔单位,
要得到21:17-->21:21 21:22-21:26--> 21:27-->21:31 ...
这些时间段内的起始价格, 最高价格,最低价格,结束价格,
单位期间内的amount总量谢谢了
K线图数据,数据统计
ID datatime price amount
1 2013-07-21 21:17 1.2 11
2 2013-07-21 21:17 1.3 15
........
100 2013-07-21 21:30 1.25 30某一分钟可能有几条数据,也许没有数据现在需要查询某个时间段内、以某单位时间为间隔内的 起始价格, 最高价格,最低价格,结束价格,
单位期间内的amount总量比如要查询21:17分开始,以5分钟为一个间隔单位,
要得到21:17-->21:21 21:22-21:26--> 21:27-->21:31 ...
这些时间段内的起始价格, 最高价格,最低价格,结束价格,
单位期间内的amount总量谢谢了
K线图数据,数据统计
if OBJECT_ID('tempdb..#t') is not null
drop table tempdb..#t;
select 1 ID,'2013-07-21 21:17' datatime,1.2 price,11 amount
into #t
union all select 2 ,'2013-07-21 21:17',1.3 ,15
union all select 3 ,'2013-07-21 21:18',1.5 ,11
union all select 4 ,'2013-07-21 22:19',1.51,12
union all select 5 ,'2013-07-21 22:20',1.52,13
union all select 6 ,'2013-07-21 23:21',1.53,15
union all select 7 ,'2013-07-22 01:21',1.54,12
union all select 8 ,'2013-07-22 01:22',1.55,12
union all select 9 ,'2013-07-22 01:23',1.56,12
union all select 10,'2013-07-22 01:30',1.25,30declare @increment int =5;;with t as
(
select sp.number, DATEADD(MINUTE,@increment*(sp.number-1),datatime) as startTime
,DATEADD(MINUTE,@increment*sp.number,datatime) endTime
from #t t
cross join master.dbo.spt_values sp
where t.ID=1
and sp.type='p'
and sp.number>0
),t2 as
(
select *,ROW_NUMBER() over(partition by b.number order by a.datatime) rn
,ROW_NUMBER() over(partition by b.number order by a.datatime desc) rn2
from #t a
join t b
on convert(datetime,a.datatime) >=b.startTime
and CONVERT(datetime, a.datatime)<b.endTime
)
select startTime,endTime,
(select price from t2 a where a.startTime=b.startTime and a.endTime=b.endTime
and a.rn=1) as startPrice
,(select price from t2 a where a.startTime=b.startTime and a.endTime=b.endTime
and a.rn2=1) as EndPrice
,MIN(price) MinPrice
,MAX(price) MaxPrice
from t2 b
group by startTime,endTime