对于 MySQL，我需要一份与平均值偏差超过 1 个标准差的行报告（以百分比表示）

Question

我正在尝试编写一个MySQL查询来计算与每种产品最常见的行有很大不同的行，并报告百分比差异，其中低于100%的价格低于该产品的平均价格，超过100%的价格较高。忽略与平均值小于1个标准差的价格。示例数据：_rowid_时间戳代码fk_product_idfk_po_id成本59522021-01-1010:19:010080513675430.85094032022-05-2314:54:3400

P粉141911244 · Answer

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

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 |
+-----------------+-------+-----------+---------------------+--------------------+-------------+

（请注意，窗口函数需要 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 |
+-----------------+---------------+-------+------+

php8，我来也

30分钟学会网站布局

尚观Oracle入门到精通视频教程

你的第一行 UNI-APP 代码

Flutter 从头到应用启动

兄弟连Linux新版视频教程

AXURE 9视频教程（适合产品经理交互产品设计UI）

零基础PS视频教程

16天带你入门UI视频教程

PS技巧和切片技巧视频教程

阿里云环境搭建以及项目上线视频教程

计算机网络概述——程序员必须掌握的基础知识

程序员必备教程——HTTP协议讲解

Websocket视频教程

_rowid	_时间戳	代码	fk_product_id	fk_po_id	成本
5952	2021-01-10 10:19:01	00805	1367	543	0.850
9403	2022-05-23 14:54:34	00805	1367	2942	0.850
41595	2022-11-23 11:20:26	00805	1367	3391	1.350
39635	2022-01-18 12:49:32	水1	344	3153	0.140
40134	2022-04-06 22:39:34	水1	344	2747	0.190
41676	2022-12-09 16:28:28	水1	344	3398	0.140
39634	2022-01-18 12:49:31	gr309203 344400	1024	3154	0.770
35634	2021-03-03 15:23:23	gr309203 344400	1024	3203	0.790
41264	2022-11-16 11:41:44	gr309203 344400	1024	3357	0.970