Functie om mediaan te berekenen in SQL Server

Volgens MSDNis Median niet beschikbaar als verzamelfunctie in Transact-SQL. Ik zou echter graag willen weten of het mogelijk is om deze functionaliteit te creëren (met behulp van de Maak een samengevoegde-functie, door de gebruiker gedefinieerde functie of een andere methode).

Wat zou de beste manier zijn (indien mogelijk) om dit te doen – de berekening van een mediaanwaarde toestaan (uitgaande van een numeriek gegevenstype) in een geaggregeerde zoekopdracht?

Antwoord 1, autoriteit 100%

2019 UPDATE:In de 10 jaar sinds ik dit antwoord schreef, zijn er meer oplossingen ontdekt die mogelijk betere resultaten opleveren. Ook hebben SQL Server-releases sindsdien (met name SQL 2012) nieuwe T-SQL-functies geïntroduceerd die kunnen worden gebruikt om medianen te berekenen. SQL Server-releases hebben ook de query-optimizer verbeterd, wat de prestaties van verschillende mediaan-oplossingen kan beïnvloeden. Net-net, mijn oorspronkelijke post uit 2009 is nog steeds in orde, maar er zijn misschien betere oplossingen voor moderne SQL Server-apps. Bekijk dit artikel uit 2012, dat een geweldige bron is: https://sqlperformance .com/2012/08/t-sql-queries/mediaan

In dit artikel werd vastgesteld dat het volgende patroon veel, veel sneller is dan alle andere alternatieven, althans volgens het eenvoudige schema dat ze hebben getest. Deze oplossing was 373x sneller (!!!) dan de langzaamste (PERCENTILE_CONT) geteste oplossing. Merk op dat deze truc twee afzonderlijke zoekopdrachten vereist, wat misschien niet in alle gevallen praktisch is. Het vereist ook SQL 2012 of later.

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;

Natuurlijk, alleen omdat één test op één schema in 2012 geweldige resultaten opleverde, kan uw kilometerstand variëren, vooral als u SQL Server 2014 of later gebruikt. Als prestatie belangrijk is voor je mediaanberekening, raad ik je ten zeerste aan om een aantal van de aanbevolen opties in dat artikel uit te proberen en te testen om er zeker van te zijn dat je de beste voor je schema hebt gevonden.

Ik zou ook bijzonder voorzichtig zijn met het gebruik van de (nieuw in SQL Server 2012) functie PERCENTILE_CONTdie wordt aanbevolen in een van de andere antwoordenop deze vraag, omdat in het hierboven gelinkte artikel werd vastgesteld dat deze ingebouwde functie 373x langzamer is dan de snelste oplossing. Het is mogelijk dat dit verschil in de 7 jaar daarna is verbeterd, maar persoonlijk zou ik deze functie niet op een grote tafel gebruiken totdat ik de prestaties ervan vergeleken heb met andere oplossingen.

ORIGINEEL POST 2009 STAAT ONDER:

Er zijn veel manieren om dit te doen, met sterk variërende prestaties. Hier is een bijzonder goed geoptimaliseerde oplossing, van Medianen, ROW_NUMBER’s en prestaties. Dit is een bijzonder optimale oplossing als het gaat om de daadwerkelijke I/O’s die tijdens de uitvoering worden gegenereerd – het lijkt duurder dan andere oplossingen, maar het is eigenlijk veel sneller.

Die pagina bevat ook een bespreking van andere oplossingen en details over prestatietests. Let op het gebruik van een unieke kolom als disambiguator voor het geval er meerdere rijen zijn met dezelfde waarde van de mediaankolom.

Zoals bij alle scenario’s voor databaseprestaties, moet u altijd proberen een oplossing uit te testen met echte gegevens op echte hardware – u weet nooit wanneer een wijziging in de optimalisatie van SQL Server of een eigenaardigheid in uw omgeving een normaal snelle oplossing langzamer maakt.

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;

Antwoord 2, autoriteit 95%

Als u SQL 2005 of beter gebruikt, is dit een mooie, eenvoudige mediaanberekening voor een enkele kolom in een tabel:

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

