Discussion :

[yuekerobero]

Bonjour à tous,

Cela fait un petit moment que j'essaye de calculer la médiane de chaque élément de ma table... Mais j'ai beau effectuer des recherches sur le net ou sur le site de mysql je ne trouve pas de solution à mon besoin.
Pourriez-vous m'aider SVP ?

Si prends l'exemple suivant :
J'ai une seule table contenant 2 champs. Un champ Nom_élève et un autre champ Note. La table contient toutes les notes des éléves sur l'ensemble de l'année.
Je veux calculer la médiane des notes pour chacun des éléves.

Comment dois-je faire ?

Merci pour votre aide.

[Cybher]

salut,

tu peux donner un petit exemple avec un jeu d'essai et le résultat attendu?

[Antoun]

voir http://sqlpro.developpez.com/optimisation/mediane/

[yuekerobero]

Vous trouverez ci-joint un exemple de table citant mon exemple ci-dessus.
Je souhaiterai obtenir la médiane de chaque élève indépendamment (celle de Luc, Michel, Marie...etc).
A ne pas confondre : Je ne veux pas obtenir la médiane de toute ma table.

salut,

tu peux donner un petit exemple avec un jeu d'essai et le résultat attendu?

[Maljuna Kris]

Saluton,
Statistiquement parlant la médiane d'une note, ça n'a pas de sens.
La médiane est la résultante d'un décompte statistique appliqué à une série de valeurs (de notes en l'occurrence). Si la série se limite à une note, la note est la médiane d'elle-même et pour toutes les notes d'une série la médiane sera la même puisqu'il s'agit de la médiane de la série.
Mais l'article d'SQLPro, explique tout cela, et bien d'autres choses encore, mieux que je ne me risquerais à le faire.

[yuekerobero]

Je suis entièrement d'accord avec toi Maljuna Kris... Mais justement, je cherche a déterminer la médiane des notes pour chaque élève. Et à chaque solution qui m'est proposé c'est toujours la médiane de la table entière. Or ce n'est pas ce que je cherche. Donc si qlqu'un pouvait m'aider à solutionner mon besoin ça serait génial car là je m'en sors vraiment pas.
Merci d'avance.

Saluton,
Statistiquement parlant la médiane d'une note, ça n'a pas de sens.
La médiane est la résultante d'un décompte statistique appliqué à une série de valeurs (de notes en l'occurrence). Si la série se limite à une note, la note est la médiane d'elle-même et pour toutes les notes d'une série la médiane sera la même puisqu'il s'agit de la médiane de la série.
Mais l'article d'SQLPro, explique tout cela, et bien d'autres choses encore, mieux que je ne me risquerais à le faire.

[Maljuna Kris]

Mon message s'en tenait au titre initial de ton post :

calcul de la médiane de chaque élément d'une table

Là c'est plus clair mais aussi plus ardu.
Je vais me pencher sur la littérature (principalement Joe Celko) pour voir ce qui se présente. Mais je suis pessimiste quand je vois comment SQLPro a disserté sur le sujet de la médiane d'une table.
L'idée serait d'implémenter une sorte de 'GROUP BY eleve' sur une des solutions proposées.

La différence entre un pessimiste et un déçu, c'est que ce dernier a des preuves. Jacques DUTRONC

[Maljuna Kris]

Rien ne sert d'espérer pour entreprendre ni de réussir pour persévérer

J'ai reformulé la définition de la médiane d'une liste comme suit :

l'occurence de la liste pour laquelle la différence entre les effectifs qui lui sont supérieurs et les effectifs qui lui sont inférieurs est la plus faible

Fort dequoi je me suis créé la toute petite base de test suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
CREATE TABLE `eleves` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `nom` char(15) character set latin1 collate latin1_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
 
-- 
-- Contenu de la table `eleves`
-- 
 
INSERT INTO `eleves` (`id`, `nom`) VALUES 
(1, 0x6d616368696e),
(2, 0x74727563);
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `notes`
-- 
 
