求移动平均的问题

Question

下面这个怎么用sql实现?

id  Sales_vol      mov_avg

1     1

2     2

3     3           =(1+2+3)/3

4     5           =(2+3+5)/3

5     4           =(3+5+4)/3

6     2           =(5+4+2)/3

7............

.......

Answer 1

--> 测试数据：@t
declare @t table
([id] int,[Sales_vol] int)
insert @t
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,5 union all
select 5,4 union all
select 6,2

select a.*,b.Sales_vol,c.Sales_vol 
from @t a 
left join @t b on a.id=b.id+1
left join @t c on a.id=c.id+2
/*
id          Sales_vol   Sales_vol   Sales_vol
----------- ----------- ----------- -----------
1           1           NULL        NULL
2           2           1           NULL
3           3           2           1
4           5           3           2
5           4           5           3
6           2           4           5
*/

--id不连续的时候用row_number()生成一个连续的序号，用序号来处理即可。

select 
	a.[id],a.[Sales_vol],
	(a.Sales_vol+b.Sales_vol+c.Sales_vol)/3.00 as mov_avg
from @t a 
left join @t b on a.id=b.id+1
left join @t c on a.id=c.id+2
/*
id          Sales_vol   mov_avg
----------- ----------- ------------------------------
1           1           NULL
2           2           NULL
3           3           2.000000
4           5           3.333333
5           4           4.000000
6           2           3.666666
*/

Answer 2

declare @t table
([id] int,[Sales_vol] int)
insert @t
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,5 union all
select 5,4 union all
select 6,2



SELECT *
--UPDATE A SET	-- 要更新的话, 只需要把 SELECT 改 UPDATE
FROM @t A
	OUTER APPLY(
		SELECT
			avg_value = AVG([Sales_vol])
		FROM(
			SELECT TOP 3	-- 3 条记录求平均
				[Sales_vol] = CONVERT(float, [Sales_vol])
			FROM @t BB
			WHERE BB.id <= A.id
			ORDER BY BB.id DESC
		)B
		HAVING COUNT(*) >= 3	-- 3 条记录		
	)B

Answer 3

-- SQL 2012 可以直接用窗口函数(需要特别处理一下前两条记录)
declare @t table
([id] int,[Sales_vol] int)
insert @t
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,5 union all
select 5,4 union all
select 6,2



SELECT *,
	AVG(CONVERT(float, [Sales_vol])) OVER(ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM @t A

Answer 4

斑竹的答案比邹建大侠的好理解

---

给我写信： QQ我：