Antwoord 3, autoriteit 55%

In SQL Server 2012 moet u PERCENTILE_CONT gebruiken :

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

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

Antwoord 4, autoriteit 13%

Mijn oorspronkelijke snelle antwoord was:

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

Hiermee krijgt u in één klap de mediaan- en interkwartielafstand. Als je echt maar één rij wilt die de mediaan is, verwijder dan het commentaar van de waar-clausule.

Als je dat in een uitlegplan stopt, is 60% van het werk het sorteren van de gegevens, wat onvermijdelijk is bij het berekenen van positieafhankelijke statistieken zoals deze.

Ik heb het antwoord aangepast om de uitstekende suggestie van Robert Ševčík-Robajz in de onderstaande opmerkingen te volgen:

;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

Dit zou de juiste mediaan- en percentielwaarden moeten berekenen als u een even aantal gegevensitems heeft. Nogmaals, verwijder de commentaar bij de laatste waar-clausule als je alleen de mediaan wilt en niet de hele percentielverdeling.

Antwoord 5, autoriteit 11%

Nog beter:

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

Van de meester zelf, Itzik Ben-Gan!

Antwoord 6, autoriteit 6%

MS SQL Server 2012 (en later) heeft de functie PERCENTILE_DISC die een specifiek percentiel berekent voor gesorteerde waarden. PERCENTILE_DISC (0.5) berekent de mediaan – https://msdn.microsoft.com/ nl-nl/bibliotheek/hh231327.aspx

Antwoord 7, autoriteit 2%

Eenvoudig, snel, nauwkeurig

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)  

Antwoord 8, autoriteit 2%

Als u de CREATE AGGREGE-functie in SQL Server wilt gebruiken, is dit hoe het te doen. Het op deze manier doen heeft het voordeel om schone query’s te kunnen schrijven. Merk op dat dit proces kan worden aangepast om een ​​percentagelij vrij eenvoudig te berekenen.

Maak een nieuw Visual Studio-project en stel het doelraamwerk in op .NET 3.5 (dit is voor SQL 2008, het kan verschillen in SQL 2012). Maak vervolgens een klassenbestand en plaats de volgende code of C # Equivalent:

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

Compileer het en kopieer het DLL- en PDB-bestand naar uw SQL Server-machine en voer de volgende opdracht uit in 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

U kunt dan een query schrijven om de mediaan zo te berekenen:
Selecteer dbo.median (veld) van tafel

Antwoord 9, Autoriteit 2%

Ik kwam deze pagina gewoon tegen terwijl je op zoek bent naar een op set gebaseerde oplossing voor Mediaan. Na het bekijken van enkele van de oplossingen, kwam ik het volgende op. Hoop is helpt / werken.

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

Antwoord 10, autoriteit 2%

De volgende zoekopdracht retourneert de mediaanuit een lijst met waarden in één kolom. Het kan niet worden gebruikt als of samen met een aggregatiefunctie, maar je kunt het nog steeds gebruiken als een subquery met een WHERE-component in de inner select.

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

Antwoord 11, autoriteit 2%

Hoewel de oplossing van Justin Grant solide lijkt, ontdekte ik dat wanneer je een aantal dubbele waarden binnen een bepaalde partitiesleutel hebt, de rijnummers voor de ASC-duplicaatwaarden in de verkeerde volgorde eindigen, zodat ze niet goed uitgelijnd zijn.

Hier is een fragment van mijn resultaat:

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

Ik heb de code van Justin gebruikt als basis voor deze oplossing. Hoewel niet zo efficiënt gezien het gebruik van meerdere afgeleide tabellen, lost het wel het probleem van de rijvolgorde op dat ik tegenkwam. Alle verbeteringen zijn welkom, aangezien ik niet zo ervaren ben in 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

Antwoord 12

Justin’s voorbeeld hierboven is erg goed. Maar die primaire sleutelbehoefte moet heel duidelijk worden vermeld. Ik heb die code in het wild gezien zonder de sleutel en de resultaten zijn slecht.

