在SQL server中滚动三天平均值

Question

我想找到三天平均值。预期的输出:

<表类> dt amt running_avg tbody><<tr>2022-05-110002022-05-215002022-05-3501002022-05-142501502022-05-150100

似乎你需要一个ROWS/RANGE参数在窗口的AVG():

数据:

SELECT *
INTO trans
FROM (VALUES
(CONVERT(date, '20220501'), 100),
(CONVERT(date, '20220502'), 150),
(CONVERT(date, '20220503'), 50),
(CONVERT(date, '20220514'), 250),
(CONVERT(date, '20220515'), 0)
) v (dt, amt)

声明:

SELECT 
dt, 
amt,
AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_avg,
CASE WHEN COUNT(*) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3 
THEN AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ELSE 0
END AS running_avg2
FROM trans
ORDER BY dt

结果:

<表类> dt amt running_avg running_avg2 tbody><<tr>2022-05-0110010002022-05-0215012502022-05-03501001002022-05-142501501502022-05-150100100