我想通过平均连续的12个数值来压缩我的数据库,使其大小缩减为原来的1/12。
为了实现这一目标,我打算使用TIMESTAMP
和FLOAT(4,2)
类型计算平均值。为了正确地对TIMESTAMP
进行平均,首先计划将其转换为INT
类型,然后用AVG()
函数计算平均值,最后再将平均值转回为DATETIME
类型。然而,在这个操作过程中,我遇到了一个无法解释的语法错误。
这是我尝试过的相应查询语句:
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
我已经尝试过多种不同版本的CAST指令,但都没有得到期望的结果。唯一语法正确的CAST语句是当我将AVG()值转换回原始数据类型时。
表current
具有以下结构:
- time(TIMESTAMP)
- current(FLOAT(4,2))
- id(INT AUTO_INCREMENT)
表condensed_12_current
具有以下结构:
- time(TIMESTAMP)
- current(FLOAT(4,2))
MySQL版本:Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)