De klacht die ik krijg over de Percentile_Cont is dat het je geen werkelijke waarde uit de dataset geeft.
Gebruik Percentile_Disc om een “mediaan” te krijgen die een werkelijke waarde uit de dataset is.

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

Antwoord 13

Schrijf in een UDF:

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

Antwoord 14

mediaan vinden

Dit is de eenvoudigste methode om de mediaan van een attribuut te vinden.

Select round(S.salary,4) median from employee S 
where (select count(salary) from station 
where salary < S.salary ) = (select count(salary) from station
where salary > S.salary)

Antwoord 15

Zie andere oplossingen voor mediane berekening in SQL hier:
“Eenvoudige manier om mediaan te berekenen met MySQL ” (de oplossingen zijn meestal Vendor-onafhankelijk).

Antwoord 16

Voor een continue variabele / maatregel ‘Col1’ van ‘Table1’

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

Antwoord 17

Veel mogelijk moeten we mediaan niet alleen voor de hele tafel berekenen, maar voor aggregaten met betrekking tot een ID. Met andere woorden, bereken de mediaan voor elke ID in onze tabel, waar elke ID veel records heeft. (Gebaseerd op de oplossing die is bewerkt door @Gdoron: goede prestaties en werken in vele 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;

Hopelijk helpt het.

Antwoord 18

Voor grootschalige datasets kunt u deze GIST proberen:

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

Het werkt door de afzonderlijke waarden die u in uw set zou vinden (zoals leeftijden of geboortejaar, enz.) te aggregeren en gebruikt SQL-vensterfuncties om elke percentielpositie te vinden die u in de query opgeeft.

Antwoord 19

Een enkele instructie gebruiken – Een manier is om de vensterfunctie ROW_NUMBER(), COUNT() te gebruiken en de subquery te filteren. Hier is het gemiddelde salaris te vinden:

SELECT AVG(e_salary) 
 FROM                                                             
    (SELECT 
      ROW_NUMBER() OVER(ORDER BY e_salary) as row_no, 
      e_salary,
      (COUNT(*) OVER()+1)*0.5 AS row_half
     FROM Employee) t
 WHERE row_no IN (FLOOR(row_half),CEILING(row_half))

Ik heb soortgelijke oplossingen via het net gezien met FLOOR en CEILING, maar ik heb geprobeerd een enkele instructie te gebruiken. (bewerkt)

Antwoord 20

Ik wilde zelf een oplossing bedenken, maar mijn brein struikelde en viel onderweg. Ik denkdat het werkt, maar vraag me niet om het ‘s ochtends uit te leggen. 😛

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)

Antwoord 21

--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]

Antwoord 22

Dit werkt met 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
)

Antwoord 23

Voor nieuwelingen zoals ikzelf die de basis leren, vind ik dit voorbeeld persoonlijk gemakkelijker te volgen, omdat het gemakkelijker is om precies te begrijpen wat er gebeurt en waar de mediaanwaarden vandaan komen…

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

In absoluut ontzag voor sommige van de bovenstaande codes!!!

Antwoord 24

Dit is een zo eenvoudig mogelijk antwoord. Werkte goed met mijn gegevens. Als u bepaalde waarden wilt uitsluiten, voegt u gewoon een where-clausule toe aan de inner select.

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

Antwoord 25

De volgende oplossing werkt onder deze veronderstellingen:

• Geen dubbele waarden
• Geen NULL’s

Code:

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

Antwoord 26

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

Antwoord 27

Ik probeer met verschillende alternatieven, maar omdat mijn gegevensrecords herhaalde waarden hebben, lijken de ROW_NUMBER-versies geen keuze voor mij. Dus hier de query die ik gebruikte (een versie met 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;

Antwoord 28

Voortbouwend op het antwoord van Jeff Atwood hierboven is het met GROUP BY en een gecorreleerde subquery om de mediaan voor elke groep te krijgen.

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

Antwoord 29

Voor uw vraag had Jeff Atwood al de eenvoudige en effectieve oplossing gegeven. Maar als u op zoek bent naar een alternatieve benadering om de mediaan te berekenen, zal onderstaande SQL-code u helpen.

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;

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