Discussion :

[nazlurf]

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

[Médiat]

Création de la table

Code :

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

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 ) :

Code :

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

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

[SQLpro]

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 :

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

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 :

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

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 :

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

INSERT INTO T_STATISTIQUES_STT VALUES (6, 23.5)

La solution devient :

Code :

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

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 :

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

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 :

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

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 +

[Médiat]

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 ?

[spidetra]

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).
--

[spidetra]

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
Cette solution fonctionne avec le jeu de données de SQLPro :

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
 
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

[belangy]

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 sql :

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

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 sql :

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

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.

[Waldar]

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