moda - sql mean function

Función para calcular la mediana en SQL Server

Question

Según MSDN , Median no está disponible como una función agregada en Transact-SQL. Sin embargo, me gustaría saber si es posible crear esta funcionalidad (utilizando la función Create Aggregate , 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éricos) en una consulta global?

Answer 1

Acabo de encontrar esta página mientras buscaba una solución basada en conjuntos para la mediana. Después de ver algunas de las soluciones aquí, se me ocurrió lo siguiente. La esperanza es ayuda / funciona

DECLARE @test TABLE(
i int identity(1,1),
id int,
score float
)
INSERT INTO @test (id,score) VALUES (1,10)
INSERT INTO @test (id,score) VALUES (1,11)
INSERT INTO @test (id,score) VALUES (1,15)
INSERT INTO @test (id,score) VALUES (1,19)
INSERT INTO @test (id,score) VALUES (1,20)
INSERT INTO @test (id,score) VALUES (2,20)
INSERT INTO @test (id,score) VALUES (2,21)
INSERT INTO @test (id,score) VALUES (2,25)
INSERT INTO @test (id,score) VALUES (2,29)
INSERT INTO @test (id,score) VALUES (2,30)
INSERT INTO @test (id,score) VALUES (3,20)
INSERT INTO @test (id,score) VALUES (3,21)
INSERT INTO @test (id,score) VALUES (3,25)
INSERT INTO @test (id,score) VALUES (3,29)
DECLARE @counts TABLE(
id int,
cnt int
)
INSERT INTO @counts (
id,
cnt
)
SELECT
id,
COUNT(*)
FROM
@test
GROUP BY
id
SELECT
drv.id,
drv.start,
AVG(t.score)
FROM
(
SELECT
MIN(t.i)-1 AS start,
t.id
FROM
@test t
GROUP BY
t.id
) drv
INNER JOIN @test t ON drv.id = t.id
INNER JOIN @counts c ON t.id = c.id
WHERE
t.i = ((c.cnt+1)/2)+drv.start
OR (
t.i = (((c.cnt+1)%2) * ((c.cnt+2)/2))+drv.start
AND ((c.cnt+1)%2) * ((c.cnt+2)/2) <> 0
)
GROUP BY
drv.id,
drv.start

Answer 2

Aun mejor:

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

¡Del maestro mismo, sqlperformance.com/2012/08/t-sql-queries/median !

Answer 3

Aunque la solución de Justin Grant parece sólida, descubrí que cuando tienes una cantidad de valores duplicados dentro de una clave de partición determinada, los números de fila para los valores duplicados de ASC terminan fuera de secuencia, por lo que no se alinean correctamente.

Aquí hay un fragmento de mi resultado:

KEY VALUE ROWA ROWD
13 2 22 182
13 1 6 183
13 1 7 184
13 1 8 185
13 1 9 186
13 1 10 187
13 1 11 188
13 1 12 189
13 0 1 190
13 0 2 191
13 0 3 192
13 0 4 193
13 0 5 194

Usé el código de Justin como base para esta solución. Aunque no es tan eficiente dado el uso de tablas derivadas múltiples, resuelve el problema de ordenamiento de filas que encontré. Cualquier mejora sería bienvenida ya que no soy tan experimentado en T-SQL.

SELECT PKEY, cast(AVG(VALUE)as decimal(5,2)) as MEDIANVALUE
FROM
(
SELECT PKEY,VALUE,ROWA,ROWD,
''FLAG'' = (CASE WHEN ROWA IN (ROWD,ROWD-1,ROWD+1) THEN 1 ELSE 0 END)
FROM
(
SELECT
PKEY,
cast(VALUE as decimal(5,2)) as VALUE,
ROWA,
ROW_NUMBER() OVER (PARTITION BY PKEY ORDER BY ROWA DESC) as ROWD
FROM
(
SELECT
PKEY,
VALUE,
ROW_NUMBER() OVER (PARTITION BY PKEY ORDER BY VALUE ASC,PKEY ASC ) as ROWA
FROM [MTEST]
)T1
)T2
)T3
WHERE FLAG = ''1''
GROUP BY PKEY
ORDER BY PKEY

Answer 4

Con frecuencia, es posible que necesitemos calcular Median no solo para toda la tabla, sino también para agregados con respecto a alguna ID. En otras palabras, calcule la mediana para cada ID en nuestra tabla, donde cada ID tiene muchos registros. (basado en la solución editada por @gdoron: buen rendimiento y funciona en muchos SQL)

SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val,
COUNT(*) OVER (PARTITION BY our_id) AS cnt,
ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rnk
FROM our_table
) AS x
WHERE rnk IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

Espero eso ayude.

Answer 5

El ejemplo anterior de Justin es muy bueno. Pero esa necesidad de la clave primaria debe establecerse muy claramente. He visto ese código en la naturaleza sin la clave y los resultados son malos.

La queja que recibo sobre el Percentile_Cont es que no le dará un valor real del conjunto de datos. Para llegar a una "mediana" que es un valor real del conjunto de datos, use Percentile_Disc.

SELECT SalesOrderID, OrderQty,
PERCENTILE_DISC(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

Answer 6

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

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

Answer 7

En un UDF, escribe:

 Select Top 1 medianSortColumn from Table T
Where (Select Count(*) from Table
Where MedianSortColumn <
(Select Count(*) From Table) / 2)
Order By medianSortColumn

Answer 8

Esta es la solución más óptima para encontrar medianas que pueda imaginar. Los nombres en el ejemplo se basan en el ejemplo de Justin. Asegúrese de que exista un índice para la tabla Sales.SalesOrderHeader con las columnas de índice CustomerId y TotalDue en ese orden.

SELECT
sohCount.CustomerId,
AVG(sohMid.TotalDue) as TotalDueMedian
FROM
(SELECT
soh.CustomerId,
COUNT(*) as NumberOfRows
FROM
Sales.SalesOrderHeader soh
GROUP BY soh.CustomerId) As sohCount
CROSS APPLY
(Select
soh.TotalDue
FROM
Sales.SalesOrderHeader soh
WHERE soh.CustomerId = sohCount.CustomerId
ORDER BY soh.TotalDue
OFFSET sohCount.NumberOfRows / 2 - ((sohCount.NumberOfRows + 1) % 2) ROWS
FETCH NEXT 1 + ((sohCount.NumberOfRows + 1) % 2) ROWS ONLY
) As sohMid
GROUP BY sohCount.CustomerId

ACTUALIZAR

Estaba un poco inseguro sobre qué método tiene el mejor rendimiento, así que hice una comparación entre mi método Justin Grants y Jeff Atwoods ejecutando consultas basadas en los tres métodos en un lote y el costo por lotes de cada consulta fueron:

Sin índice:

• Mina 30%
• Justin Grants 13%
• Jeff Atwoods 58%

Y con índice

• Mina 3%.
• Justin Grants 10%
• Jeff Atwoods 87%

Traté de ver qué tan bien escalan las consultas si tienes índice al crear más datos de alrededor de 14 000 filas por un factor de 2 hasta 512, lo que significa al final alrededor de 7,2 millones de filas. Tenga en cuenta que me aseguré de que el campo CustomeId fuera único para cada vez que hice una única copia, por lo que la proporción de filas en comparación con la instancia única de CustomerId se mantuvo constante. Mientras hacía esto, ejecuté ejecuciones en las que reconstruí el índice después, y noté que los resultados se estabilizaron en torno a un factor de 128 con los datos que tenía para estos valores:

• Mina 3%.
• Justin Grants 5%
• Jeff Atwoods 92%

Me pregunté cómo el rendimiento podría haberse visto afectado por la cantidad de filas escaladas, pero manteniendo constante CustomerId único, así que configuré una nueva prueba en la que solo hice esto. Ahora, en lugar de estabilizar, la relación del costo del lote se mantuvo divergente, también en lugar de alrededor de 20 filas por ID de cliente por promedio, tuve al final alrededor de 10000 filas por cada identificador único. Los números donde:

• Mina 4%
• Justins 60%
• Jeffs 35%

Me aseguré de implementar correctamente cada método al comparar los resultados. Mi conclusión es que el método que utilicé generalmente es más rápido, siempre y cuando el índice exista. También notó que este método es lo que se recomienda para este problema en particular en este artículo https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=5

Una forma de mejorar aún más el rendimiento de las llamadas posteriores a esta consulta es mantener la información de recuento en una tabla auxiliar. Incluso podría mantenerlo activando un desencadenador que actualice y contenga información sobre el recuento de las filas de SalesOrderHeader dependientes de CustomerId; por supuesto, también puede almacenar la mediana fácilmente.

Answer 9

Esta es una respuesta tan simple como podría haberlo hecho. Funcionó bien con mis datos. Si desea excluir ciertos valores, simplemente agregue una cláusula where a la selección interna.

SELECT TOP 1
ValueField AS MedianValue
FROM
(SELECT TOP(SELECT COUNT(1)/2 FROM tTABLE)
ValueField
FROM
tTABLE
ORDER BY
ValueField) A
ORDER BY
ValueField DESC

Answer 10

Esto funciona con SQL 2000:

DECLARE @testTable TABLE
(
VALUE INT
)
--INSERT INTO @testTable -- Even Test
--SELECT 3 UNION ALL
--SELECT 5 UNION ALL
--SELECT 7 UNION ALL
--SELECT 12 UNION ALL
--SELECT 13 UNION ALL
--SELECT 14 UNION ALL
--SELECT 21 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 29 UNION ALL
--SELECT 40 UNION ALL
--SELECT 56
--
--INSERT INTO @testTable -- Odd Test
--SELECT 3 UNION ALL
--SELECT 5 UNION ALL
--SELECT 7 UNION ALL
--SELECT 12 UNION ALL
--SELECT 13 UNION ALL
--SELECT 14 UNION ALL
--SELECT 21 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 23 UNION ALL
--SELECT 29 UNION ALL
--SELECT 39 UNION ALL
--SELECT 40 UNION ALL
--SELECT 56
DECLARE @RowAsc TABLE
(
ID INT IDENTITY,
Amount INT
)
INSERT INTO @RowAsc
SELECT VALUE
FROM @testTable
ORDER BY VALUE ASC
SELECT AVG(amount)
FROM @RowAsc ra
WHERE ra.id IN
(
SELECT ID
FROM @RowAsc
WHERE ra.id -
(
SELECT MAX(id) / 2.0
FROM @RowAsc
) BETWEEN 0 AND 1
)

Answer 11

Hay muchas formas de hacerlo, con un rendimiento espectacularmente variable. Aquí hay una solución particularmente bien optimizada, de Medians, 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 pruebas de rendimiento. Tenga en cuenta el uso de una columna única como desambiguador en caso de que haya múltiples filas con el mismo valor de la columna 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á 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;

Answer 12

Intento con varias alternativas, pero debido a que mis registros de datos tienen valores repetidos, las versiones ROW_NUMBER parecen no ser una opción para mí. Entonces aquí la consulta que utilicé (una versión con NTILE):

SELECT distinct
CustomerId,
(
MAX(CASE WHEN Percent50_Asc=1 THEN TotalDue END) OVER (PARTITION BY CustomerId) +
MIN(CASE WHEN Percent50_desc=1 THEN TotalDue END) OVER (PARTITION BY CustomerId)
)/2 MEDIAN
FROM
(
SELECT
CustomerId,
TotalDue,
NTILE(2) OVER (
PARTITION BY CustomerId
ORDER BY TotalDue ASC) AS Percent50_Asc,
NTILE(2) OVER (
PARTITION BY CustomerId
ORDER BY TotalDue DESC) AS Percent50_desc
FROM Sales.SalesOrderHeader SOH
) x
ORDER BY CustomerId;

Answer 13

La siguiente consulta devuelve la mediana de una lista de valores en una columna. No se puede usar como una función agregada o junto con ella, pero puede usarla como una sub consulta con una cláusula WHERE en la selección interna.

SQL Server 2005+:

SELECT TOP 1 value from
(
SELECT TOP 50 PERCENT value
FROM table_name
ORDER BY value
)for_median
ORDER BY value DESC

Answer 14

La siguiente solución funciona bajo estas suposiciones:

• Sin valores duplicados
• Sin valores NULL

Código:

IF OBJECT_ID(''dbo.R'', ''U'') IS NOT NULL
DROP TABLE dbo.R
CREATE TABLE R (
A FLOAT NOT NULL);
INSERT INTO R VALUES (1);
INSERT INTO R VALUES (2);
INSERT INTO R VALUES (3);
INSERT INTO R VALUES (4);
INSERT INTO R VALUES (5);
INSERT INTO R VALUES (6);
-- Returns Median(R)
select SUM(A) / CAST(COUNT(A) AS FLOAT)
from R R1
where ((select count(A) from R R2 where R1.A > R2.A) =
(select count(A) from R R2 where R1.A < R2.A)) OR
((select count(A) from R R2 where R1.A > R2.A) + 1 =
(select count(A) from R R2 where R1.A < R2.A)) OR
((select count(A) from R R2 where R1.A > R2.A) =
(select count(A) from R R2 where R1.A < R2.A) + 1) ;

Answer 15

MS SQL Server 2012 (y posterior) tiene la función PERCENTILE_DISC que calcula un percentil específico para valores ordenados. PERCENTILE_DISC (0.5) calculará la mediana - https://msdn.microsoft.com/en-us/library/hh231327.aspx

Answer 16

Mi respuesta rápida original fue:

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

Esto le dará la mediana y el rango intercuartílico de una sola vez. Si realmente solo quieres una fila que sea la mediana, entonces quita el comentario de la cláusula where.

Cuando lo incluyes en un plan de explicación, el 60% del trabajo consiste en ordenar los datos que son inevitables al calcular estadísticas dependientes de la posición como esta.

He enmendado la respuesta para seguir la excelente sugerencia de Robert Ševčík-Robajz en los comentarios a continuación:

;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

Esto debería calcular los valores medianos y percentiles correctos cuando tiene un número par de elementos de datos. Nuevamente, elimine el comentario de la cláusula where final si solo desea la mediana y no toda la distribución de percentiles.

Answer 17

Para conjuntos de datos a gran escala, puede probar este GIST:

https://gist.github.com/chrisknoll/1b38761ce8c5016ec5b2

Funciona agregando los distintos valores que encontraría en su conjunto (como las edades, el año de nacimiento, etc.) y utiliza las funciones de la ventana SQL para localizar cualquier posición de percentil que especifique en la consulta.

Answer 18

Para principiantes como yo, que están aprendiendo los conceptos básicos, personalmente encuentro este ejemplo más fácil de seguir, ya que es más fácil entender exactamente qué está sucediendo y de dónde vienen los valores medianos ...

select
( max(a.[Value1]) + min(a.[Value1]) ) / 2 as [Median Value1]
,( max(a.[Value2]) + min(a.[Value2]) ) / 2 as [Median Value2]
from (select
datediff(dd,startdate,enddate) as [Value1]
,xxxxxxxxxxxxxx as [Value2]
from dbo.table1
)a

¡En absoluto temor de algunos de los códigos anteriores!

Answer 19

Para su pregunta, Jeff Atwood ya había dado la solución simple y efectiva. Pero, si está buscando algún método alternativo para calcular la mediana, el siguiente código SQL lo ayudará.

create table employees(salary int);
insert into employees values(8); insert into employees values(23); insert into employees values(45); insert into employees values(123); insert into employees values(93); insert into employees values(2342); insert into employees values(2238);
select * from employees;
declare @odd_even int; declare @cnt int; declare @middle_no int;
set @cnt=(select count(*) from employees); set @middle_no=(@cnt/2)+1; select @odd_even=case when (@cnt%2=0) THEN -1 ELse 0 END ;
select AVG(tbl.salary) from (select salary,ROW_NUMBER() over (order by salary) as rno from employees group by salary) tbl where tbl.rno=@middle_no or tbl.rno=@middle_no+@odd_even;

Si está buscando calcular la mediana en MySQL, este enlace github será útil.

Answer 20

Para una variable / medida continua ''col1'' de ''tabla1''

select col1
from
(select top 50 percent col1,
ROW_NUMBER() OVER(ORDER BY col1 ASC) AS Rowa,
ROW_NUMBER() OVER(ORDER BY col1 DESC) AS Rowd
from table1 ) tmp
where tmp.Rowa = tmp.Rowd

Answer 21

Quería encontrar una solución por mí mismo, pero mi cerebro tropezó y cayó en el camino. Creo que funciona, pero no me pidas que lo explique por la mañana. :PAG

DECLARE @table AS TABLE
(
Number int not null
);
insert into @table select 2;
insert into @table select 4;
insert into @table select 9;
insert into @table select 15;
insert into @table select 22;
insert into @table select 26;
insert into @table select 37;
insert into @table select 49;
DECLARE @Count AS INT
SELECT @Count = COUNT(*) FROM @table;
WITH MyResults(RowNo, Number) AS
(
SELECT RowNo, Number FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Number) AS RowNo, Number FROM @table) AS Foo
)
SELECT AVG(Number) FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2)

