Discussion: Calcul de la médiane

  1. #1
    Nouveau Candidat au Club
    Profil pro
    Inscrit en
    juillet 2005
    Messages
    1
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juillet 2005
    Messages : 1
    Points : 1
    Points
    1

    Par défaut 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

  2. #2
    Inactif Avatar de Médiat
    Inscrit en
    décembre 2003
    Messages
    1 946
    Détails du profil
    Informations forums :
    Inscription : décembre 2003
    Messages : 1 946
    Points : 2 205
    Points
    2 205

    Par défaut

    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
    J'affirme péremptoirement que toute affirmation péremptoire est fausse
    5ième élément : barde-prince des figures de style, duc de la synecdoque
    Je ne réponds jamais aux questions techniques par MP

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 017
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 017
    Points : 39 514
    Points
    39 514
    Billets dans le blog
    1

    Par défaut

    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  4. #4
    Inactif Avatar de Médiat
    Inscrit en
    décembre 2003
    Messages
    1 946
    Détails du profil
    Informations forums :
    Inscription : décembre 2003
    Messages : 1 946
    Points : 2 205
    Points
    2 205

    Par défaut

    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 ?
    J'affirme péremptoirement que toute affirmation péremptoire est fausse
    5ième élément : barde-prince des figures de style, duc de la synecdoque
    Je ne réponds jamais aux questions techniques par MP

  5. #5
    Membre confirmé

    Profil pro
    Inscrit en
    octobre 2003
    Messages
    288
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2003
    Messages : 288
    Points : 451
    Points
    451

    Par défaut Re: Calcul de la médiane

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

  6. #6
    Membre confirmé

    Profil pro
    Inscrit en
    octobre 2003
    Messages
    288
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : octobre 2003
    Messages : 288
    Points : 451
    Points
    451

    Par défaut

    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

  7. #7
    Candidat au Club
    Homme Profil pro
    Technicien en informatique
    Inscrit en
    février 2011
    Messages
    9
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Technicien en informatique
    Secteur : Service public

    Informations forums :
    Inscription : février 2011
    Messages : 9
    Points : 4
    Points
    4

    Par défaut

    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.

  8. #8
    Modérateur

    Homme Profil pro
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    7 427
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 39
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 7 427
    Points : 15 703
    Points
    15 703

    Par défaut

    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [AC-2002] Calcul de la médiane
    Par delisle59 dans le forum Requêtes et SQL.
    Réponses: 0
    Dernier message: 10/03/2010, 20h24
  2. Fonction calcul de la médiane d'un montant
    Par MikaelBO dans le forum Designer
    Réponses: 8
    Dernier message: 29/06/2009, 14h20
  3. Calcul de la Médiane pour chaque élément d'une table
    Par yuekerobero dans le forum Requêtes
    Réponses: 11
    Dernier message: 30/09/2008, 12h01
  4. Calcul de la médiane
    Par revsys dans le forum Delphi
    Réponses: 9
    Dernier message: 21/05/2007, 10h11
  5. [SQL-Oracle]Calcul de la médiane
    Par fdraven dans le forum Oracle
    Réponses: 5
    Dernier message: 14/08/2006, 14h37

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo