我想通过对12个连续值取平均值,将现在包含大量值的数据库压缩到原始大小的1/12.
为此,我使用类型TIMESTAMP
和FLOAT(4,2)
来形成平均值.为了正确地计算TIMESTAMP
的平均值,我首先想将TIMESTAMP
转换为INT
,然后用AVG()
计算平均值,最后将平均值转换回DATETIME
.然而,在此操作过程中,我收到了一个我无法解释的语法错误.
这是我try 过的各自的查询:
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
我try 了许多不同版本的CAST指令,但都没有产生预期的效果.语法上唯一正确的CAST语句是当我将AVG()值转换回原始数据类型时.
表电流具有以下 struct : 时间作为时间戳 当前为浮点数(4,2) ID为整型自动递增
表CONDESED_12_CURRENT具有以下 struct : 时间作为时间戳 当前为浮点数(4,2)
用于x86_64上的Linux的MySQL Ver 8.2.0(MySQL社区服务器-GPL)