如何在MariaDB中创建z_score列并计算每行数据的z分数

错误原因 #

ER_INVALID_GROUP_FUNC_USE 报错的核心原因是AVG()、STDDEV()属于聚合函数，默认返回全表的统计结果，无法直接在行级更新语句中与单行列值混合计算，SQL无法识别计算层级。
另外你新增的z_score列定义为INT整型会丢失小数精度，建议改为DOUBLE或FLOAT浮点类型。

最简实现方案 #

方案1：通用关联更新（兼容MySQL、PostgreSQL等绝大多数数据库） #

先通过子查询计算全表的均值、标准差，再关联更新每行的z_score：

-- 调整列类型
ALTER TABLE tbl
ADD z_score DOUBLE;

-- 执行更新
UPDATE tbl
CROSS JOIN (
    SELECT AVG(`values`) AS avg_val, STDDEV(`values`) AS std_val
    FROM tbl
    WHERE `values` IS NOT NULL
) AS global_stats
SET z_score = (tbl.`values` - global_stats.avg_val) / global_stats.std_val
WHERE tbl.`values` IS NOT NULL;

方案2：窗口函数写法（支持窗口函数的数据库可用，代码更短） #

UPDATE tbl
SET z_score = (`values` - AVG(`values`) OVER ()) / STDDEV(`values`) OVER ()
WHERE `values` IS NOT NULL;

提示：values是SQL保留关键字，执行语句时如果报语法冲突，给列名加反引号包裹即可。

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