Answer 22

Si desea usar la función Create Aggregate en SQL Server, esta es la forma de hacerlo. Hacerlo de esta manera tiene la ventaja de poder escribir consultas claras. Tenga en cuenta que este proceso podría adaptarse para calcular un valor Percentil con bastante facilidad.

Cree un nuevo proyecto de Visual Studio y establezca el marco de destino en .NET 3.5 (esto es para SQL 2008, puede ser diferente en SQL 2012). A continuación, cree un archivo de clase y coloque el siguiente código, o c # equivalente:

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO
<Serializable>
<SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, _
IsInvariantToOrder:=True, MaxByteSize:=-1, IsNullIfEmpty:=True)>
Public Class Median
Implements IBinarySerialize
Private _items As List(Of Decimal)
Public Sub Init()
_items = New List(Of Decimal)()
End Sub
Public Sub Accumulate(value As SqlDecimal)
If Not value.IsNull Then
_items.Add(value.Value)
End If
End Sub
Public Sub Merge(other As Median)
If other._items IsNot Nothing Then
_items.AddRange(other._items)
End If
End Sub
Public Function Terminate() As SqlDecimal
If _items.Count <> 0 Then
Dim result As Decimal
_items = _items.OrderBy(Function(i) i).ToList()
If _items.Count Mod 2 = 0 Then
result = ((_items((_items.Count / 2) - 1)) + (_items(_items.Count / 2))) / 2@
Else
result = _items((_items.Count - 1) / 2)
End If
Return New SqlDecimal(result)
Else
Return New SqlDecimal()
End If
End Function
Public Sub Read(r As BinaryReader) Implements IBinarySerialize.Read
''deserialize it from a string
Dim list = r.ReadString()
_items = New List(Of Decimal)
For Each value In list.Split(","c)
Dim number As Decimal
If Decimal.TryParse(value, number) Then
_items.Add(number)
End If
Next
End Sub
Public Sub Write(w As BinaryWriter) Implements IBinarySerialize.Write
''serialize the list to a string
Dim list = ""
For Each item In _items
If list <> "" Then
list += ","
End If
list += item.ToString()
Next
w.Write(list)
End Sub
End Class

