Funkcja do obliczania mediany w SQL Server
Zgodnie z MSDN Mediana nie jest dostępna jako funkcja agregująca w Transact-SQL. Chciałbym się jednak dowiedzieć, czy jest możliwe utworzenie tej funkcjonalności (używając funkcji Create Aggregate , funkcji zdefiniowanej przez użytkownika lub innej metody).
Jaki byłby najlepszy sposób (jeśli to możliwe), aby to zrobić - pozwolić na obliczenie wartości mediany (przy założeniu numerycznego typu danych) w zapytaniu zbiorczym?
30 answers
Istnieje wiele sposobów, aby to zrobić, z dramatycznie różnej wydajności. Oto jedno szczególnie dobrze zoptymalizowane rozwiązanie, z Medians, ROW_NUMBERs, and performance. Jest to szczególnie optymalne rozwiązanie, jeśli chodzi o rzeczywiste I/Os generowane podczas wykonywania-wygląda na droższe niż inne rozwiązania, ale w rzeczywistości jest znacznie szybsze.
Ta strona zawiera również omówienie innych rozwiązań i szczegóły testów wydajności. Zwróć uwagę na użycie unikalnej kolumny jako disambiguator w przypadku, gdy istnieje wiele wierszy o tej samej wartości środkowej kolumny.
Tak jak w przypadku wszystkich scenariuszy wydajności baz danych, zawsze staraj się przetestować rozwiązanie z prawdziwymi danymi na rzeczywistym sprzęcie - nigdy nie wiesz, kiedy zmiana optymalizatora SQL Server lub osobliwość w Twoim środowisku spowolni normalnie szybkie rozwiązanie.
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-14 18:43:16
Jeśli używasz SQL 2005 lub lepszego, jest to ładne, proste mediana obliczenia dla pojedynczej kolumny w tabeli:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-08 09:34:05
W SQL Server 2012 powinieneś użyć PERCENTYLE_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
Zobacz też: http://blog.sqlauthority.com/2011/11/20/sql-server-introduction-to-percentile_cont-analytic-functions-introduced-in-sql-server-2012/Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-05-24 22:10:26
Moja oryginalna szybka odpowiedź brzmiała:
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
To da ci medianę i przedział międzykwartylowy za jednym zamachem. Jeśli naprawdę chcesz tylko jeden wiersz, który jest medianą, odkomentuj klauzulę where.
Kiedy wkładasz to do planu wyjaśnień, 60% pracy polega na sortowaniu danych, co jest nieuniknione przy obliczaniu statystyk zależnych od pozycji.
Zmieniłem odpowiedź, aby podążać za doskonałą sugestią Roberta Ševčíka-Robajza w komentarzach poniżej:
;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
To powinno obliczyć prawidłowe wartości mediany i percentyla, gdy masz parzystą liczbę pozycji danych. Ponownie, odkomentuj ostateczną klauzulę where, jeśli chcesz tylko medianę, a nie cały rozkład percentyla.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-09-26 20:39:17
Jeszcze lepiej:
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);
Od samego mistrza, Itzika Ben-Gana!Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-02-11 18:12:41
MS SQL Server 2012 (i nowsze) posiada funkcję PERCENTYLE_DISC, która oblicza określony percentyl dla posortowanych wartości. PERCENTYLE_DISC (0.5) obliczy medianę - https://msdn.microsoft.com/en-us/library/hh231327.aspx
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-25 13:00:18
Właśnie natknąłem się na tę stronę, szukając rozwiązania opartego na zestawie do mediany. Po zapoznaniu się z niektórymi rozwiązaniami tutaj, wymyśliłem następujące. Nadzieja pomaga/działa.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-24 14:43:20
Proste, szybkie, dokładne
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-08 19:29:28
Jeśli chcesz użyć funkcji Create Aggregate w SQL Server, oto jak to zrobić. Robienie tego w ten sposób ma tę zaletę, że jest w stanie pisać czyste zapytania. Należy zauważyć, że proces ten można dość łatwo dostosować do obliczenia wartości percentyla.
Utwórz nowy projekt Visual Studio i ustaw docelowy framework na. Net 3.5 (jest to dla SQL 2008, może być inaczej w SQL 2012). Następnie utwórz plik klasy i umieść następujący kod, lub odpowiednik c#:
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
Wtedy skompiluj go i skopiuj plik DLL i PDB na maszynę SQL Server i uruchom następujące polecenie w 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
Możesz następnie napisać zapytanie, aby obliczyć medianę w następujący sposób: Wybierz dbo.Mediana (pole) z tabeli
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-05-28 18:27:57
Poniższe zapytanie zwraca mediana z listy wartości w jednej kolumnie. Nie można jej używać jako funkcji agregującej lub razem z nią, ale nadal można jej używać jako zapytania podrzędnego z klauzulą WHERE w wewnętrznym 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-08 14:18:08
Powyższy przykład Justina jest bardzo dobry. Ale ta podstawowa potrzeba kluczowa powinna być wyraźnie określona. Widziałem ten kod w dziczy bez klucza i wyniki są złe.
Skarga, którą otrzymuję na temat Percentyle_cont jest taka, że nie da ci rzeczywistej wartości ze zbioru danych. Aby uzyskać "medianę", która jest rzeczywistą wartością ze zbioru danych, użyj Percentyle_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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-09-06 17:51:07
Chociaż rozwiązanie Justina Granta wydaje się solidne, odkryłem, że gdy masz liczbę zduplikowanych wartości w danym kluczu partycji, numery wierszy dla duplikatów ASC kończą się sekwencją, więc nie są prawidłowo wyrównane.
Oto fragment z mojego wyniku:
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
Użyłem kodu Justina jako podstawy dla tego rozwiązania. Chociaż nie jest tak wydajny, biorąc pod uwagę użycie wielu tabel pochodnych, rozwiązuje problem z porządkowaniem wierszy, z którym się spotkałem. Wszelkie ulepszenia byłoby mile widziane, ponieważ nie jestem tak doświadczony w 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-10 19:49:56
W UDF wpisz:
Select Top 1 medianSortColumn from Table T
Where (Select Count(*) from Table
Where MedianSortColumn <
(Select Count(*) From Table) / 2)
Order By medianSortColumn
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-13 00:03:42
Zobacz inne rozwiązania dla średnich obliczeń w SQL tutaj: "prosty sposób obliczania mediany za pomocą MySQL "(rozwiązania są w większości niezależne od dostawców).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:47:29
Dla zmiennej ciągłej / miary "col1" z "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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-09 23:52:53
Chciałam sama wypracować rozwiązanie, ale mój mózg się potknął i upadł po drodze. Myślę, że to działa, ale nie proś mnie o wyjaśnienie tego rano. : 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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-08-27 19:30:15
--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]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-08 09:23:37
To działa z 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
)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-06-13 13:08:41
Dla początkujących, takich jak ja, którzy uczą się samych podstaw, osobiście uważam ten przykład za łatwiejszy do naśladowania, ponieważ łatwiej jest dokładnie zrozumieć, co się dzieje i skąd pochodzą mediany...
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
W absolutnym podziwie dla niektórych z powyższych kodów!!!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-06-18 09:35:09
To jest tak prosta odpowiedź, jak tylko mogłem wymyślić. Dobrze mi poszło z moimi danymi. Jeśli chcesz wykluczyć pewne wartości, po prostu dodaj klauzulę where do wewnętrznego 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-07-16 22:27:44
Następujące rozwiązanie działa w tych założeniach:
- Brak duplikatów wartości
- No NULLs
Kod:
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) ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-17 21:02:04
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-25 08:50:45
Próbuję z kilkoma alternatywami, ale ponieważ moje rekordy danych mają powtarzające się wartości, wersje NUMER_ wiersza nie są dla mnie wyborem. Więc tutaj zapytanie użyłem (wersja z 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-07-31 21:55:39
Bazując na powyższej odpowiedzi Jeffa Atwooda, tutaj jest to z GROUP BY i skorelowanym zapytaniem podrzędnym, aby uzyskać medianę dla każdej grupy.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-03-11 18:01:40
Często może być konieczne obliczenie mediany nie tylko dla całej tabeli, ale dla agregatów w odniesieniu do jakiegoś ID. Innymi słowy, Oblicz medianę dla każdego ID w naszej tabeli, gdzie każdy ID ma wiele rekordów. (w oparciu o rozwiązanie edytowane przez @ gdoron: dobra wydajność i działa w wielu 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;
Mam nadzieję, że to pomoże.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-21 22:02:03
Na twoje pytanie Jeff Atwood podał już proste i skuteczne rozwiązanie. Ale jeśli szukasz alternatywnego podejścia do obliczenia mediany, poniżej kod SQL pomoże ci.
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;
Jeśli chcesz obliczyć medianę w MySQL, ten github link będzie przydatny.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-05 05:42:38
Jest to najbardziej optymalne rozwiązanie dla znalezienia medianów, jakie mogę wymyślić. Nazwy w przykładzie są oparte na przykładzie Justina. Upewnij się, że indeks dla tabeli Sprzedaż.SalesOrderHeader istnieje z kolumnami indeksu CustomerId i TotalDue w tej kolejności.
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
Byłem trochę niepewny, która metoda ma najlepszą wydajność, więc zrobiłem porównanie mojej metody Justin Grants i Jeff Atwoods uruchamiając zapytanie na podstawie wszystkich trzech metod w jednej partii i koszt każdego zapytania wynosił:
Bez indeksu:
- Moje 30%
- Justin Udziela 13%
- Jeff Atwoods 58%
Oraz z indeksem
- Moje 3%.
- Justin Udziela 10%
- Jeff Atwoods 87%
Próbowałem zobaczyć, jak dobrze skalują się zapytania, jeśli masz indeks, tworząc więcej danych z około 14 000 wierszy o współczynnik 2 do 512, co oznacza w końcu około 7,2 milionów wierszy. Uwaga, upewniłem się, że CustomeId field gdzie unique za każdym razem zrobiłem jedną kopię, więc proporcja wierszy w porównaniu do unikalnej instancji CustomerId była utrzymywana na stałym poziomie. Kiedy to robiłem, uruchomiłem egzekucje, gdzie przebudowałem indeks później, i zauważyłem, że wyniki ustabilizowały się na poziomie około współczynnika 128 z danymi, które miałem do tych wartości:
- Moje 3%.
- Justin Udziela 5%
- Jeff Atwoods 92%
Zastanawiałem się, jak na wydajność mogło wpłynąć skalowanie liczby wierszy ale zachowując unikalną stałą CustomerId, więc skonfigurowałem nowy test, w którym zrobiłem właśnie to. Teraz zamiast stabilizować, stosunek kosztów partii był rozbieżny, również zamiast około 20 wierszy na klienta średnio miałem w końcu około 10000 wierszy na taki unikalny identyfikator. Liczby, gdzie:
- Moje 4%
- Justins 60%
- Jeffs 35%
Upewniłem się, że zaimplementowałem każdą metodę poprawnie, porównując wyniki. Mój wniosek jest taki, że metoda, którą zastosowałem jest ogólnie szybciej, dopóki istnieje indeks. Zauważyłem również, że ta metoda jest tym, co jest zalecane dla tego konkretnego problemu w tym artykule https://www.microsoftpressstore.com/articles/article.aspx?p=2314819&seqNum=5
Sposobem na jeszcze większą poprawę wydajności kolejnych wywołań tego zapytania jest zachowanie informacji o liczbie w tabeli pomocniczej. Możesz nawet go utrzymać, mając WYZWALACZ tej aktualizacji i przechowując informacje dotyczące liczby SalesOrderHeader wiersze zależne od CustomerId, oczywiście możesz również łatwo zapisać medianę.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-07 09:05:03
Dla dużych zbiorów danych możesz wypróbować ten GIST:
Https://gist.github.com/chrisknoll/1b38761ce8c5016ec5b2
Działa poprzez agregację różnych wartości, które można znaleźć w zestawie (takich jak wiek, rok urodzenia, itp.) i używa funkcji okna SQL do zlokalizowania dowolnej pozycji percentyla określonej w zapytaniu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-13 00:24:08
Mediana Znalezienia
Jest to najprostsza metoda, aby znaleźć medianę atrybutu.
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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-09-05 17:34:52
AVG (column)
simmplest possible answer
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-31 02:46:49