22,209
社区成员
发帖
与我相关
我的任务
分享
drop table #BaseData,#Summary
--- 准备模拟数据
create table #BaseData(date_time varchar(20),Write int)
insert #BaseData
select '01:00',99
union all
select '01:05',30
union all
select '01:10',46
union all
select '01:15',77
union all
select '01:20',11
union all
select '01:25',76
union all
select '01:30',42
union all
select '01:35',80
union all
select '01:40',20
--- 查看模拟数据
selecT * from #BaseData
--- 生成排序列,必将数据转入汇总零时表
selecT ROW_NUMBER()over(order by date_time) sno,* into #Summary from #BaseData
--- 汇总零时表中添加 平均值列
alter table #Summary add AvgWrite float
---4个值求平均,所以初始话4个变量
declare @P1 float,@P2 float,@P3 float,@P4 float
---将平均值保存在AvgWrite列中
update #Summary set @P1=case when sno%4=1 then Write else @P1 end
,@P2=case when sno%4=2 then Write else @P2 end
,@P3=case when sno%4=3 then Write else @P3 end
,@P4=case when sno%4=0 then Write else @P4 end
,AvgWrite=(@P1+@P2+@P3+@P4)/4.0
---查看最终结果
selecT * from #Summary
--知道起始时间么,假设表是tb,日期时间字段是date
declare @mindate datetime
select top 1 @mindate = date from tb order by date
select (datediff(mi,@mindate,date)-1)/20 as date,avg(colName) as avgcol
from tb
group by (datediff(mi,@mindate,date)-1)/20