SQL SERVER游标操作
前几天一朋友做数据分析,数据源是EXCEL的,他们作分析都编写VBA脚本来分析,其中有一个问题问了我下,我看了之后感觉处理数据分析完全可以导入到数据库做分析,编写VBA来分析感觉怪怪(不过也有好处,可以直接把生成的数据转化成图形)。其中一个要求是这样的,某一车站从早上第一班车到晚上最后一班车,期间不断有人刷卡上车,要分析出每5分钟内有多少人上车。我拿过EXCEL表,只有一列数据有效,我把它导入到SQL SERVER里,如下图:
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor1.jpg)
数据格式是存的是有点类似时间戳类型的,先要转换成标准的时间格式,简单处理下可以了,增加一列好了
UPDATE 西直门站 SET PullInTime=SUBSTRING(进站时间,1,4)+'-'+SUBSTRING(进站时间,5,2)+'-'+SUBSTRING(进站时间,7,2)+' '+SUBSTRING(进站时间,9,2)+':'+SUBSTRING(进站时间,11,2) 要求精确到分就可以了,秒就不转换了,转换后的格式如下:
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor2.jpg)
该站的首发班车时间是凌晨5点,末班是晚上零点,首先可以构造一个时间的区间段表,如下
--构造区间段
declare @dayBegin datetime,@dayEnd datetime
declare @table table(StartTime datetime,EndTime datetime)
set @dayBegin = '2009-6-23 5:00'
set @dayEnd = '2009-6-24 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 执行后数据如下
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor3.jpg)
区间段分好了,就可以想到每取出一个时间段,然后在上车时间记录表里查询有多少条记录在该段时间内就行了,可以考虑用游标。
declare s cursor --declare 创建游标
static
for select StartTime,EndTime from @table
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor4.jpg)
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor1.jpg)
数据格式是存的是有点类似时间戳类型的,先要转换成标准的时间格式,简单处理下可以了,增加一列好了
UPDATE 西直门站 SET PullInTime=SUBSTRING(进站时间,1,4)+'-'+SUBSTRING(进站时间,5,2)+'-'+SUBSTRING(进站时间,7,2)+' '+SUBSTRING(进站时间,9,2)+':'+SUBSTRING(进站时间,11,2) 要求精确到分就可以了,秒就不转换了,转换后的格式如下:
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor2.jpg)
该站的首发班车时间是凌晨5点,末班是晚上零点,首先可以构造一个时间的区间段表,如下
--构造区间段
declare @dayBegin datetime,@dayEnd datetime
declare @table table(StartTime datetime,EndTime datetime)
set @dayBegin = '2009-6-23 5:00'
set @dayEnd = '2009-6-24 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 执行后数据如下
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor3.jpg)
区间段分好了,就可以想到每取出一个时间段,然后在上车时间记录表里查询有多少条记录在该段时间内就行了,可以考虑用游标。
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 西直门站 where PullInTime > @StartTime and PullInTime <=@EndTime
--这里就不能用between and了,不然分隔的时间点上车的人数会在相邻的两个区间段重复计数,另外第一班车的上车时间等于@StartTime 没有计进去,这里不影响总体分析,当然可以做个标记,读一个区间段时用between...and...就可以了
fetch next from s into @StartTime,@EndTime
end
close s --关闭游标
deallocate s --删除游标,释放资源
select * from @TempTable
![](https://images.cnblogs.com/cnblogs_com/peaceli/cursor4.jpg)