wake-up-neo.net

Funktion zum Berechnen des Medians in SQL Server

Gemäß MSDN ist Median in Transact-SQL nicht als Aggregatfunktion verfügbar. Ich möchte jedoch wissen, ob es möglich ist, diese Funktionalität zu erstellen (mithilfe der Funktion Create Aggregate , einer benutzerdefinierten Funktion oder einer anderen Methode). 

Was wäre der beste Weg (wenn möglich), dies zu tun - die Berechnung eines Medianwerts (unter der Annahme eines numerischen Datentyps) in einer Aggregatabfrage zuzulassen?

191
Yaakov Ellis

Es gibt viele Möglichkeiten, dies zu tun, wobei die Leistung dramatisch variiert. Hier ist eine besonders gut optimierte Lösung aus Median, ROW_NUMBERs und Performance. Dies ist eine besonders optimale Lösung, wenn es um tatsächliche E/A-Vorgänge geht, die während der Ausführung generiert werden. Sie sehen kostspieliger aus als andere Lösungen, sind jedoch tatsächlich viel schneller.

Auf dieser Seite werden auch andere Lösungen und Details zum Leistungstest erläutert. Beachten Sie die Verwendung einer eindeutigen Spalte als Disambiguator für den Fall, dass mehrere Zeilen mit demselben Wert der Medianspalte vorhanden sind.

Wie bei allen Datenbankleistungsszenarien sollten Sie immer versuchen, eine Lösung mit realen Daten auf echter Hardware zu testen. Sie wissen nie, wann eine Änderung des SQL Server-Optimierers oder eine Besonderheit in Ihrer Umgebung eine normalerweise schnelle Lösung langsamer macht.

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;
119
Justin Grant

Wenn Sie SQL 2005 oder besser verwenden, ist dies eine einfache, einfache Mittelwertberechnung für eine einzelne Spalte in einer Tabelle:

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
172
Jeff Atwood

In SQL Server 2012 sollten Sie PERCENTILE_CONT verwenden:

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

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

70
Simon_Weaver

Meine ursprüngliche schnelle Antwort war:

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

Dadurch erhalten Sie den Median- und Interquartilbereich auf einen Schlag. Wenn Sie wirklich nur eine Zeile wünschen, die den Median darstellt, können Sie die where-Klausel auskommentieren.

Wenn Sie das in einen EXPLAIN-Plan stecken, sortieren 60% der Arbeit die Daten, was bei der Berechnung dieser positionsabhängigen Statistiken nicht zu vermeiden ist.

Ich habe die Antwort geändert, um dem ausgezeichneten Vorschlag von Robert Ševčík-Robajz in den folgenden Kommentaren zu folgen:

;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

Dies sollte die korrekten Median- und Perzentilwerte berechnen, wenn Sie eine gerade Anzahl von Datenelementen haben. Kommentieren Sie die letzte where-Klausel erneut, wenn Sie nur den Median und nicht die gesamte Perzentilverteilung wünschen.

21
Sir Wobin

Noch besser:

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

Vom Meister selbst, Itzik Ben-Gan !

MS SQL Server 2012 (und höher) verfügt über die PERCENTILE_DISC-Funktion, die ein bestimmtes Perzentil für sortierte Werte berechnet. PERCENTILE_DISC (0.5) berechnet den Median - https://msdn.Microsoft.com/en-us/library/hh231327.aspx

6
enkryptor

Wenn Sie die Funktion zum Erstellen von Aggregaten in SQL Server verwenden möchten, gehen Sie wie folgt vor. Wenn Sie dies auf diese Weise tun, haben Sie den Vorteil, dass Sie saubere Abfragen schreiben können. Beachten Sie, dass dieser Prozess angepasst werden kann, um einen Perzentilwert relativ einfach zu berechnen.

Erstellen Sie ein neues Visual Studio-Projekt, und legen Sie das Zielframework auf .NET 3.5 fest (dies gilt für SQL 2008, in SQL 2012 kann es anders sein). Erstellen Sie dann eine Klassendatei und geben Sie den folgenden Code oder das entsprechende C # ein:

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))) / [email protected]
      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

Dann kompilieren Sie es und kopieren Sie die DLL - und PDB-Datei auf Ihren SQL Server-Computer, und führen Sie den folgenden Befehl in SQL Server aus:

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

