CAST结合窗口函数引发语法错误：求12个连续值均值压缩数据库

问题背景 #

我需要将大表current按12个连续值取平均，压缩到新表condensed_12_current（原大小的1/12）。涉及TIMESTAMP和FLOAT(4,2)类型，为计算时间的平均值，先把TIMESTAMP转成整数求平均，再转回时间类型，但执行SQL时触发语法错误。

表结构 #

• current表：time TIMESTAMP，current FLOAT(4,2)，id INT自增主键
• condensed_12_current表：time TIMESTAMP，current FLOAT(4,2)
  使用MySQL 8.2.0 Linux x86_64社区版。

尝试的SQL语句 #

INSERT INTO condensed_12_current(time_mean, current_mean)
SELECT
  CAST(AVG(CAST(time as INTEGER)) AS DATETIME) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS time_mean,
  AVG(current) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS current_mean
FROM current
WHERE id % 12 = 0;

报错信息 #

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER)) AS DATETIME) OVER (
    ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ' at line 3

问题原因与解决方案 #

核心问题 #

MySQL的语法规则要求：窗口子句OVER()必须直接紧跟在聚合函数之后，不能将聚合函数嵌套在CAST这类函数内部再附加窗口子句。你的写法把AVG()放在了CAST()里面，再在外面加OVER()，违反了这个语法顺序。

修正后的SQL语句 #

先通过窗口函数计算出整数形式的时间平均值，再将结果转换为DATETIME（这里推荐用UNIX_TIMESTAMP()替代直接转INT，行为更明确稳定）：

INSERT INTO condensed_12_current(time_mean, current_mean)
SELECT
  CAST(AVG(UNIX_TIMESTAMP(time)) OVER (ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS DATETIME) AS time_mean,
  AVG(current) OVER (ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS current_mean
FROM current
WHERE id % 12 = 0;

额外说明 #

1. UNIX_TIMESTAMP(time)会把TIMESTAMP转换为从1970-01-01 00:00:00 UTC开始的秒数整数，比直接CAST(time AS INTEGER)更可靠，避免不同环境下的类型转换差异。
2. WHERE id %12=0的逻辑是对的：如果id是连续自增的，这个条件会筛选出每12条数据的最后一条，窗口函数会包含它前面的11条记录，刚好完成12个连续值的平均计算。

内容的提问来源于stack exchange，提问作者moritz1979