我想找到三天的平均值。預期輸出：
| dt | 上午 | running_avg |
|---|---|---|
| 2022-05-1 | 100 | 0 |
| 2022-05-2 | 150 | 0 |
| 2022-05-3 | 50 | 100 |
| 2022-05-14 | 250 | 150 |
| 2022-05-15 | 0 | 100 |
應計算 3 天視窗的平均值。我的查詢是：
select a.dt, avg(b.amt) over(order by a.dt ) as running_avg,b.amt
from trans a
left join trans b on b.dt = a.dt
where a.dt between DATEADD(day,-3, a.dt) and getdate()
我的查詢只是給出正常的運行平均值，而不是 3 天的平均值。讓我知道如何在 SQL Server 中完成此操作。
謝謝！
uj5u.com熱心網友回復：
看來您需要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 | 上午 | running_avg | running_avg2 |
|---|---|---|---|
| 2022-05-01 | 100 | 100 | 0 |
| 2022-05-02 | 150 | 125 | 0 |
| 2022-05-03 | 50 | 100 | 100 |
| 2022-05-14 | 250 | 150 | 150 |
| 2022-05-15 | 0 | 100 | 100 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476569.html
下一篇：使用H2DB和SQL遞回分割路徑