A continuación, compílelo y copie el archivo DLL y PDB en su máquina SQL Server y ejecute el siguiente comando en SQL Server:

CREATE ASSEMBLY CustomAggregate FROM ''{path to your DLL}''
WITH PERMISSION_SET=SAFE;
GO
CREATE AGGREGATE Median(@value decimal(9, 3))
RETURNS decimal(9, 3)
EXTERNAL NAME [CustomAggregate].[{namespace of your DLL}.Median];
GO

A continuación, puede escribir una consulta para calcular la mediana de esta manera: SELECCIONE dbo.Median (campo) FROM Table

Answer 23

Si está usando SQL 2005 o mejor, este es un cálculo de mediana simple 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

Answer 24

Simple, rápido y preciso

SELECT x.Amount
FROM (SELECT amount,
Count(1) OVER (partition BY ''A'') AS TotalRows,
Row_number() OVER (ORDER BY Amount ASC) AS AmountOrder
FROM facttransaction ft) x
WHERE x.AmountOrder = Round(x.TotalRows / 2.0, 0)

Answer 25

Sobre la base de la respuesta de Jeff Atwood aquí arriba, es con GROUP BY y una subconsulta correlacionada para obtener la mediana para cada grupo.

SELECT TestID,
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score) AS BottomHalf)
+
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts WHERE TestID = Posts_parent.TestID ORDER BY Score DESC) AS TopHalf)
) / 2 AS MedianScore,
AVG(Score) AS AvgScore, MIN(Score) AS MinScore, MAX(Score) AS MaxScore
FROM Posts_parent
GROUP BY Posts_parent.TestID

