>我有一个表格,上面有周数和平均时间。示例表数据如下。
CREATE TABLE my_table (
`week` INTEGER,
`time` INTEGER
);
INSERT INTO my_table
(`week`, `time`)
VALUES
('14', '-249'),
('15', '-267'),
('16', '-254'),
('17', '-256'),
('18', '-254'),
('19', '-262'),
('20', '-265'),
('21', '-263'),
('22', '-258'),
('23', '-257'),
('24', '-269'),
('25', '-278'),
('26', '-278'),
('27', '-285');
我运行以下代码以根据数据获取 4 周移动平均线:
SELECT week, time, avg(time)
OVER (ORDER BY time ROWS BETWEEN 4 PRECEDING AND 0 FOLLOWING) AS moving_average
FROM my_table
ORDER BY week ASC
这给了我以下输出:
week time moving_average
14 -249 -254
15 -267 -275
16 -254 -257
17 -256 -259
18 -254 -255
19 -262 -265
20 -265 -271
21 -263 -268
22 -258 -263
23 -257 -261
24 -269 -277
25 -278 -281
26 -278 -280
27 -285 -285
但是,移动平均线不正确。它应该看起来像这样:
Desired Results
week time moving_average
14 -249 null
15 -267 null
16 -254 null
17 -256 -257
18 -254 -258
19 -262 -257
20 -265 -259
21 -263 -261
22 -258 -262
23 -257 -261
24 -269 -262
25 -278 -266
26 -278 -271
27 -285 -278
您能建议如何修改我的SQL查询以获得所需的结果中显示的准确移动平均线吗?
您需要按周排序:
SELECT week, time,
avg(time) OVER (ORDER BY week ROWS BETWEEN 4 PRECEDING AND 0 FOLLOWING) AS moving_average
FROM my_table
ORDER BY week ASC;
这仍然没有完全按照你想要的去做,但这是一个更明显的问题。 根据您的示例数据,我认为您需要:
SELECT week, time,
(case when row_number() over (order by week) >= 4
then avg(time) over (order by week rows between 3 preceding and current row)
end) as moving_average
FROM my_table
ORDER BY week ASC;
请注意,您似乎希望移动平均线的前三周和当前行 - 对于"四周"移动平均线来说有点违反直觉。