CREATE TABLE `notes` (
  `eleve` int(10) unsigned NOT NULL,
  `note` int(10) unsigned default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `notes`
-- 
 
INSERT INTO `notes` (`eleve`, `note`) VALUES 
(1, 5),
(2, 5),
(1, 6),
(2, 5),
(1, 3),
(2, 7),
(1, 6),
(1, 8);

A laquelle j'ai appliqué la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT DISTINCT nom,n.note,
(SELECT COUNT(n2.note) FROM notes n2 
    WHERE n.note<=n2.note AND n2.eleve=e.id )-1 pliegaj,
(SELECT COUNT(n3.note) FROM notes n3 
    WHERE n.note>=n3.note AND n3.eleve=e.id )-1 plietaj
FROM `notes` n
JOIN `eleves` e ON e.id=n.eleve
HAVING ABS(pliegaj-plietaj) BETWEEN 0 AND 1

Et qui retourne les résultats suivants :

'nom','note','pliegaj','plietaj'
'truc','5','2','1'
'machin','6','2','3'

Je me suis immodestement largement écarté des chemins empruntés par les experts, mais je crois bien tenir là un début de piste.
Je vais maintenant fermer tous ces bouquins et laisser décanter tout cela jusqu'à ce qu'il fasse jour.
Bonan nokton kaj ĝis revido.

[yuekerobero]

C'est super gentil de ta part de prendre du temps pour m'aider a trouver une solution. Personnellement, j'ai sincèrement pas vraiment compris tes résultats mais je vais attendre patiemment que tu progresses dans ta réflexion et que tu m'expliques tout cela.
Espérant fortement que tu ou que quelqu'un arrive à me trouver une solution... Merci d'avance à tous pour vos aides ! J'attends donc vos propositions...

Je crois bien tenir là un début de piste.
Je vais maintenant fermer tous ces bouquins et laisser décanter tout cela jusqu'à ce qu'il fasse jour.

[Maljuna Kris]

De nove Saluton,
Après quelques relectures de Celko, je me suis rabattu sur du php pour pouvoir te proposer une solution.
J'ai travaillé avec cette micro-base de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CREATE TABLE `eleves` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `nom` char(15) character set latin1 collate latin1_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
-- 
-- Contenu de la table `eleves`
-- 
 
INSERT INTO `eleves` (`id`, `nom`) VALUES 
(1, 0x6d616368696e),
(2, 0x74727563),
(3, 0x626964756c65);
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `notes`
-- 
 
CREATE TABLE `notes` (
  `eleve` int(10) unsigned NOT NULL,
  `note` int(10) unsigned default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `notes`
-- 
 
INSERT INTO `notes` (`eleve`, `note`) VALUES 
(1, 5),
(2, 5),
(1, 6),
(2, 5),
(1, 3),
(2, 7),
(1, 6),
(1, 8),
(2, 9),
(3, 1),
(3, 2),
(3, 4),
(3, 5),
(3, 12);

et voici le script

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?php
$base='mediane';
require 'connexion.inc.php';
$sql="SELECT e.nom,
GROUP_CONCAT(n.note ORDER BY n.note ASC SEPARATOR ';') liste
FROM eleves e INNER JOIN notes n ON e.id=n.eleve
GROUP BY e.id";
$res=mysql_query($sql) or die($sql.'<br />'.mysql_error());
?>
<?php
function mediane($liste){
    $notes=explode(';',$liste);
    $milieu=floor(count($notes)/2);
    if((count($notes))%2==0){
        return ($notes[$milieu]+$notes[$milieu-1])/2;//médiane financière
        }
    else{return $notes[$milieu];}
    }
function moyenne($liste){
    $notes=explode(';',$liste);
    return array_sum($notes)/count($notes);
    }
while($ligne=mysql_fetch_assoc($res)){
    echo $ligne['nom']."\t".$ligne['liste']."\t mediane : " .
              mediane($ligne['liste']) .
              "\tmoyenne : ".moyenne($ligne['liste']).'<br />';
    }
?>

et, enfin, le résultat

machin 3;5;6;6;8 mediane : 6 moyenne : 5.6
truc 5;5;7;9 mediane : 6 moyenne : 6.5
bidule 1;2;4;5;12 mediane : 4 moyenne : 4.8

Bien sur, cette solution ne règle pas le problème des éventuelles valeurs NULL qui seraient présentes dans la série. Ni des cas de polulation de notes très très mal réparties dans une série, genre{1,2,1,1,2,1,2,1,1,1,12} qui retournera une médiane de 1, dont on se demande bien ce qu'elle peut représenter.

[yuekerobero]

Merci énormément Maljuna Kris, je n'ai pas encore tester ta solution mais il semble que c'est en bonne voie.

Cela étant, je reste un peu bête sur ta solution. Je suis un peu novice sur le sujet. Et pourrais-tu m'expliquer comment tester le code PHP sur mon poste car je ne sais pas du tout comment faire ? Pourrais-tu m'expliquer la procédure ?

Il est vrai qu'à l'origine, je ne voulais pas trop m'aventurer sur une solution en PHP mais ne rester qu'en requête SQL. Serait-ce trop compliqué à instancier ?
Si tu estimes que cette solution est la meilleur alors j'essayerai au mieux de m'y adapter.

Merci encore pour tout.
J'attends tes réponses.

Cdlt.

[Maljuna Kris]

Merci énormément Maljuna Kris, je n'ai pas encore tester ta solution mais il semble que c'est en bonne voie.

Ne dankinde.

Cela étant, je reste un peu bête sur ta solution. Je suis un peu novice sur le sujet. Et pourrais-tu m'expliquer comment tester le code PHP sur mon poste car je ne sais pas du tout comment faire ? Pourrais-tu m'expliquer la procédure ?

Ça ne me semble pas trop le lieu, il vaudrait mieux exposer cela sur un forum dédié à php (ils ont un excellent forum ici).

Il est vrai qu'à l'origine, je ne voulais pas trop m'aventurer sur une solution en PHP mais ne rester qu'en requête SQL. Serait-ce trop compliqué à instancier ?

En tout cas, ça me dépasse, en dépit des nombreuses et tardives lectures et recherches que j'ai menées sur le sujet ces derniers temps. D'autant que, au final, la solution en php sest avérée toute bête.