Für MySQL benötige ich einen Bericht über Zeilen, die um mehr als eine Standardabweichung vom Mittelwert (ausgedrückt als Prozentsatz) abweichen.-Fragen und Antworten zum chinesischen PHP-Netzwerk

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

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

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