Sie können dann eine Abfrage schreiben, um den Median wie folgt zu berechnen: SELECT dbo.Median (Feld) FROM-Tabelle

4
Rono

Einfach, schnell, genau 

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)  
4
Tobbi

Die folgende Abfrage gibt den Median aus einer Liste von Werten in einer Spalte zurück. Sie kann nicht als oder zusammen mit einer Aggregatfunktion verwendet werden. Sie können sie jedoch weiterhin als Unterabfrage mit einer WHERE-Klausel im inneren select verwenden.

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
3
PyQL

Ich bin gerade auf diese Seite gestoßen, als ich nach einer medienbasierten Lösung gesucht habe. Nachdem ich mir hier einige Lösungen angesehen hatte, kam ich zu folgenden Themen. Hoffnung ist hilft/arbeitet.

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
3
brian

Obwohl die Lösung von Justin Grant solide erscheint, habe ich festgestellt, dass die Zeilennummern der ASC-Duplikatwerte nicht in der richtigen Reihenfolge liegen, wenn Sie mehrere doppelte Werte in einem bestimmten Partitionsschlüssel haben.

Hier ist ein Ausschnitt aus meinem Ergebnis: 

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

Ich habe Justins Code als Grundlage für diese Lösung verwendet. Obwohl es bei Verwendung mehrerer abgeleiteter Tabellen nicht so effizient ist, wird das Problem mit der Zeilenreihenfolge behoben. Jede Verbesserung wäre willkommen, da ich in T-SQL nicht so erfahren bin.

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
3
Jeff Sisson

Justins Beispiel oben ist sehr gut. Dieses Primärschlüsselbedürfnis sollte jedoch sehr deutlich gemacht werden. Ich habe diesen Code in der Wildnis ohne den Schlüssel gesehen und die Ergebnisse sind schlecht.

Die Beschwerde, die ich über Percentile_Cont bekomme, ist, dass Sie keinen tatsächlichen Wert aus dem Datensatz erhalten. Um einen Median zu erhalten, der ein tatsächlicher Wert aus dem Datensatz ist, verwenden Sie 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
2
Brian Nordberg

Schreiben Sie in eine UDF:

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

Für eine stetige Variable/Takt "col1" aus "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
1
karishma kavle

Weitere Lösungen für die Medianberechnung in SQL finden Sie hier: " Einfache Methode zur Berechnung des Median mit MySQL " (die Lösungen sind meistens herstellerunabhängig).

1
Bill Karwin

