Une façon simple de calculer la médiane avec MySQL

Question

Quelle est la méthode la plus simple (et, espérons-le, pas trop lente) pour calculer la médiane avec MySQL ? J'ai utilisé AVG(x) pour trouver la moyenne, mais j'ai du mal à trouver un moyen simple de calculer la médiane. Pour l'instant, je renvoie toutes les lignes à PHP, je fais un tri, puis je choisis la ligne du milieu, mais il doit bien y avoir un moyen simple de le faire en une seule requête MySQL.

Exemple de données :

id | val
--------
 1    4
 2    7
 3    2
 4    2
 5    9
 6    8
 7    3

Triage sur val donne 2 2 3 4 7 8 9 donc la médiane devrait être 4 contre SELECT AVG(val) qui == 5 .

Answer 1

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 souligne qu'après la première passe, @rownum contiendra le nombre total de lignes. Ceci peut être utilisé pour déterminer la médiane, donc aucune seconde passe ou jointure n'est nécessaire.

Aussi AVG(dd.val) y dd.row_number IN(...) est utilisé pour produire correctement une 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+ contient une fonction MEDIAN

Answer 2

J'ai juste J'ai trouvé une autre réponse en ligne dans les commentaires :

Pour les médianes dans presque tous les SQL :

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

Assurez-vous que vos colonnes sont bien indexées et que l'index est utilisé pour le filtrage et le tri. Vérifiez avec les plans 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) .

Ensuite, choisissez-le dans la liste :

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

Vous devriez obtenir une ligne contenant uniquement la valeur que vous souhaitez.

Jacob

Answer 3

J'ai constaté que la solution acceptée ne fonctionnait pas sur mon installation MySQL, renvoyant un ensemble vide, mais cette requête a fonctionné pour moi dans toutes les situations où je l'ai testée :

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5
LIMIT 1

Answer 4

Malheureusement, ni les réponses de TheJacobTaylor ni celles de velcrow ne donnent de résultats précis pour les versions actuelles de MySQL.

La réponse de Velcro ci-dessus est proche, mais elle ne calcule pas correctement pour les ensembles de résultats avec un nombre pair de lignes. Les médianes sont définies comme étant soit 1) le chiffre du milieu pour les ensembles à numéros impairs, soit 2) la moyenne des deux chiffres du milieu pour les ensembles à numéros pairs.

Voici donc la solution de Velcro, adaptée aux nombres pairs et impairs :

SELECT AVG(middle_values) AS 'median' FROM (
  SELECT t1.median_column AS 'middle_values' FROM
    (
      SELECT @row:=@row+1 as `row`, x.median_column
      FROM median_table AS x, (SELECT @row:=0) AS r
      WHERE 1
      -- put some where clause here
      ORDER BY x.median_column
    ) AS t1,
    (
      SELECT COUNT(*) as 'count'
      FROM median_table x
      WHERE 1
      -- put same where clause here
    ) AS t2
    -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
    WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;

Pour l'utiliser, suivez ces 3 étapes simples :

1. Remplacez "median_table" (2 occurrences) dans le code ci-dessus par le nom de votre table
2. Remplacez "median_column" (3 occurrences) par le nom de la colonne pour laquelle vous souhaitez trouver une médiane.
3. Si vous avez une condition "WHERE", remplacez "WHERE 1" (2 occurrences) par votre condition "where".

Answer 5

Un commentaire sur cette page dans la documentation MySQL a la suggestion suivante :

-- (mostly) High Performance scaling MEDIAN function per group
-- Median defined in http://en.wikipedia.org/wiki/Median
--
-- by Peter Hlavac
-- 06.11.2008
--
-- Example Table:

DROP table if exists table_median;
CREATE TABLE table_median (id INTEGER(11),val INTEGER(11));
COMMIT;

INSERT INTO table_median (id, val) VALUES
(1, 7), (1, 4), (1, 5), (1, 1), (1, 8), (1, 3), (1, 6),
(2, 4),
(3, 5), (3, 2),
(4, 5), (4, 12), (4, 1), (4, 7);

-- Calculating the MEDIAN
SELECT @a := 0;
SELECT
id,
AVG(val) AS MEDIAN
FROM (
SELECT
id,
val
FROM (
SELECT
-- Create an index n for every id
@a := (@a + 1) mod o.c AS shifted_n,
IF(@a mod o.c=0, o.c, @a) AS n,
o.id,
o.val,
-- the number of elements for every id
o.c
FROM (
SELECT
t_o.id,
val,
c
FROM
table_median t_o INNER JOIN
(SELECT
id,
COUNT(1) AS c
FROM
table_median
GROUP BY
id
) t2
ON (t2.id = t_o.id)
ORDER BY
t_o.id,val
) o
) a
WHERE
IF(
-- if there is an even number of elements
-- take the lower and the upper median
-- and use AVG(lower,upper)
c MOD 2 = 0,
n = c DIV 2 OR n = (c DIV 2)+1,

-- if its an odd number of elements
-- take the first if its only one element
-- or take the one in the middle
IF(
c = 1,
n = 1,
n = c DIV 2 + 1
)
)
) a
GROUP BY
id;

-- Explanation:
-- The Statement creates a helper table like
--
-- n id val count
-- ----------------
-- 1, 1, 1, 7
-- 2, 1, 3, 7
-- 3, 1, 4, 7
-- 4, 1, 5, 7
-- 5, 1, 6, 7
-- 6, 1, 7, 7
-- 7, 1, 8, 7
--
-- 1, 2, 4, 1

-- 1, 3, 2, 2
-- 2, 3, 5, 2
--
-- 1, 4, 1, 4
-- 2, 4, 5, 4
-- 3, 4, 7, 4
-- 4, 4, 12, 4

-- from there we can select the n-th element on the position: count div 2 + 1