MySQL - Calculer les rangs centiles à la volée

Question

J'ai une requête MySQL SELECT qui utilise 20 comparaisons différentes dans la même table. Voici un exemple :

SELECT * FROM mytable
WHERE (col1 > (col2 * 0.25))
AND (col5 < col10) .......

J'essaie de calculer les rangs centiles en fonction de l'ordre d'une colonne appelée SCORE au sein de la SELECT résultats retournés. J'ai essayé d'utiliser des numéros de rangée incrémentiels et COUNT(*) pour obtenir le rang de l'action et le nombre total de résultats retournés, mais je ne sais pas comment attribuer le même rang lorsque certains des résultats ont la même valeur. SCORE .

Voici la formule que j'essaie de calculer :

((COUNT(lower scores) + (COUNT(same/tied scores) / 2)) * 100) / COUNT(total results)

Comment puis-je trouver le nombre de notes inférieures, de notes identiques/liées et de notes totales dans la même ligne de résultats pour calculer les percentiles à la volée ?

J'essaie d'éviter d'utiliser des procédures stockées car je veux que les administrateurs de mon application puissent adapter l'instruction SELECT dans la zone d'administration de mes applications, selon les besoins.

Answer 1

En utilisant le code de Shlomi ci-dessus, voici le code que j'ai trouvé pour calculer les rangs centiles (au cas où quelqu'un voudrait les calculer à l'avenir) :

SELECT 
    c.id, c.score, ROUND(((@rank - rank) / @rank) * 100, 2) AS percentile_rank
FROM
    (SELECT 
    *,
        @prev:=@curr,
        @curr:=a.score,
        @rank:=IF(@prev = @curr, @rank, @rank + 1) AS rank
    FROM
        (SELECT id, score FROM mytable) AS a,
        (SELECT @curr:= null, @prev:= null, @rank:= 0) AS b
ORDER BY score DESC) AS c;

Answer 2

Voici un post (de moi) qui explique le classement pendant SELECT : SQL : Classement sans Self Join .

Il utilise des variables définies par l'utilisateur auxquelles on accède et qui sont assignées même lorsque les lignes sont itérées.

En utilisant la même logique, il pourrait être étendu pour inclure les nombres de scores totaux, de scores distincts, etc. En guise d'aperçu, voici une requête typique :

SELECT
  score_id, student_name, score,
  @prev := @curr,
  @curr := score,
  @rank := IF(@prev = @curr, @rank, @rank+1) AS rank
FROM
  score,
  (SELECT @curr := null, @prev := null, @rank := 0) sel1
ORDER BY score DESC
;

Answer 3

Les réponses de Shlomi et de Zishan (qui utilise le code de Shlomi) ne donnent absolument pas de résultats précis, comme je l'ai découvert en examinant les résultats d'un de mes grands tableaux. Comme répondu ailleurs, il est apparemment impossible de calculer les rangs centiles dans une seule requête MySQL : Rang percentile SQL

L'approche de Shlomi Noach, qui utilise des variables définies par l'utilisateur, est la suivante - à première vue comme si cela fonctionnait bien pour les deux premiers pourcentages de classement, mais cela dégénère rapidement pour les rangs inférieurs de votre tableau. Regardez les résultats de vos données par vous-même, comme je l'ai fait.

Voir ce billet de Roland Bouman qui explique pourquoi l'approche de Shlomi utilisant des variables définies par l'utilisateur dans une seule instruction SQL ne fonctionne pas, avec une proposition de meilleure solution :

http://rpbouman.blogspot.com/2009/09/mysql-another-ranking-trick.html

J'ai donc adapté le code de Bouman à cette fin et voici ma solution, qui combine nécessairement PHP et MySQL :

Étape 1) Calculez et stockez le rang absolu pour chaque ligne en soumettant les deux requêtes suivantes :

SET @@group_concat_max_len := @@max_allowed_packet;

UPDATE mytable INNER JOIN (SELECT ID, FIND_IN_SET(
    score,
        (SELECT GROUP_CONCAT(
             DISTINCT score
             ORDER BY score  DESC
            )
        FROM mytable)
        ) AS rank
FROM mytable) AS a
ON mytable.ID=a.ID
SET mytable.rank = rank;

Étape 2 : Récupérer le nombre total de lignes (et stocker le résultat dans une variable PHP $total)

SELECT COUNT(ID) FROM mytable

Étape 3 : Utilisez une boucle PHP pour itérer dans le tableau et utiliser le rang absolu de chaque ligne pour calculer le rang centile de la ligne :

3a) Boucle à travers :

SELECT ID, rank FROM mytable

tout en stockant ces valeurs de ligne comme $ID et $rank en PHP

3b) Pour chaque ligne exécutée :

$sql = 'UPDATE mytable INNER JOIN (
            SELECT (100*COUNT(ID)/'.$total.') percentile
            FROM mytable
            WHERE rank >= '.$rank.'
        ) a 
        ON mytable.ID = a.ID
        WHERE mytable.ID='.$ID.'
        SET mytable.percentile = a.percentile';

Ce n'est probablement pas le processus le plus efficace, mais il est certainement précis, et comme dans mon cas la valeur du "score" n'est pas mise à jour très souvent, j'exécute le script ci-dessus comme une opération batch cron pour maintenir à jour les rangs centiles.