![]() |
Function to Calculate Median in SQL Server - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server) +---- Thread: Function to Calculate Median in SQL Server (/Thread-Function-to-Calculate-Median-in-SQL-Server) |
Function to Calculate Median in SQL Server - harriettnplcw - 07-31-2023 According to [MSDN][1], Median is not available as an aggregate function in Transact-SQL. However, I would like to find out whether it is possible to create this functionality (using the [Create Aggregate][2] function, user defined function, or some other method). What would be the best way (if possible) to do this - allow for the calculation of a median value (assuming a numeric data type) in an aggregate query? [1]: [To see links please register here] [2]:[To see links please register here] RE: Function to Calculate Median in SQL Server - relaunch558416 - 07-31-2023 See other solutions for median calculation in SQL here: "[Simple way to calculate median with MySQL][1]" (the solutions are mostly vendor-independent). [1]: [To see links please register here] RE: Function to Calculate Median in SQL Server - hays174 - 07-31-2023 I wanted to work out a solution by myself, but my brain tripped and fell on the way. I *think* it works, but don't ask me to explain it in the morning. :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) RE: Function to Calculate Median in SQL Server - youjikqxgmr - 07-31-2023 --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] RE: Function to Calculate Median in SQL Server - tyrelltyrian642 - 07-31-2023 If you're using SQL 2005 or better this is a nice, simple-ish median calculation for a single column in a table: 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 RE: Function to Calculate Median in SQL Server - participial296 - 07-31-2023 I just came across this page while looking for a set based solution to median. After looking at some of the solutions here, I came up with the following. Hope is helps/works. 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 RE: Function to Calculate Median in SQL Server - electly522089 - 07-31-2023 This works with 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 ) RE: Function to Calculate Median in SQL Server - guardianess292375 - 07-31-2023 Simple, fast, accurate 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) RE: Function to Calculate Median in SQL Server - alayahjxfqrnxzft - 07-31-2023 In SQL Server 2012 you should use [PERCENTILE_CONT][1]: 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 See also : [To see links please register here] [1]: [To see links please register here] RE: Function to Calculate Median in SQL Server - splurged86327 - 07-31-2023 If you want to use the Create Aggregate function in SQL Server, this is how to do it. Doing it this way has the benefit of being able to write clean queries. Note this this process could be adapted to calculate a Percentile value fairly easily. Create a new Visual Studio project and set the target framework to .NET 3.5 (this is for SQL 2008, it may be different in SQL 2012). Then create a class file and put in the following code, or 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 Then compile it and copy the DLL and PDB file to your SQL Server machine and run the following command 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 You can then write a query to calculate the median like this: SELECT dbo.Median(Field) FROM Table |