Función para calcular la mediana en SQL Server
Según MSDN , Median no está disponible como función agregada en Transact-SQL. Sin embargo, me gustaría saber si es posible crear esta funcionalidad (utilizando la función Crear agregado , la función definida por el usuario o algún otro método).
¿Cuál sería la mejor manera (si es posible) de hacer esto: permitir el cálculo de un valor mediano (suponiendo un tipo de datos numérico) en una consulta agregada?
Si está utilizando SQL 2005 o superior, 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 DE 2019: En los 10 años transcurridos 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 utilizar para calcular 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 PERCENTILE_CONTsolución más lenta ( ) probada. Tenga en cuenta que este truco requiere dos consultas independientes, lo que puede no resultar práctico 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 probar varias de las opciones recomendadas en ese artículo para asegurarse de haber encontrado la mejor para su esquema.
También tendría especial cuidado al usar la función (nueva en SQL Server 2012) PERCENTILE_CONTque se recomienda en una de las otras respuestas a esta pregunta, porque el artículo vinculado anteriormente encontró que esta función incorporada 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 formas de hacer esto, con rendimientos que varían dramáticamente. Aquí hay una solución particularmente bien optimizada, desde 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 sobre otras soluciones y detalles de 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 mediana.
Como ocurre con todos los escenarios de rendimiento de bases de datos, intente siempre 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á 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;
