Un moyen simple de calculer la médiane avec MySQL

Question

Quel est le moyen le plus simple (pas trop lent, espérons-le) de calculer la médiane à l'aide de MySQL ? J'utilise AVG(x) pour trouver la moyenne, mais j'ai du mal à trouver un moyen simple de calculer la médiane. À l'heure actuelle, je renvoie toutes les lignes à PHP, je trie, puis je sélectionne les lignes du milieu, mais il doit exister un moyen simple de le faire dans une seule requête MySQL.

Exemples de données :

id val |
---------
 1 4
 2 7
 3 2
 4 2
 5 9
 6 8
 73≪/pré>
Trier val donne 2 2 3 4 7 8 9, donc la médiane doit être 4, et  ;SELECT AVG(val) == 5. 

Answer 1

Je viens de trouver une autre réponse en ligne dans les commentaires :

Assurez-vous que vos colonnes sont bien indexées et que les index sont utilisés pour le filtrage et le tri. Validez avec le plan d'explication.

select count(*) from table --find the number of rows

Calculez le numéro de ligne « médian ». Peut-être utiliser : median_row = Floor(count / 2).

Puis sélectionnez-le dans la liste :

select val from table order by val asc limit median_row,1

Cela devrait renvoyer une ligne avec les valeurs souhaitées.

Answer 2

Dans MariaDB / MySQL :

SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM data d, (SELECT @rownum:=0) r
  WHERE d.val is NOT NULL
  -- put some where clause here
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

Steve Cohen a souligné qu'après le premier passage, @rownum contiendra le nombre total de lignes. Cela peut être utilisé pour déterminer la médiane, donc aucune seconde passe ou concaténation n’est requise.

De plus, AVG(dd.val) 和 dd.row_number IN(...) est utilisé pour générer correctement la médiane lorsqu'il y a un nombre pair d'enregistrements. Raisonnement :

SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3

Enfin, MariaDB 10.3.3+ inclut la fonction MEDIAN