Calcul de la médiane

Bonjour,

J'aimerai savoir s'il est possible de calculer de manière simple la médiane d'un jeu de valeurs à l'aide d'SQL Server 2000, soit en transac-sql directement, soit avec Reporting Services. J'ai parcouru la msdn sans rien trouver, sauf une fonction Median pour Analysis Services, mais je ne sais pas comment m'en servir :(

J'ai toujours en dernier recours la solution de calculer la médiane en C# et d'intégrer ce code à Reporting Services, mais j'aimerai mieux utiliser les fonctions qui sont dans la boîte ... je trouve étonnant de ne pas avoir la médiane alors que les autres fontions statistiques sont présentes (Max, Min, Avg, StDev, ...)

merci d'avance pour vos réponses :)

NB : j'ai fais une recherche sur le forum, je n'ai rien trouvé ... je m'excuse par avance si ce sujet avait déjà été traité et je vous remercie d'avance aussi de me rediriger vers le bon post :)

Création de la table

Code:

---

1 2 3 4 5 6 7

create table Stat (Caractere Smallint, Effectif smallint) insert into Stat values(1, 10) insert into Stat values(2, 15) insert into Stat values(3, 18) insert into Stat values(4, 12) insert into Stat values(5, 8) insert into Stat values(6, 7)

---

Le total des effectifs est 70.

Calcul de la médiane (je ne garantis pas les temps de réponses pour des grosses tables :wink: ) :

Code:

---

1 2 3 4 5

select * from (select a.Caractere, a.effectif, sum(b.Effectif) as Somme, sum(b.Effectif) - a.Effectif as Precedente from Stat a inner join Stat b on a.Caractere >= b.Caractere group by a.Caractere, a.Effectif) c where (select sum(Effectif)/2 from stat) between Precedente and Somme

---

Ta solution est totalement fausse !!!!

Voici les différents cas avec les problématiques à répondre :

1) les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre impaires :
Voici le jeu de données que j'ai utilisé pour ce cas :

Code:

---

1 2 3 4 5 6 7 8

CREATE TABLE T_STATISTIQUES_STT (STT_ID INT, STT_VALEUR FLOAT NOT NULL) INSERT INTO T_STATISTIQUES_STT VALUES (1, 22.0) INSERT INTO T_STATISTIQUES_STT VALUES (2, 27.5) INSERT INTO T_STATISTIQUES_STT VALUES (3, 22.5) INSERT INTO T_STATISTIQUES_STT VALUES (4, 24.0) INSERT INTO T_STATISTIQUES_STT VALUES (5, 23.0)

---

La solution étant :

Code:

---

1 2 3

STT_VALEUR ---------- 23.0

---

2) les données à calculées sont toutes différentes (il n'y a pas de doublon) et en nombre paires :
Rajoutons au jeu précédent la ligne :

Code:

---

INSERT INTO T_STATISTIQUES_STT VALUES (6, 23.5)

---

La solution devient :

Code:

---

1 2 3

STT_VALEUR ---------- 23.25

---

3) voyons ce qui se passe avec des valeurs identiques multiples et un nombre de lignes impair :
Rajoutons au jeu précédent les lignes :

Code:

---

1 2 3 4 5

INSERT INTO T_STATISTIQUES_STT VALUES (7, 22.0) INSERT INTO T_STATISTIQUES_STT VALUES (8, 22.0) INSERT INTO T_STATISTIQUES_STT VALUES (9, 22.0) INSERT INTO T_STATISTIQUES_STT VALUES (10, 22.0) INSERT INTO T_STATISTIQUES_STT VALUES (11, 22.0)

---

La solution devient :

Code:

---

1 2 3

STT_VALEUR ---------- 22.0

---

Bien entendu je vous laisse mijoter pour trouver la solution et ne vous répondrait qu'en mail privé !

A +

Citation:

---

Envoyé par SQLpro

Ta solution est totalement fausse !!!!

---

Avec ta solution calcule la médiane de la série statistique qui étudie le caractère :"Initiale du patronyme" en France !
comme il y a de forte chance que le calcul donne une valeur située entre deux lettres, par exemple entre L et M, que donne ton calcul ? La moyenne ces codes ASCII ?

Citation:

---

Envoyé par nazlurf

Bonjour,
J'ai toujours en dernier recours la solution de calculer la médiane en C# et d'intégrer ce code à Reporting Services, mais j'aimerai mieux utiliser les fonctions qui sont dans la boîte ... je trouve étonnant de ne pas avoir la médiane alors que les autres fontions statistiques sont présentes (Max, Min, Avg, StDev, ...)

---