Answer 26

Vea otras soluciones para el cálculo de la mediana en SQL aquí: " Forma simple de calcular la mediana con MySQL " (las soluciones son en su mayoría independientes del proveedor).

Answer 27

--Create Temp Table to Store Results in
DECLARE @results AS TABLE
(
[Month] datetime not null
,[Median] int not null
);
--This variable will determine the date
DECLARE @IntDate as int
set @IntDate = -13
WHILE (@IntDate < 0)
BEGIN
--Create Temp Table
DECLARE @table AS TABLE
(
[Rank] int not null
,[Days Open] int not null
);
--Insert records into Temp Table
insert into @table
SELECT
rank() OVER (ORDER BY DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, ''1970'')), 0), DATEDIFF(day,DATEADD(ss, SVR.open_date, ''1970''),DATEADD(ss, SVR.close_date, ''1970'')),[SVR].[ref_num]) as [Rank]
,DATEDIFF(day,DATEADD(ss, SVR.open_date, ''1970''),DATEADD(ss, SVR.close_date, ''1970'')) as [Days Open]
FROM
mdbrpt.dbo.View_Request SVR
LEFT OUTER JOIN dbo.dtv_apps_systems vapp
on SVR.category = vapp.persid
LEFT OUTER JOIN dbo.prob_ctg pctg
on SVR.category = pctg.persid
Left Outer Join [mdbrpt].[dbo].[rootcause] as [Root Cause]
on [SVR].[rootcause]=[Root Cause].[id]
Left Outer Join [mdbrpt].[dbo].[cr_stat] as [Status]
on [SVR].[status]=[Status].[code]
LEFT OUTER JOIN [mdbrpt].[dbo].[net_res] as [net]
on [net].[id]=SVR.[affected_rc]
WHERE
SVR.Type IN (''P'')
AND
SVR.close_date IS NOT NULL
AND
[Status].[SYM] = ''Closed''
AND
SVR.parent is null
AND
[Root Cause].[sym] in ( ''RC - Application'',''RC - Hardware'', ''RC - Operational'', ''RC - Unknown'')
AND
(
[vapp].[appl_name] in (''3PI'',''Billing Rpts/Files'',''Collabrent'',''Reports'',''STMS'',''STMS 2'',''Telco'',''Comergent'',''OOM'',''C3-BAU'',''C3-DD'',''DIRECTV'',''DIRECTV Sales'',''DIRECTV Self Care'',''Dealer Website'',''EI Servlet'',''Enterprise Integration'',''ET'',''ICAN'',''ODS'',''SB-SCM'',''SeeBeyond'',''Digital Dashboard'',''IVR'',''OMS'',''Order Services'',''Retail Services'',''OSCAR'',''SAP'',''CTI'',''RIO'',''RIO Call Center'',''RIO Field Services'',''FSS-RIO3'',''TAOS'',''TCS'')
OR
pctg.sym in (''Systems.Release Health Dashboard.Problem'',''DTV QA Test.Enterprise Release.Deferred Defect Log'')
AND
[Net].[nr_desc] in (''3PI'',''Billing Rpts/Files'',''Collabrent'',''Reports'',''STMS'',''STMS 2'',''Telco'',''Comergent'',''OOM'',''C3-BAU'',''C3-DD'',''DIRECTV'',''DIRECTV Sales'',''DIRECTV Self Care'',''Dealer Website'',''EI Servlet'',''Enterprise Integration'',''ET'',''ICAN'',''ODS'',''SB-SCM'',''SeeBeyond'',''Digital Dashboard'',''IVR'',''OMS'',''Order Services'',''Retail Services'',''OSCAR'',''SAP'',''CTI'',''RIO'',''RIO Call Center'',''RIO Field Services'',''FSS-RIO3'',''TAOS'',''TCS'')
)
AND
DATEADD(mm, DATEDIFF(mm, 0, DATEADD(ss, SVR.close_date, ''1970'')), 0) = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0)
ORDER BY [Days Open]
DECLARE @Count AS INT
SELECT @Count = COUNT(*) FROM @table;
WITH MyResults(RowNo, [Days Open]) AS
(
SELECT RowNo, [Days Open] FROM
(SELECT ROW_NUMBER() OVER (ORDER BY [Days Open]) AS RowNo, [Days Open] FROM @table) AS Foo
)
insert into @results
SELECT
DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,@IntDate,getdate())), 0) as [Month]
,AVG([Days Open])as [Median] FROM MyResults WHERE RowNo = (@Count+1)/2 OR RowNo = ((@Count+1)%2) * ((@Count+2)/2)
set @IntDate = @IntDate+1
DELETE FROM @table
END
select *
from @results
order by [Month]

Answer 28

DECLARE @Obs int
DECLARE @RowAsc table
(
ID INT IDENTITY,
Observation FLOAT
)
INSERT INTO @RowAsc
SELECT Observations FROM MyTable
ORDER BY 1
SELECT @Obs=COUNT(*)/2 FROM @RowAsc
SELECT Observation AS Median FROM @RowAsc WHERE ID=@Obs