Calculer le Centile XX avec MySQL

Bonjour,

MySQL

Aujourd’hui nous revenons sur MySQL avec un article décrivant la méthode et la logique nécessaire au calcul du centile d’un échantillon de données grâce à MySQL, de deux manières différentes (qui se rejoignent).

La première méthode permet de calculer le centile XX en deux requêtes MySQL alors que la deuxième, basée sur le même principe, permet de le faire en une seule requête. Je tiens cependant à vous sensibiliser sur les temps de calcul qui peuvent être assez long en fonction de la taille de la table et des filtres/groupement appliqués.

Si vous ne savez pas très bien ce qu’est le centile, je vous conseille de lire cela.

Première méthode : grâce à deux requêtes 

Partons du principe que nous voulons calculer le « centile 99 » d’un temps de réponse stocké dans la colonne « value » de la table response_time. Nous allons d’abord récupérer le nombre de valeurs à traiter :

mysql>Select count(*) from response_time where date > xx ...
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

Nous voulons donc calculer le centile 99 sur 100 000 valeurs. Nous pouvons donc dire que 100 000 valeurs représente 100%, ce qui veut dire que 100 000/100 = 1 000 représente 1% de mes valeurs. Si je range dans l’ordre décroissant et que je prends les 1 000 premières valeurs, j’aurai donc les 1000 valeurs les plus élevées, en récupérant la millième valeur, je récupèrerai donc la valeur pour laquelle 99% des valeurs sont inférieures, vous commencez à voir 🙂 ?

La dernière étape consiste donc à récupérer, dans notre cas, la millième valeur :

mysql>select value from response_time where date > ... order by value desc limit 1000,1;
+----------------+
| response_time  |
+----------------+
|   80           |
+----------------+
1 row in set (0.00 sec)

Nous savons donc maintenant que 99% des temps de réponse sont inférieurs à 80ms. Dans notre cas, si je souhaitais récupérer le 95 centile, j’aurai fait cela :

mysql>select value from response_time where date > ... order by value desc limit 5000,1;
+----------------+
| response_time  |
+----------------+
|   110          |
+----------------+
1 row in set (0.00 sec)

Deuxième méthode : en une seule requête

Cette méthode est juste la mise en pratique des deux requêtes précédente en une seule grâce à deux fonctions. Cette requête semble un peu complexe de prime abord mais en fait elle s’avère assez simple à comprendre :

SELECT
 CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
  GROUP_CONCAT(value ORDER BY value SEPARATOR ','),
   ',', 99/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `99th Per`
 FROM response_time;

On applique la fonction group_concat sur la colonne puis on récupère 99% des valeurs grâce au substring_index se basant sur 99/100 * COUNT(*), autour du groupe concat et enfin on récupère la dernière valeur grâce au substring_index entourant le tout. Enfin on convertit le résultat en décimal. Il suffit de remplacer « 99 » par le centile souhaité.

Très important : veillez à augmenter la taille de la variable group_concat_max_len afin d’être sûr que le group_contact puisse contenir toutes les valeurs nécessaires. De plus, suivant les valeurs récupérées, il faudra peut être préciser des paramètres pour DECIMAL(X,Y).

Attention aux temps de réponse de cette requête sur une table conséquente surtout si vous filtrez/groupez le résultat.

A bientôt!

PS: Si le contenu de cet article vous semble erroné ou pas assez précis, merci de me le signaler dans les commentaires et ou par mail à contact@etiennegautier.net

Tags: , , , , ,

1 comment

  1. Bonjour,

    Sur les séries comportant un nombre pair de valeur, le calcul de la médiane (donc du CENTILE50) renverra un résultat faux car dans ce cas il n’y a pas de valeur « au milieu », c’est-à-dire qui sépare 2 groupes de valeur ayant le même nombre d’occurrence.
    Le code ci-dessous devrait fonctionner:
    SET @rowindex := -1;

    SELECT
    AVG(g.value )
    FROM
    (SELECT @rowindex:=@rowindex + 1 AS rowindex,
    response_time.value AS value
    FROM response_time
    ORDER BY response_time.value ) AS g
    WHERE
    g.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2));

    Maintenant sur une table de 100 000 valeurs, la différence doit être négligeable, mais sur des séries plus petites elle peut être conséquente.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.