在SQL (mariadb)中创建和计算z分数列

Question

正如标题所说，寻找一种简单的方法来计算列中每行的z分数。

我尝试了以下，不知道为什么它不工作(返回错误ER_INVALID_GROUP_FUNC_USE)

ALTER TABLE tbl
ADD z_score INT;
UPDATE tbl
SET z_score = (values - avg(values))/ stddev(values)
WHERE values IS NOT NULL;

我在网上看到过这样做的方法，但它们感觉比必要的更复杂。

Answer 1

为了计算z-score，你需要平均值和标准差。您不需要指定正在使用的数据库，但是在(几乎)所有数据库中都应该工作的一种方法是子查询:

UPDATE tbl
SET z_score = (SELECT (tbl.values - avg(tbl2.values)) / stddev(tbl2.values)
FROM tbl tbl2
)
WHERE values IS NOT NULL;

大多数数据库也支持UPDATE中的某种JOIN操作，但语法取决于数据库。

编辑:

MariaDB不支持上述标准SQL语法。相反，您可以使用CROSS JOIN:

UPDATE tbl CROSS JOIN
(SELECT AVG(values) as avg_values, STDDEV(values) as stddev_values
FROM tbl
) t2
SET tbl2.z_score = (tbl.values - avg_values) / NULLIF(stddev_values, 0)
WHERE values IS NOT NULL AND stddev_values <> 0;