求助 请问怎么算这种平均数
有一个新需求 算每日平均数
就比如说 今天是5号
那么我要的结果就是
result=(1号+2号+3号+4号+5号)/5
每天依次类推
而且数据还包含不同的月份,不同的年份,请问应该怎么实现呢?
求助
[解决办法]
在看看这个
create table #tmp
(
recordDate varchar(50)
,cl1_40 int
,cl2_60 int
,cl3_70 int
)
insert into #tmp(recordDate,cl1_40,cl2_60,cl3_70)
select'2013-01-01',0, 14, 0
union allselect'2013-01-02', 0, 115, 0
union allselect'2013-01-03', 0, 128, 0
union allselect'2013-01-04', 0, 103, 0
union allselect'2013-01-05', 0, 64 ,0
union allselect'2013-02-06', 0, 12 ,0
union allselect'2013-02-07', 0, 43 ,0
union allselect'2013-02-08', 0, 89 ,0
union allselect'2013-02-09', 0, 59 ,0
union allselect'2013-02-10', 0, 74 ,0
select * from #tmp
select *
,(
select SUM(t1.cl2_60)*1.0/Max(DATEPART(day,t1.recordDate))
from #tmp t1
where t1.recordDate<=t.recordDate
and DATEPART(year,t1.recordDate)= DATEPART(year,t.recordDate)
and DATEPART(MONTH,t1.recordDate)= DATEPART(MONTH,t.recordDate)
) as 'DataOfYouWant'
from #tmp t
