在MySQL中为每一行计算Z分数? (简单的) [英] Calculating Z-Score for each row in MySQL? (simple)

问题描述

我正在寻找一种有效的方法来为MySQL表中的每一行分配一个Z分数(也称为标准分数).

I am looking for an efficient way to assign a Z-Score (also known as standard score) for each row in a MySQL table.

Z = Z-Score
X = Actual value
μ = Mean value
σ = Standard Deviation

我尝试过:

SELECT pTime,(M1-AVG(M1))/STD(M1),
             (M2-AVG(M2))/STD(M2),
             (M3-AVG(M3))/STD(M3),
             (M4-AVG(M4))/STD(M4)
 FROM mergebuys;

但最后只有1行.

当子查询只需要计算一次时，使用子查询的效率似乎很低.

It seems really inefficient to have to use a subquery when it only needs to be calculated one time.

推荐答案

SELECT
    mergebuys.pTime,
    (mergebuys.M1 - aggregates.AVGM1) / aggregates.STDM1 AS z1,
    (mergebuys.M2 - aggregates.AVGM2) / aggregates.STDM2 AS z2,
    (mergebuys.M3 - aggregates.AVGM3) / aggregates.STDM3 AS z3,
    (mergebuys.M4 - aggregates.AVGM4) / aggregates.STDM4 AS z4
FROM
    mergebuys
    CROSS JOIN (
        SELECT
            AVG(M1) AS AVGM1,
            STD(M1) AS STDM1,
            AVG(M2) AS AVGM2,
            STD(M2) AS STDM2,
            AVG(M3) AS AVGM3,
            STD(M3) AS STDM3,
            AVG(M4) AS AVGM4,
            STD(M4) AS STDM4
        FROM
            mergebuys
    ) AS aggregates

这篇关于在MySQL中为每一行计算Z分数? (简单的)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！