Pour MySQL, j'ai besoin d'un rapport des lignes qui s'écartent de plus d'un écart type par rapport à la moyenne (exprimée en pourcentage)

Question

J'essaie d'écrire une requête MySQL pour compter les lignes qui sont significativement différentes de la ligne la plus courante pour chaque produit et signaler la différence en pourcentage, avec moins de 100 % ayant un prix inférieur au prix moyen de ce produit et plus de 100 % ayant un prix inférieur élevé. Les prix inférieurs à 1 écart type par rapport à la moyenne sont ignorés. Exemple de données : _rowid_timestamp code fk_product_idfk_po_id coût 59522021-01-1010:19:010080513675430.85094032022-05-2314:54:3400

P粉141911244 · Answer

Cette requête montre comment utiliser les fonctions de fenêtre pour calculer le nombre d'écarts types et le coût en pourcentage du coût moyen pour chaque code donné.

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;

Résultat :

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

(Veuillez noter que les fonctions de fenêtre nécessitent MySQL 8.0).

Cette requête vous montre uniquement comment le calcul est effectué. Pour obtenir les résultats souhaités :

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

_rowid	_Horodatage	Code	fk_product_id	fk_po_id	Coût
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	Eau 1	344	3153	0,140
40134	2022-04-06 22:39:34	Eau 1	344	2747	0,190
41676	2022-12-09 16:28:28	Eau 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