Comment calculer la médiane d'un ensemble de données dans MySQL

Pour obtenir la médiane d'un ensemble de données (comme le revenu médian d'une certaine région ou d'une certaine entreprise), nous devons généralement subdiviser cette tâche en 3 petites tâches :

1 . données et donnez à chaque ligne de données son classement parmi toutes les données ;

Trouvez le numéro de classement médian

3. Trouvez la valeur de classement intermédiaire correspondante ; prend comme exemple le revenu mensuel des employés d'une entreprise pour illustrer l'utilisation de certaines instructions MySQL complexes.

Méthode 1

Créer une table de test

Créez d'abord une table de revenus. L'instruction de création de table est :

CREATE TABLE IF NOT EXISTS `employee` (
  `id`     INT                  AUTO_INCREMENT PRIMARY KEY,
  `name`   VARCHAR(10) NOT NULL DEFAULT '',
  `income` INT         NOT NULL DEFAULT '0'
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
INSERT INTO `employee` (`name`, `income`)
VALUES ('麻子', 20000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('李四', 12000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('张三', 10000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('王二', 16000);
INSERT INTO `employee` (`name`, `income`)
VALUES ('土豪', 40000);

Terminer la tâche 1

Trier les données et donner à chaque ligne de données son classement parmi toutes les données :

SELECT t1.name, t1.income, COUNT(*) AS rank
FROM employee AS t1,
     employee AS t2
WHERE t1.income < t2.income
   OR (t1.income = t2.income AND t1.name <= t2.name)
GROUP BY t1.name, t1.income
ORDER BY rank;

Le résultat de la requête est :

Terminer la petite tâche 2

Trouver le numéro de classement médian :

SELECT (COUNT(*) + 1) DIV 2 as rank
FROM employee;

Le résultat de la requête est :

Terminer la petite tâche 3

SELECT income AS median
FROM (SELECT t1.name, t1.income, COUNT(*) AS rank
      FROM employee AS t1,
           employee AS t2
      WHERE t1.income < t2.income
         OR (t1.income = t2.income AND t1.name <= t2.name)
      GROUP BY t1.name, t1.income
      ORDER BY rank) t3
WHERE rank = (SELECT (COUNT(*) + 1) DIV 2 FROM employee)

Le résultat de la requête est :

À ce À ce stade, nous avons trouvé comment obtenir la médiane à partir d’un ensemble de données.

Méthode 2

Ce qui suit est une introduction à une autre méthode d'optimisation des déclarations de classement.

Nous savons tous comment trier un ensemble de données. Dans cet exemple, la méthode d'implémentation est la suivante :

SELECT name, income
FROM employee
ORDER BY income DESC

Le résultat de la requête est :

Pouvons-nous aller plus loin et ajouter une colonne aux résultats de la requête ? Les données de cette colonne constituent le classement ?

Nous pouvons atteindre cet objectif grâce à 3 variables personnalisées :

La première variable est utilisée pour enregistrer le revenu de la ligne de données actuelle

La deuxième variable utilisée pour enregistrer le revenu de la ligne de données précédente

La troisième variable est utilisée pour enregistrer le classement de la ligne de données actuelle

SET @curr_income := 0;
SET @prev_income := 0;
SET @rank := 0;
SELECT `name`,
       @curr_income := income                                      AS income,
       @rank := if(@prev_income != @curr_income, @rank + 1, @rank) AS rank,
       @prev_income := @curr_income                                AS dummy
FROM employee
ORDER BY income DESC

Les résultats de la requête sont les suivants :

Trouvez ensuite le numéro de classement de la médiane et trouvez ensuite le revenu médian :

SET @curr_income := 0;
SET @prev_income := 0;
SET @rank := 0;
SELECT income AS median
FROM (SELECT `name`,
             @curr_income := income                                      AS income,
             @rank := if(@prev_income != @curr_income, @rank + 1, @rank) AS rank,
             @prev_income := @curr_income                                AS dummy
      FROM employee
      ORDER BY income DESC) AS t1
WHERE t1.rank = (SELECT (COUNT(*) + 1) DIV 2 FROM employee)

Le résultat de la requête est :

À ce point, nous avons trouvé deux façons de résoudre le problème médian. Saupoudrer de fleurs.

Recommandé : "

Tutoriel mysql

"

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!