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, où en dessous de 100 % le prix est inférieur au prix moyen de ce produit et supérieur à 100 % . Les prix inférieurs à 1 écart type par rapport à la moyenne sont ignorés.
Exemples de données :
| _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 |
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;
Cela ne renverra aucune ligne, mais trois lignes (une pour chaque produit) devraient apparaître dans le rapport.
Le résultat attendu devrait être :
| Code | fk_product_id | Coût | Pourcentage |
|---|---|---|---|
| 00805 | 1367 | 1.350 | 133 |
| Eau 1 | 344 | 0,190 | 121 |
| gr309203 344400 | 1024 | 0.970 | 115 |
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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 :
(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 | +-----------------+---------------+-------+------+