Ich versuche es mit mehreren Alternativen, aber da meine Datensätze wiederholte Werte haben, scheinen die ROW_NUMBER-Versionen keine Wahl für mich zu sein. Also hier die Abfrage, die ich verwendet habe (eine Version mit 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;
0
Galled

Dies ist die optimale Lösung, um Mediane zu finden, die ich mir vorstellen kann. Die Namen im Beispiel basieren auf dem Beispiel von Justin. Stellen Sie sicher, dass ein Index für die Tabelle Sales.SalesOrderHeader mit den Indexspalten CustomerId und TotalDue in dieser Reihenfolge vorhanden ist.

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

UPDATE

Ich war ein bisschen unsicher, welche Methode die beste Leistung bietet. Deshalb habe ich einen Vergleich zwischen meiner Methode Justin Grants und Jeff Atwoods durchgeführt, indem eine Abfrage ausgeführt wurde, die auf allen drei Methoden in einem Batch basiert.

Ohne Index:

  • Mine 30%
  • Justin gewährt 13%
  • Jeff Atwoods 58%

Und mit Index

  • Mir 3%.
  • Justin gewährt 10% 
  • Jeff Atwoods 87%

Ich habe versucht zu sehen, wie gut die Abfragen skaliert werden, wenn Sie einen Index haben, indem Sie mehr Daten aus etwa 14.000 Zeilen um den Faktor 2 bis 512 erstellen, was am Ende etwa 7,2 Millionen Zeilen bedeutet. Hinweis Ich habe sichergestellt, dass das Feld CustomeId für jede einzelne Kopie eindeutig war, sodass der Anteil der Zeilen im Vergleich zur eindeutigen Instanz von CustomerId konstant gehalten wurde. Während ich dies tat, führte ich Hinrichtungen durch, bei denen ich danach den Index neu aufbaute, und ich stellte fest, dass sich die Ergebnisse bei einem Faktor von 128 mit den Daten, die ich auf diese Werte hatte, stabilisierten:

  • Mir 3%.
  • Justin gewährt 5% 
  • Jeff Atwoods 92%

Ich wunderte mich, wie die Leistung durch die Skalierung der Zeilenanzahl beeinflusst werden konnte, während die eindeutige CustomerId konstant gehalten wurde. Deshalb habe ich einen neuen Test eingerichtet, bei dem ich genau dies tat. Anstatt sich zu stabilisieren, divergierte das Chargenkostenverhältnis immer weiter. Statt etwa 20 Zeilen pro CustomerId pro Durchschnitt hatte ich am Ende etwa 10000 Zeilen pro eindeutiger ID. Die Zahlen wo:

  • Mine 4%
  • Justins 60%
  • Jeffs 35%

Ich stellte sicher, dass ich jede Methode korrekt implementierte, indem ich die Ergebnisse verglich. Meine Schlussfolgerung ist, dass die von mir verwendete Methode im Allgemeinen schneller ist, solange der Index vorhanden ist. Es wurde auch festgestellt, dass diese Methode für dieses bestimmte Problem in diesem Artikel empfohlen wird https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=5

Um die Leistung nachfolgender Aufrufe dieser Abfrage noch weiter zu verbessern, können Sie die Zählerinformationen in einer Hilfstabelle beibehalten. Sie können es sogar pflegen, indem Sie einen Auslöser für diese Aktualisierung verwenden und Informationen über die Anzahl der SalesOrderHeader-Zeilen in Abhängigkeit von CustomerId enthalten. Natürlich können Sie auch den Median einfach speichern.

0
Kaveh Hadjari

Für Neulinge wie mich, die die Grundlagen erlernen, ist es mir leichter, diesem Beispiel zu folgen, da es einfacher ist zu verstehen, was genau passiert und woher die mittleren Werte kommen ...

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 absoluter Ehrfurcht vor einigen der oben genannten Codes !!!

0
Justine
--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]
0
Gregg Silverman

Ich wollte selbst eine Lösung finden, aber mein Gehirn stolperte und fiel auf den Weg. Ich denke es funktioniert, aber frage mich nicht, es morgen früh zu erklären. : P

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)
0
Gavin

Für große Datensätze können Sie diese Gist versuchen:

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

Dabei werden die unterschiedlichen Werte zusammengefasst, die Sie in Ihrem Satz finden würden (z. B. Alter, Geburtsjahr usw.), und mithilfe von SQL-Fensterfunktionen können Sie die Perzentilposition ermitteln, die Sie in der Abfrage angeben.

0
Chris Knoll

Häufig müssen wir den Median nicht nur für die gesamte Tabelle berechnen, sondern für Aggregate in Bezug auf eine ID. Mit anderen Worten, berechnen Sie den Median für jede ID in unserer Tabelle, wobei jede ID viele Datensätze enthält. (basierend auf der von @gdoron bearbeiteten Lösung: gute Leistung und funktioniert in vielen SQL-Umgebungen)

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;

Ich hoffe es hilft.

0

Die folgende Lösung funktioniert unter diesen Annahmen:

  • Keine doppelten Werte
  • Keine NULLs

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

Aufbauend auf Jeff Atwoods Antwort hier oben ist es mit GROUP BY und einer zugehörigen Unterabfrage, um den Median für jede Gruppe zu ermitteln.

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
0
Jim B

Für Ihre Frage hatte Jeff Atwood bereits die einfache und effektive Lösung gegeben. Wenn Sie jedoch nach einem alternativen Ansatz zur Berechnung des Medians suchen, hilft Ihnen der folgende SQL-Code. 

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 [email protected]_no or [email protected][email protected]_even;

Wenn Sie den Median in MySQL berechnen möchten, ist dieser github link nützlich.

0

Medianfindung

Dies ist die einfachste Methode, um den Median eines Attributs zu ermitteln. 

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)
0
Nivesh Krishna

Dies funktioniert mit 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

)
0
SQLMason
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 [email protected]
0
Arie Yehieli

Dies ist eine so einfache Antwort wie ich könnte. Funktionierte gut mit meinen Daten. Wenn Sie bestimmte Werte ausschließen möchten, fügen Sie einfach eine where-Klausel zum inneren select hinzu.

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
0
John P.