Fonction pour calculer la médiane dans le serveur SQL

Question

Según MSDN La médiane n'est pas disponible comme fonction d'agrégation dans Transact-SQL. Cependant, j'aimerais savoir s'il est possible de créer cette fonctionnalité (en utilisant la fonction Créer un agrégat fonction, fonction définie par l'utilisateur, ou toute autre méthode).

Quelle serait la meilleure façon (si possible) de faire cela - permettre le calcul d'une valeur médiane (en supposant un type de données numériques) dans une requête agrégée ?

Answer 1

Si vous utilisez SQL 2005 ou une version plus récente, il s'agit d'un calcul de médiane simple et efficace pour une seule colonne d'une table :

SELECT
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median

Answer 2

MISE À JOUR 2019 : Au cours des dix années qui se sont écoulées depuis que j'ai rédigé cette réponse, d'autres solutions ont été découvertes qui pourraient donner de meilleurs résultats. De plus, les versions de SQL Server publiées depuis lors (en particulier SQL 2012) ont introduit de nouvelles fonctionnalités T-SQL qui peuvent être utilisées pour calculer les médianes. Les versions de SQL Server ont également amélioré leur optimiseur de requêtes, ce qui peut affecter la performance des différentes solutions de médiane. Au final, mon article original de 2009 est toujours valable, mais il existe peut-être de meilleures solutions pour les applications modernes de SQL Server. Jetez un coup d'œil à cet article de 2012 qui est une excellente ressource : https://sqlperformance.com/2012/08/t-sql-queries/median

Cet article a trouvé que le modèle suivant était beaucoup, beaucoup plus rapide que toutes les autres alternatives, au moins sur le schéma simple qu'ils ont testé. Cette solution était 373x plus rapide ( !!!) que la plus lente ( PERCENTILE_CONT ) solution testée. Notez que cette astuce nécessite deux requêtes distinctes, ce qui peut ne pas être pratique dans tous les cas. Elle nécessite également SQL 2012 ou une version ultérieure.

DECLARE @c BIGINT = (SELECT COUNT(*) FROM dbo.EvenRows);

SELECT AVG(1.0 * val)
FROM (
    SELECT val FROM dbo.EvenRows
     ORDER BY val
     OFFSET (@c - 1) / 2 ROWS
     FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
) AS x;

Bien sûr, ce n'est pas parce qu'un test effectué sur un schéma en 2012 a donné d'excellents résultats que les résultats peuvent varier, surtout si vous utilisez SQL Server 2014 ou une version ultérieure. Si la performance est importante pour votre calcul de la médiane, je vous suggère fortement d'essayer et de tester la performance de plusieurs des options recommandées dans cet article pour vous assurer que vous avez trouvé la meilleure pour votre schéma.

Je serais aussi particulièrement prudent en utilisant la fonction (nouvelle dans SQL Server 2012) PERCENTILE_CONT qui est recommandé dans l'un des autres réponses à cette question, car l'article lié ci-dessus a constaté que cette fonction intégrée était 373 fois plus lente que la solution la plus rapide. Il est possible que cette disparité ait été améliorée au cours des 7 années qui se sont écoulées depuis, mais personnellement, je n'utiliserais pas cette fonction sur une grande table avant d'avoir vérifié ses performances par rapport à d'autres solutions.

L'ARTICLE ORIGINAL DE 2009 EST CI-DESSOUS :

Il existe de nombreuses façons de le faire, avec des performances très variables. Voici une solution particulièrement bien optimisée, provenant de _Médianes, ROW_NUMBERs, et performance_ . Il s'agit d'une solution particulièrement optimale en ce qui concerne les E/S réelles générées pendant l'exécution - elle semble plus coûteuse que les autres solutions, mais elle est en fait beaucoup plus rapide.

Cette page contient également une discussion sur d'autres solutions et des détails sur les tests de performance. Notez l'utilisation d'une colonne unique comme désambiguïsation dans le cas où il y a plusieurs lignes avec la même valeur de la colonne médiane.

Comme pour tous les scénarios de performance des bases de données, essayez toujours de tester une solution avec des données réelles sur du matériel réel - vous ne savez jamais quand une modification de l'optimiseur de SQL Server ou une particularité de votre environnement rendra plus lente une solution normalement rapide.

SELECT
   CustomerId,
   AVG(TotalDue)
FROM
(
   SELECT
      CustomerId,
      TotalDue,
      -- SalesOrderId in the ORDER BY is a disambiguator to break ties
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc
   FROM Sales.SalesOrderHeader SOH
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY CustomerId
ORDER BY CustomerId;

Answer 3

Dans SQL Server 2012, vous devez utiliser PERCENTILE_CONT :

SELECT SalesOrderID, OrderQty,
    PERCENTILE_CONT(0.5) 
        WITHIN GROUP (ORDER BY OrderQty)
        OVER (PARTITION BY SalesOrderID) AS MedianCont
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY SalesOrderID DESC

Voir aussi : http://blog.sqlauthority.com/2011/11/20/sql-server-introduction-to-percentile_cont-analytic-functions-introduced-in-sql-server-2012/

Answer 4

Ma réponse rapide initiale était :

select  max(my_column) as [my_column], quartile
from    (select my_column, ntile(4) over (order by my_column) as [quartile]
         from   my_table) i
--where quartile = 2
group by quartile

Cela vous donnera la médiane et l'écart interquartile d'un seul coup. Si vous ne voulez vraiment qu'une ligne qui soit la médiane, décommentez la clause where.

Lorsque vous collez cela dans un plan d'explication, 60% du travail consiste à trier les données, ce qui est inévitable lorsque l'on calcule des statistiques dépendantes de la position comme celle-ci.

J'ai modifié la réponse pour suivre l'excellente suggestion de Robert Ševcík-Robajz dans les commentaires ci-dessous :

;with PartitionedData as
  (select my_column, ntile(10) over (order by my_column) as [percentile]
   from   my_table),
MinimaAndMaxima as
  (select  min(my_column) as [low], max(my_column) as [high], percentile
   from    PartitionedData
   group by percentile)
select
  case
    when b.percentile = 10 then cast(b.high as decimal(18,2))
    else cast((a.low + b.high)  as decimal(18,2)) / 2
  end as [value], --b.high, a.low,
  b.percentile
from    MinimaAndMaxima a
  join  MinimaAndMaxima b on (a.percentile -1 = b.percentile) or (a.percentile = 10 and b.percentile = 10)
--where b.percentile = 5

Cela devrait permettre de calculer les valeurs correctes de la médiane et du percentile lorsque vous avez un nombre pair d'éléments de données. Encore une fois, décommentez la clause where finale si vous voulez seulement la médiane et non la distribution entière des percentiles.

Answer 5

Encore mieux :

SELECT @Median = AVG(1.0 * val)
FROM
(
    SELECT o.val, rn = ROW_NUMBER() OVER (ORDER BY o.val), c.c
    FROM dbo.EvenRows AS o
    CROSS JOIN (SELECT c = COUNT(*) FROM dbo.EvenRows) AS c
) AS x
WHERE rn IN ((c + 1)/2, (c + 2)/2);

De la part du maître lui-même, Itzik Ben-Gan !