SQL游标操作每隔5分钟时间段数据统计信息

Tb_People 表信息：

id    uname   era   amount   plushtime
1000031   张亮   中年   100000   201404050908
1000032   张亮   中年   100000   201404050913
1000033   天天   年轻   233233   201404050918
1000034   天天   年轻   233233   201404050923
1000035   kimi   年轻   455631   201404050933
1000036   kimi   年轻   455631   201404050938

--分析出每5分钟内有多少人乘车
--时间转化秒不用转化初始刷卡时间值是20140404090806
UPDATE [Tb_People] SET shuacardtime=SUBSTRING(plushtime,1,4)+'-'+SUBSTRING(plushtime,5,2)+'-'+SUBSTRING(plushtime,7,2)+' '+SUBSTRING(plushtime,9,2)+':'+SUBSTRING(plushtime,11,2)

结果为：

id      uname   era   amount   plushtime   shuacardtime
1000031   张亮   中年   100000   201404050908   2014-04-05 09:08
1000032   张亮   中年   100000   201404050913   2014-04-05 09:13
1000033   天天   年轻   233233   201404050918   2014-04-05 09:18
1000034   天天   年轻   233233   201404050923   2014-04-05 09:23
1000035   kimi   年轻   455631   201404050933   2014-04-05 09:33
1000036   kimi   年轻   455631   201404050938   2014-04-05 09:38

--构造区间段

declare @dayBegin datetime,@dayEnd datetime

declare @table table(StartTime datetime,EndTime datetime)

set @dayBegin = '2014-04-05 6:00'

set @dayEnd = '2014-04-06 0:00'

while @dayBegin <=@dayEnd

 begin

 insert @table select @dayBegin,dateadd(mi,5,@dayBegin)  --每5分钟一个间隔

 set @dayBegin=dateadd(mi,5,@dayBegin)

 end

select * from @table -- 执行后数据如下

StartTime      EndTime
2014-04-05 06:00:00.000   2014-04-05 06:05:00.000
2014-04-05 06:05:00.000   2014-04-05 06:10:00.000
2014-04-05 06:10:00.000   2014-04-05 06:15:00.000
2014-04-05 06:15:00.000   2014-04-05 06:20:00.000
2014-04-05 06:20:00.000   2014-04-05 06:25:00.000

--区间段分好了，就可以想到每取出一个时间段，然后在乘车时间记录表里查询有多少条记录在该段时间内就行了，可以考虑用游标。

declare s cursor  --declare 创建游标

static

for select StartTime,EndTime from @table

--定义变量

declare @StartTime datetime,@EndTime datetime

declare @TempTable table(StartTime datetime,EndTime datetime,Number int)

open s  --打开游标

fetch next from s into @StartTime,@EndTime --提取上次提取行的下一行

while(@@fetch_status = 0)

begin

  insert @TempTable select isnull(max(@StartTime),@StartTime),isnull(max(@EndTime),@EndTime), count(*) from Tb_People where shuacardtime > @StartTime and shuacardtime <=@EndTime

--这里就不能用between and了，不然分隔的时间点上车的人数会在相邻的两个区间段重复计数,另外第一班车的上车时间等于@StartTime 没有计进去，这里不影响总体分析，当然可以做个标记，读一个区间段时用between...and...就可以了

   fetch next from s into @StartTime,@EndTime

end

close s  --关闭游标

deallocate s  --删除游标，释放资源

select * from @TempTable

执行结果如下：

StartTime              EndTime            Number
2014-04-05 09:05:00.000   2014-04-05 09:10:00.000   1
2014-04-05 09:10:00.000   2014-04-05 09:15:00.000   1
2014-04-05 09:15:00.000   2014-04-05 09:20:00.000   1
2014-04-05 09:20:00.000   2014-04-05 09:25:00.000   1
2014-04-05 09:25:00.000   2014-04-05 09:30:00.000   0
2014-04-05 09:30:00.000   2014-04-05 09:35:00.000   1

Substring

SQL游标操作每隔5分钟时间段数据统计信息