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_CONT
die 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
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;
ANTWOORD 30
Dit is de meest optimale oplossing voor het vinden van medianen die ik kan bedenken. De namen in het voorbeeld zijn gebaseerd op Justin-voorbeeld. Zorg ervoor dat een index voor tabel
Sales.Salesorderheader bestaat met Index Columns Customerid en TotalDue in die volgorde.
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
Ik was een beetje onzeker over welke methode de beste prestaties levert, dus ik deed een vergelijking tussen mijn methode Justin Grants en Jeff Atwoods door een query uit te voeren op basis van alle drie de methoden in één batch en de batchkosten van elke query waren:
p>
Zonder index:
- Mijn 30%
- Justin schenkt 13%
- Jeff Atwoods 58%
En met index
- Mijn 3%.
- Justin schenkt 10%
- Jeff Atwoods 87%
Ik heb geprobeerd te zien hoe goed de query’s schalen als je een index hebt door meer gegevens te creëren van ongeveer 14.000 rijen met een factor 2 tot 512, wat uiteindelijk ongeveer 7,2 miljoen rijen betekent. Opmerking Ik zorgde ervoor dat het veld CustomeId uniek was voor elke keer dat ik een enkele kopie deed, dus het aandeel rijen in vergelijking met het unieke exemplaar van CustomerId werd constant gehouden. Terwijl ik dit deed, voerde ik uitvoeringen uit waarbij ik de index daarna herbouwde, en ik merkte dat de resultaten stabiliseerden rond een factor 128 met de gegevens die ik had voor deze waarden:
- Mijn 3%.
- Justin schenkt 5%
- Jeff Atwoods 92%
Ik vroeg me af hoe de prestatie kon worden beïnvloed door het aantal rijen te schalen, maar de unieke CustomerId constant te houden, dus heb ik een nieuwe test opgezet waarbij ik precies dit deed. In plaats van te stabiliseren, bleef de batchkostenverhouding divergeren, ook in plaats van ongeveer 20 rijen per CustomerId per gemiddelde had ik uiteindelijk ongeveer 10000 rijen per zo’n unieke ID. De nummers waar:
- Mijn 4%
- Justins 60%
- Jeffs 35%
Ik zorgde ervoor dat ik elke methode correct implementeerde door de resultaten te vergelijken.
Mijn conclusie is de gebruikte methode is over het algemeen sneller zolang de index bestaat. Ook merkte op dat deze methode is wat wordt aanbevolen voor dit specifieke probleem in dit artikel https://www.microsoftpressstore.com/articles/article.aspx?p=2314819& ;Seqnum=5
Een manier om de prestaties van latere oproepen naar deze query nog verder te verbeteren, is nog verder om de tell-informatie in een hulptafel te blijven. Je zou het zelfs kunnen onderhouden door een trigger te hebben die informatie bijwerkt en bezit met betrekking tot de telling van de Rijen van de Salesorderheader, afhankelijk van CustomerID, natuurlijk kun je de mediaan ook eenvoudig opslaan.