mysql - 如何修复计算不准确的移动平均线的 SQL 查询

Question

我有一个表格，其中包含星期 week 和平均时间 time。示例表数据如下。

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 查询以获得所需结果中所示的准确移动平均值？

Answer 1

您需要按周订购:

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;

请注意，您似乎想要移动平均线的前三周和当前行——对于“四周”移动平均线来说有点违反直觉。