MySQL查询需求：筛选偏离均值超1标准差的行并计算价格差异百分比

需要编写MySQL查询语句，筛选各产品中与常规价格差异较大的行，计算价格相对该产品均值的百分比差异（低于100%表示价格低于均值，高于100%表示价格高于均值），同时忽略与均值偏差小于1个标准差的价格。

样本数据 #

当前错误查询 #

SELECT code, fk_product_id, cost, cost/
  (SELECT avg(cost) FROM po_line aa WHERE aa.code = code) AS percent 
FROM po_line 
WHERE (SELECT STDDEV(cost) FROM po_line ss WHERE ss.code = code)>1;

问题说明 #

该查询未返回任何结果，但实际应有3行数据（每个产品各1行）出现在报告中。

期望结果 #

解决方案 #

错误分析 #

1. WHERE条件逻辑错误：原查询判断产品的标准差大于1，但样本中所有产品的标准差都远小于1（比如00805的标准差约为0.288），导致没有数据被筛选出来。正确逻辑应该是判断单条记录的价格与均值的偏差超过1个标准差，而非标准差本身大于1。
2. 子查询别名冲突：子查询中aa.code = code未指定外层表别名，可能导致字段引用歧义，应明确关联外层表的code字段。
3. 百分比计算遗漏：原查询的percent是cost/均值，需要乘以100并取整才能得到期望的百分比数值。

正确查询语句 #

使用窗口函数先计算每个产品分组的均值和标准差，再筛选符合条件的记录：

SELECT
    code,
    fk_product_id,
    cost,
    ROUND((cost / avg_cost) * 100) AS percent
FROM (
    SELECT
        code,
        fk_product_id,
        cost,
        AVG(cost) OVER (PARTITION BY code) AS avg_cost,
        STDDEV(cost) OVER (PARTITION BY code) AS stddev_cost
    FROM po_line
) AS sub
WHERE cost < (avg_cost - stddev_cost) OR cost > (avg_cost + stddev_cost);

查询逻辑说明 #

1. 内层子查询通过PARTITION BY code对每个产品分组，计算出该组的平均价格avg_cost和标准差stddev_cost。
2. 外层查询筛选出价格低于均值-标准差或高于均值+标准差的记录，即偏离常规价格超过1个标准差的行。
3. 最后计算价格相对均值的百分比并取整，得到期望的percent字段。

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