对于MySQL，我需要一个报告，其中包含与平均值的标准差大于1的行(百分比

我尝试编写一个MySQL查询来计算与每个产品最常见的行有很大差异的行，并报告百分比差异，其中低于100%的价格低于该产品的平均价格，超过100%的价格高于平均价格。忽略与平均价格相差小于1个标准差的价格。
样本数据：
| _行ID|时间戳|代码|fk_产品_id| fk_采购订单标识|成本|
| - ------| - ------| - ------| - ------| - ------| - ------|
| 小行星5952| 2021年1月10日10时19分01秒|小零零八零五|小行星|五百四十三|0.850|
| 小行星9403| 2022年5月23日14时54分34秒|小零零八零五|小行星|小行星2942| 0.850|
| 小行星41595| 2022年11月23日11时20分26秒|小零零八零五|小行星|小行星3391| 1.350美元|
| 小行星39635| 2022年1月18日12时49分32秒|水1|三四四|小行星3153| 0.140|
| 小行星40134| 2022年4月6日22时39分34秒|水1|三四四|小行星2747| 0.190|
| 小行星41676| 2022年12月9日16时28分28秒|水1|三四四|小行星3398| 0.140|
| 小行星39634| 2022年1月18日12时49分31秒|编号309203 344400|小行星|小行星3154| 0.770|
| 小行星35634| 2021年3月3日15时23分23秒|编号309203 344400|小行星|小行星3203| 0.790|
| 小行星41264| 2022年11月16日11时41分44秒|编号309203 344400|小行星|小行星3357| 0.970|

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;

这不会返回任何行，但报表中应该显示三行（每个产品一行）。
预期结果应为：
| 代码|fk_产品_id|成本|百分比|
| - ------| - ------| - ------| - ------|
| 小零零八零五|小行星|1.350美元|一百三十三|
| 水1|三四四|0.190|一百二十一|
| 编号309203 344400|小行星|0.970|一百一十五|

此查询显示如何使用窗口函数计算标准差的数量以及每个给定代码的成本相对于平均成本的百分比。

SELECT code, cost, avg, std, 
  ABS(cost-avg)/std AS num_std, 
  cost*100/avg AS pct
FROM (
  SELECT code, cost,
    AVG(cost) OVER (PARTITION BY code) AS avg,
    STDDEV(cost) OVER (PARTITION BY code) AS std
  FROM po_line
) AS p;

结果：

+-----------------+-------+-----------+---------------------+--------------------+-------------+
| code            | cost  | avg       | std                 | num_std            | pct         |
+-----------------+-------+-----------+---------------------+--------------------+-------------+
| 00805           | 0.850 | 1.0166667 | 0.23570226039551592 | 0.7071069226079035 |  83.6065546 |
| 00805           | 0.850 | 1.0166667 | 0.23570226039551592 | 0.7071069226079035 |  83.6065546 |
| 00805           | 1.350 | 1.0166667 | 0.23570226039551592 | 1.4142134209517383 | 132.7868809 |
| gr309203 344400 | 0.770 | 0.8433333 |  0.0899382504215469 | 0.8153738777025533 |  91.3043514 |
| gr309203 344400 | 0.790 | 0.8433333 |  0.0899382504215469 | 0.5929990827042229 |  93.6758930 |
| gr309203 344400 | 0.970 | 0.8433333 |  0.0899382504215469 | 1.4083740722807512 | 115.0197674 |
| Water1          | 0.140 | 0.1566667 | 0.02357022603955158 | 0.7071081954001099 |  89.3616831 |
| Water1          | 0.190 | 0.1566667 | 0.02357022603955158 | 1.4142121481595331 | 121.2765699 |
| Water1          | 0.140 | 0.1566667 | 0.02357022603955158 | 0.7071081954001099 |  89.3616831 |
+-----------------+-------+-----------+---------------------+--------------------+-------------+

(Note窗口函数需要MySQL 8.0）。
该查询仅显示如何进行计算。要获得所需的结果：

SELECT code, fk_product_id, cost, ROUND(cost*100/avg) AS pct
FROM (
  SELECT code, cost, fk_product_id,
    AVG(cost) OVER (PARTITION BY code) AS avg,
    STDDEV(cost) OVER (PARTITION BY code) AS std
  FROM po_line
) AS p
WHERE ABS(cost-avg)/std > 1;

+-----------------+---------------+-------+------+
| code            | fk_product_id | cost  | pct  |
+-----------------+---------------+-------+------+
| 00805           |          1367 | 1.350 |  133 |
| gr309203 344400 |          1024 | 0.970 |  115 |
| Water1          |           344 | 0.190 |  121 |
+-----------------+---------------+-------+------+

对于MySQL，我需要一个报告，其中包含与平均值的标准差大于1的行(百分比

1条答案

相关问题

热门标签

最新问答