sql 移动平均

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT item, purchases, category, AVG(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_purchases
FROM Produce

(an-1+an+an+1)/3

item	purchases	category	avg_purchases	移动平均计算方法
orange	2	fruit	2	2/1
leek	2	vegetable	4	(2+2+8)/3
apple	8	fruit	6.33333
cabbage	9	vegetable	9
lettuce	10	vegetable	14
kale	23	vegetable	16.5

±------------------------------------------------------+