SQL Server: truc simple pour calculer la mediane

À moins d’avoir une version entreprise de SQL Server 2000 et installé les Analysis Services, SQL Server n’a pas de fonction pour calculer la médiane d’un champ sur une série d’enregistrements.

Je me suis cassé le coco à penser comment faire, incluant des procédures SQL avec curseurs.  Finalement, j’ai trouvé une solution simple et rapide et j’aimerais la partager via un exemple.

Supposons que l’on ait une table avec une série d’appels téléphoniques avec la durée de chaque appel.  Pour simplifier l’exemple, voici une définition de table d’appels:

CREATE TABLE Appels (
   AppelID  INT PRIMARY KEY,
   Duree    SMALLINT NOT NULL)

Pour l’exemple, on a donc une table avec une série d’identificateurs d’appel et la durée en secondes pour chaque appel.  Évidemment, dans la vraie vie, il y aurait d’autres champs…

Selon la définition d’une médiane, on doit trier la série d’appels selon la durée et prendre la durée du milieu.  En général, la médiane est un chiffre important et est souvent plus pratique que la moyenne (disponible comme fonction dans SQL Server), puisque ça permet de ne pas tenir compte de valeurs extrèmes qui feraient en sorte que la moyenne ne correspond pas à un appel typique.  Donc, dans cet exemple, la médiane devrait nous donner la durée typique d’un appel.

Maintenant, voici comment faire…  On va va trier les appels selon la durée, prendre la moitié des premiers appels, les éliminer de notre liste d’appels et ensuite prendre le premier appel de la liste résultante que l’on trie à nouveau.  ATTENTION: cette technique donnera toujours la bonne médianne sur un nombre impair d’enregistrements.  Par contre, si le nombre est pair, on ne prend pas vraiment la valeur du milieu.  Il faudrait alors séparer la série d’enregistrements en deux et prendre la moyenne des 2 valeurs où l’on a fait la séparation, ce qui serait pas mal plus compliqué.  Mais, en général, sur un grand nombre d’enregistrements avec des valeurs de nombre entiers, ma technique fonctionne bien.

Voici la requête SQL pour calculer la médianne sur notre exemple:

SELECT TOP 1 Duree
  FROM Appels
 WHERE AppelID NOT IN (
    SELECT TOP 50 PERCENT AppelID
      FROM Appels
      ORDER BY Duree
    )
 ORDER BY Duree

Donc, la sous-requête du “NOT IN” va sélectionner les 50% appels les moins longs en triant les appels selon la durée et en faisant un “TOP 50 PERCENT” des appels.  Ensuite, la requête principale va choisir tous les appels, triés encore par durée, élimine les 50% premiers appels (la moitié des appels) et chosit le “TOP 1”, c’est à dire le premier appel de la liste résultante.  Ça devrait donc toujours nous donner la bonne médianne de la durée des appels pour un nombre impair d’appels.  Si on a beaucoup d’enregistrements d’appels, ça devrait aussi nous donner la bonne médianne pour un nombre d’appels pair, puisque la durée d’un appel ne varie pas trop d’un appel à l’autre.  En général, les appels du milieu, lorsque triée par durée, devraient tous avoir  la même durée sur une grande quantité de données.

Évidemment, cette technique n’est pas fiable sur de petites quantités de données.  Utilisez-là judicieusement…

3 thoughts on “SQL Server: truc simple pour calculer la mediane

  1. Nostra

    Merci, c’est exactement ce que je cherchais à faire !

  2. fred

    Voici une version beaup plus rapide a l’execution (car pas d’intersection : Not in)

    SELECT TOP 1 Duree

    FROM Appels

    WHERE AppelID > (select max(AppelID) from (

    SELECT TOP 50 PERCENT AppelID

    FROM Appels

    ORDER BY Duree

    ))

    ORDER BY Duree

  3. Salut Fred,

    oui, c’est pas mal plus élégant.

    J’aurais dû y penser… Merci.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>