Saltar al contenido

Función para calcular la mediana en SQL Server

Si te encuentras con alguna parte que no entiendes puedes dejarlo en la sección de comentarios y te responderemos tan rápido como podamos.

Solución:

Si está utilizando SQL 2005 o mejor, este es un cálculo de mediana agradable y simple para una sola columna en una tabla:

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

ACTUALIZACIÓN 2019: En los 10 años desde que escribí esta respuesta, se han descubierto más soluciones que pueden producir mejores resultados. Además, las versiones de SQL Server desde entonces (especialmente SQL 2012) han introducido nuevas funciones de T-SQL que se pueden usar para calcular las medianas. Las versiones de SQL Server también han mejorado su optimizador de consultas, lo que puede afectar el rendimiento de varias soluciones medianas. Net-net, mi publicación original de 2009 todavía está bien, pero puede haber mejores soluciones para las aplicaciones modernas de SQL Server. Eche un vistazo a este artículo de 2012, que es un gran recurso: https://sqlperformance.com/2012/08/t-sql-queries/median

Este artículo encontró que el siguiente patrón es mucho, mucho más rápido que todas las demás alternativas, al menos en el esquema simple que probaron. Esta solución fue 373 veces más rápida (!!!) que la más lenta (PERCENTILE_CONT) solución probada. Tenga en cuenta que este truco requiere dos consultas separadas que pueden no ser prácticas en todos los casos. También requiere SQL 2012 o posterior.

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;

Por supuesto, solo porque una prueba en un esquema en 2012 arrojó excelentes resultados, su kilometraje puede variar, especialmente si está en SQL Server 2014 o posterior. Si el rendimiento es importante para el cálculo de la mediana, le sugiero encarecidamente que pruebe varias de las opciones recomendadas en ese artículo para asegurarse de haber encontrado la mejor para su esquema.

También sería especialmente cuidadoso al usar la función (nueva en SQL Server 2012) PERCENTILE_CONT eso se recomienda en una de las otras respuestas a esta pregunta, porque el artículo vinculado anteriormente encontró que esta función integrada es 373 veces más lenta que la solución más rápida. Es posible que esta disparidad haya mejorado en los 7 años transcurridos desde entonces, pero personalmente no usaría esta función en una mesa grande hasta que verifique su rendimiento frente a otras soluciones.

LA PUBLICACIÓN ORIGINAL DE 2009 ESTÁ A CONTINUACIÓN:

Hay muchas maneras de hacer esto, con un rendimiento que varía drásticamente. Aquí hay una solución particularmente bien optimizada, de Medianas, ROW_NUMBER y rendimiento. Esta es una solución particularmente óptima cuando se trata de E/S reales generadas durante la ejecución: parece más costosa que otras soluciones, pero en realidad es mucho más rápida.

Esa página también contiene una discusión de otras soluciones y detalles de las pruebas de rendimiento. Tenga en cuenta el uso de una columna única como desambiguador en caso de que haya varias filas con el mismo valor de la columna de la mediana.

Al igual que con todos los escenarios de rendimiento de la base de datos, siempre intente probar una solución con datos reales en hardware real: nunca se sabe cuándo un cambio en el optimizador de SQL Server o una peculiaridad en su entorno harán que una solución normalmente rápida sea más lenta.

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;

En SQL Server 2012 debe usar 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

Consulte también: http://blog.sqlauthority.com/2011/11/20/sql-server-introduction-to-percentile_cont-analytic-functions-introduced-in-sql-server-2012/

valoraciones y reseñas

Si guardas algún reparo y disposición de beneficiar nuestro tutorial puedes realizar una crítica y con placer lo observaremos.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *