如何对表中前后两条记录进行计算?
怎么计算前后两条记录时间字段的差值呢?比如:
uer sessionid pathid timeid
1 1 2 0:0:0
1 1 1 0:0:15
1 1 3 0:0:40
1 2 2 0:3:15
1 2 4 0:3:40
2 3 2 0:4:0
2 3 1 0:4:15
2 3 3 0:4:40
2 4 2 0:5:15
2 4 4 0:5:40
希望得到以下表,添加的timestay属性为pathid的持续时间,其中每个session的最后一个pathid所对应的timestay值设置为0,即下表
uer sessionid pathid timestay(秒)
1 1 2 15
1 1 1 25
1 1 3 0
1 2 2 25
1 2 4 0
2 3 2 15
2 3 1 25
2 3 3 0
2 4 2 25
2 4 4 0
还请各位再帮帮忙啊!
怎么计算前后两条记录时间字段的差值呢?比如:
uer sessionid pathid timeid
1 1 2 0:0:0
1 1 1 0:0:15
1 1 3 0:0:40
1 2 2 0:3:15
1 2 4 0:3:40
2 3 2 0:4:0
2 3 1 0:4:15
2 3 3 0:4:40
2 4 2 0:5:15
2 4 4 0:5:40
希望得到以下表,添加的timestay属性为pathid的持续时间,其中每个session的最后一个pathid所对应的timestay值设置为0,即下表
uer sessionid pathid timestay(秒)
1 1 2 15
1 1 1 25
1 1 3 0
1 2 2 25
1 2 4 0
2 3 2 15
2 3 1 25
2 3 3 0
2 4 2 25
2 4 4 0
还请各位再帮帮忙啊!
on a.uer=b.uer and a.sessionid =b.sessionid and a.timeid<b.timeid
group by a.uer, a.sessionid ,a.pathid ,a.timeid
order by a.timeid
insert into #q
select 1 , 1 , 2, '0:0:0' union all
select 1, 1 , 1, '0:0:15' union all
select 1, 1 , 3, '0:0:40' union all
select 1, 2 , 2, '0:3:15' union all
select 1, 2 , 4, '0:3:40' union all
select 2, 3 , 2, '0:4:0' union all
select 2, 3 , 1, '0:4:15' union all
select 2, 3 , 3, '0:4:40' union all
select 2, 4 , 2, '0:5:15' union all
select 2, 4 , 4, '0:5:40'
select a.* ,isnull(datediff(ss,a.timeid,min(b.timeid)),0) from #q a left join #q b
on a.uer=b.uer and a.sessionid =b.sessionid and a.timeid<b.timeid
group by a.uer, a.sessionid ,a.pathid ,a.timeid
order by a.timeid/**
uer sessionid pathid timeid
----------- ----------- ----------- -------------------- -----------
1 1 2 0:0:0 15
1 1 1 0:0:15 25
1 1 3 0:0:40 0
1 2 2 0:3:15 25
1 2 4 0:3:40 0
2 3 2 0:4:0 15
2 3 1 0:4:15 25
2 3 3 0:4:40 0
2 4 2 0:5:15 25
2 4 4 0:5:40 0(所影响的行数为 10 行)**/
insert test0702 select 1, 1, 2 , '0:0:0' union all select
1, 1, 1, '0:0:15' union all select
1 , 1 , 3 , '0:0:40' union all select
1 , 2 , 2 , '0:3:15' union all select
1 , 2 , 4 ,'0:3:40' union all select
2 , 3 , 2 ,'0:4:0' union all select
2 , 3 , 1 ,'0:4:15' union all select
2 , 3 , 3 ,'0:4:40' union all select
2 , 4 ,2 ,'0:5:15' union all select
2 , 4 ,4 ,'0:5:40'select * from test0702
truncate table test0702select *,identity(int) as id into # from test0702
insert #(uer,sessionid,pathid,timeid) select top 1 uer,sessionid,pathid,timeid from # order by id descselect a.uer,a.sessionid,a.pathid,a.timeid,datediff(second,a.timeid,b.timeid) as timestay from # a join # b on a.id=b.id-1
insert test0702 select 1, 1, 2 , '0:0:0' union all select
1, 1, 1, '0:0:15' union all select
1 , 1 , 3 , '0:0:40' union all select
1 , 2 , 2 , '0:3:15' union all select
1 , 2 , 4 ,'0:3:40' union all select
2 , 3 , 2 ,'0:4:0' union all select
2 , 3 , 1 ,'0:4:15' union all select
2 , 3 , 3 ,'0:4:40' union all select
2 , 4 ,2 ,'0:5:15' union all select
2 , 4 ,4 ,'0:5:40'
select *,identity(int) as id into # from test0702
insert #(uer,sessionid,pathid,timeid) select top 1 uer,sessionid,pathid,timeid from # order by id descselect a.uer,a.sessionid,a.pathid,a.timeid,datediff(second,a.timeid,b.timeid) as timestay from # a join # b on a.id=b.id-1
运行之后结果最符合要求,但是在 sessionid变化的时候timestay数值不对
uer sessionid pathid timeid
1 1 2 0:0:0
1 1 1 0:0:15
1 1 3 0:0:40
1 2 2 0:3:15
希望在倒数第二条记录得到的timestay是0秒而不是(0:3:15-0:0:40)155
不知道还能否做出改进,谢谢!
create table a(uer int,sessionid int,pathid int,timeid varchar(20))
insert a values(1,1,2,'0:0:45')
insert a values(1,1,1,'0:0:45')
insert a values(1,1,1,'0:0:45')
insert a values(1,1,3,'0:0:46')
insert a values(1,128,12,'0:43:26')
insert a values(1,128,8,'0:43:26')
insert a values(8,8,4,'0:9:8')
insert a values(8,8,3,'0:9:9')
insert a values(8,8,2,'0:9:9')
insert a values(8,8,7,'0:10:20')go
--创建临时表#tmp1并插入数据
create table #tmp1(num int identity(1,1),uer int, sessionid int, pathid int, timeid datetime)
insert #tmp1 select * from a
--定义游标
declare @num int
declare @uer int
declare @sessionid int
declare @pathid int
declare @timeid datetime
declare @tmp_timeid datetime
declare @tmp_uer int
declare @tmp_sessionid int
create table #tmp(num int,uer int,sessionid int,pathid int,timeid int)declare test_cursor cursor for select num,uer,sessionid,pathid,timeid from #tmp1 order by num desc
open test_cursorfetch next from test_cursor into @num,@uer,@sessionid,@pathid,@timeid
while @@fetch_status = 0
begin
if (select count(*) from #tmp)=0
begin
insert #tmp values(@num,@uer,@sessionid,@pathid,0)
end
else
begin
select @tmp_uer=uer,@tmp_sessionid=sessionid
from #tmp
where num=(select min(num) from #tmp)
select @tmp_timeid=timeid from #tmp1 where num=(select min(num) from #tmp)
if (@uer=@tmp_uer) and (@sessionid=@tmp_sessionid)
begin
insert #tmp values(@num,@uer,@sessionid,@pathid,datediff(ss,@timeid,@tmp_timeid))
end
else
begin
insert #tmp values(@num,@uer,@sessionid,@pathid,0)
end
end
fetch next from test_cursor into @num,@uer,@sessionid,@pathid,@timeid
endclose test_cursor
deallocate test_cursor
select uer,sessionid,pathid,timeid as timestay from #tmp order by num
go