如何记算当前列与前一列的均值?
create table t (a int);
insert into t
select 1
union all
select 2
union all
select 3
union all
select 4;
create table #t (a INT,id int);
insert into #t
select 1,1
union all
select 2,2
union all
select 3,3
union all
select 4,4
SELECT a,'('+RTRIM(a)+'+'+RTRIM((SELECT a FROM #t WHERE id=t1.id-1))+')/2' [avg]
FROM #t t1
create table t (a int);
insert into t
select 1
union all
select 2
union all
select 3
union all
select 4;
;with tt
as
(
select *,ROW_NUMBER() over(order by @@servername) ronum
from t
)
select t1.a,(t1.a + t2.a) /2 'avg(Cn,Cn-1)'
from tt t1
left join tt t2
on t1.ronum = t2.ronum + 1
/*
aavg(Cn,Cn-1)
1NULL
21
32
43
*/
create table t (a int);
insert into t
select 1
union all
select 2
union all
select 3
union all
select 4;
;with tt
as
(
select *,ROW_NUMBER() over(order by @@servername) ronum
from t
)
select t1.a,(t1.a + t2.a) /2.0 'avg(Cn,Cn-1)'
from tt t1
left join tt t2
on t1.ronum = t2.ronum + 1
/*
aavg(Cn,Cn-1)
1NULL
21.500000
32.500000
43.500000
*/
create table #tab (a INT,id int);
insert into #tab
select 1,1
union all
select 2,2
union all
select 3,3
union all
select 4,4
select a.a,'('+convert(varchar(20),a.id)+'+'+convert(varchar(20),
case when b.id-1=0 then null else b.id-1 end)+')/2' as [avg(Cn,Cn-1)] from #tab a
left join #tab b on a.id=b.id
a avg(Cn,Cn-1)
----------- ---------------------------------------------
1 NULL
2 (2+1)/2
3 (3+2)/2
4 (4+3)/2
(4 行受影响)
create table #tab (a INT,id int);
insert into #tab
select 1,1
union all
select 2,2
union all
select 3,3
union all
select 4,4
select a.a,(convert(numeric(18,2),a.id)+
case when b.id-1=0 then null else b.id-1 end )/2 as [avg(Cn,Cn-1)] from #tab a
left join #tab b on a.id=b.id
a avg(Cn,Cn-1)
----------- ---------------------------------------
1 NULL
2 1.500000
3 2.500000
4 3.500000
(4 行受影响)
create table #t (a int);
insert into #t
select 1
union all
select 2
union all
select 3
union all
select 4;
;with ceb as
(
select ROW_NUMBER() over(order by getdate()) as rn,* from #t
)
select b.a,cast(((b.a+c.a)/2.0) as float) as 'avg(Cn,Cn-1)' from ceb b left join
ceb c on b.rn=c.rn+1