Für MySQL benötige ich einen Bericht über Zeilen, die um mehr als eine Standardabweichung vom Mittelwert (ausgedrückt als Prozentsatz) abweichen.

Question

Ich versuche, eine MySQL-Abfrage zu schreiben, um Zeilen zu zählen, die sich deutlich von der häufigsten Zeile für jedes Produkt unterscheiden, und um die prozentuale Differenz zu melden, wobei unter 100 % der Preis unter dem Durchschnittspreis für dieses Produkt und über 100 % höher liegt . Preise, die weniger als 1 Standardabweichung vom Mittelwert betragen, werden ignoriert.

Beispieldaten:

_rowid	_Zeitstempel	Code	fk_product_id	fk_po_id	Kosten
5952	2021-01-10 10:19:01	00805	1367	543	0,850
9403	23.05.2022 14:54:34	00805	1367	2942	0,850
41595	23.11.2022 11:20:26	00805	1367	3391	1.350
39635	2022-01-18 12:49:32	Wasser 1	344	3153	0,140
40134	2022-04-06 22:39:34	Wasser 1	344	2747	0,190
41676	2022-12-09 16:28:28	Wasser 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

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;

Dadurch werden keine Zeilen zurückgegeben, aber im Bericht sollten drei Zeilen (eine für jedes Produkt) erscheinen.

Das erwartete Ergebnis sollte sein:

Code	fk_product_id	Kosten	Prozentsatz
00805	1367	1.350	133
Wasser 1	344	0,190	121
gr309203 344400	1024	0,970	115

Answer 1

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

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