Le langage SQL n'est pas doué pour faire des statistiques, il n'est pas fait pour ça.
Tu as dans le livre de Joe Celko : SQL Avancé - 2° edition tout un chapitre consacré aux statistiques, et en particulier aux différents calculs de la médiane.

Je ne les ai jamais regardé, mais si vraiment tu reste bloqué, je regarderai ce soir.

Perso, je prèfére utiliser des outils extérieurs pour faire tout mes traitements statistiques, et si en plus l'outil peu se connecter a mon serveur sql ce n'est que du bonheur (R pour ne pas le citer - attention la prise en main est ardu, si ce n'est que pour calculer une médiane).
--

Bon voici une solution pour le pb de la mediane.
C'est une de mes solutions préférées, maintenant je sais pourquoi je passe par des outils externe pour faire des stats :lol:
Cette solution fonctionne avec le jeu de données de SQLPro :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

CREATE VIEW Valeurs( poids,occurences) AS SELECT STT_VALEUR, COUNT(*) FROM T_STATISTIQUES_STT GROUP BY STT_VALEUR   CREATE VIEW MiddleValues( poids) AS SELECT poids FROM valeurs AS VS1 WHERE (SELECT SUM(VS2.occurences) / 2.0 + 0.25 FROM valeurs AS VS2) > (SELECT SUM(VS2.occurences) FROM valeurs AS VS2 WHERE VS1.poids <= VS2.poids) - VS1.occurences AND (SELECT SUM(VS2.occurences) / 2.0 + 0.25 FROM valeurs AS VS2) > (SELECT SUM(VS2.occurences) FROM valeurs AS VS2 WHERE VS1.poids >= VS2.poids) - VS1.occurences   SELECT AVG(poids) AS mediane FROM MiddleValues ;

---

Ce qui me plait dans cette solution, c'est qu'une fois que les 2 vues sont crées un simple select te donne la médiane.

La solution n'est pas de moi, même si j'aurai aimé :
Médiane de Vaughan avec View - SQl Avancé 2° Edition. - p 316

Bonjour,

Bien que la discussion date de quelques années, voici une autre solution pour le calcul de la médiane en utilisant les CTE (Common Table Expression) qui m'a été fort utile dans mon travail.

Supposons que l'on désire établir la médiane du salaire quotidien d'un groupe d'employé en fonction du département, du corps d'emploi et de la classe d'emploi.

Établissons d'abord le script pour extraire les données brutes dans une table temporaire, "#temp01".

Code:

---

1 2 3 4 5 6 7 8 9

SELECT Emploi.Departement, Personne.Nom, Personne.Prenom, CorpsEmploi.No + ' - ' + CorpsEmploi.Nom AS 'CorpsEmploi', ClasseEmploi.No + ' - ' + ClasseEmploi.Nom AS 'ClasseEmploi', Emploi.Salaire AS 'SalairePeriode', Emploi.Salaire / 10 AS 'SalaireQuotidien' INTO #temp01 FROM Emploi INNER JOIN Personne ON Emploi.Cle_personne = Personne.Cle LEFT JOIN Corps_emploi ON Emploi.Cle_corpsEmploi = CorpsEmploi.Cle LEFT JOIN Classe_emploi ON Emploi.Cle_classeEmploi = ClasseEmploi.Cle ORDER BY 1,2,3

---

Lorsque la table temporaire est créé, il suffit d'exécuter le script suivant en y apportant les regroupements désirés en fonction de vos besoins.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

WITH OrderByTest (Departement, CorpsEmploi, ClasseEmploi, SalaireQuotidien, RowNum, CountOrders) AS ( SELECT Departement, CorpsEmploi, ClasseEmploi, SalaireQuotidien, ROW_NUMBER() OVER (PARTITION BY Departement, CorpsEmploi, ClasseEmploi ORDER BY SalaireQuotidien) AS RowNum, COUNT(Accréditation) OVER (PARTITION BY Departement,CorpsEmploi, ClasseEmploi) AS CountOrders FROM #temp01 AS Test )   SELECT Departement, CorpsEmploi, ClasseEmploi, AVG(SalaireQuotidien) as 'Médiane', CountOrders AS 'Nombre' FROM OrderByAccreditation WHERE RowNum BETWEEN (CountOrders + 1)/2 AND (CountOrders + 2)/2 GROUP BY Departement, CorpsEmploi, ClasseEmploi, CountOrders

---

Les éléments importants à retenir, ce sont les fonctions ROW_NUMBER et OVER PARTITION BY.

Ce script a été testé sous SQL Server 2008 R2.

A noter que depuis la version 2012, une fonction de fenêtrage gère la médiane :
https://docs.microsoft.com/en-us/sql...t-transact-sql