SQL Server에서 중위수 계산 기능

source

SQL Server에서 중위수 계산 기능

factcode 2023. 4. 29. 09:57

SQL Server에서 중위수 계산 기능

MSDN에 따르면 Transact-SQL에서는 중위수를 집계 함수로 사용할 수 없습니다.그러나 Create Aggregate 함수, 사용자 정의 함수 또는 다른 방법을 사용하여 이 기능을 만들 수 있는지 알고 싶습니다.

집계 쿼리에서 중위수 값(숫자 데이터 유형으로 가정)을 계산할 수 있는 가장 좋은 방법은 무엇입니까?

SQL 2005 이상을 사용하는 경우 표의 단일 열에 대한 간단하고 단순한 중위수 계산입니다.

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

2019 업데이트: 이 답변을 쓴 지 10년 동안 더 나은 결과를 낼 수 있는 솔루션이 더 많이 발견되었습니다.또한 이후 SQL Server 릴리스(특히 SQL 2012)에서는 중위수를 계산하는 데 사용할 수 있는 새로운 T-SQL 기능이 도입되었습니다.SQL Server 릴리스에서는 다양한 중앙 관리 솔루션의 성능에 영향을 줄 수 있는 쿼리 최적화 도구도 개선되었습니다.Net-net, 원래 2009년 게시물은 여전히 괜찮지만 최신 SQL Server 앱을 위한 더 나은 솔루션이 있을 수 있습니다.2012년의 이 기사를 살펴보십시오. 이 기사는 훌륭한 자료입니다. https://sqlperformance.com/2012/08/t-sql-queries/median

이 기사에서는 다음 패턴이 다른 모든 대안보다 적어도 그들이 테스트한 간단한 스키마에서 훨씬 더 빠르다는 것을 발견했습니다. (373릅보션루린장은솔루션)3▁the▁was이!!!▁this다▁37▁solution) 더 빨랐습니다.PERCENTILE_CONT 테스트 완료.) 솔루션을 테스트했습니다.이 방법에는 두 개의 별도 쿼리가 필요하며, 모든 경우에 실용적이지 않을 수 있습니다.또한 SQL 2012 이상이 필요합니다.

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;

물론 2012년에 한 스키마에서 한 번 테스트한 결과가 우수하다고 해서 마일리지가 달라질 수 있습니다. 특히 SQL Server 2014 이상을 사용하는 경우에는 더욱 그렇습니다.성능이 중위수 계산에 중요한 경우 해당 문서에서 권장하는 몇 가지 옵션을 사용하여 스키마에 가장 적합한 옵션을 찾았는지 확인하는 것이 좋습니다.

이 질문에 대한 다른 답변 중 하나에 권장되는 (SQL Server 2012의 새로운 기능) 기능을 사용하는 것도 특히 주의해야 합니다. 위에 링크된 기사에서는 이 기본 제공 기능이 가장 빠른 솔루션보다 373배 더 느리다고 판단했기 때문입니다.그 이후 7년 동안 이러한 격차가 개선되었을 가능성이 있지만, 개인적으로 다른 솔루션과 비교하여 성능을 확인하기 전까지는 이 기능을 큰 테이블에서 사용하지 않을 것입니다.

2009년 원본 게시물은 다음과 같습니다.

이를 위한 다양한 방법이 있으며, 성능은 크게 다릅니다.여기에 중위수, ROW_NUMBERs 및 성능에서 특별히 최적화된 솔루션이 하나 있습니다.이는 실행 중에 생성되는 실제 I/O와 관련하여 특히 최적의 솔루션입니다. 다른 솔루션보다 비용이 더 많이 들어 보이지만 실제로는 훨씬 더 빠릅니다.

이 페이지에는 다른 솔루션 및 성능 테스트 세부 정보도 나와 있습니다.중위수 열의 값이 동일한 행이 여러 개 있는 경우 고유한 열을 구분 기호로 사용합니다.

모든 데이터베이스 성능 시나리오와 마찬가지로 항상 실제 하드웨어의 실제 데이터를 사용하여 솔루션을 테스트합니다. SQL Server의 최적화 도구를 변경하거나 환경의 특수성으로 인해 정상 속도의 솔루션이 느려지는 경우는 알 수 없습니다.

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;

SQL Server 2012에서는 PERCENTILE_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

참고 항목: http://blog.sqlauthority.com/2011/11/20/sql-server-introduction-to-percentile_cont-analytic-functions-introduced-in-sql-server-2012/

저의 원래 빠른 답변은 다음과 같습니다.

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

이렇게 하면 중위수와 사분위간 범위를 한 번에 얻을 수 있습니다.중위수인 행을 하나만 원하는 경우 where 절의 주석을 제거합니다.

그것을 설명 계획에 집어넣으면, 60%의 작업이 데이터를 정렬하는 것인데, 이와 같이 위치 의존적인 통계를 계산할 때 피할 수 없습니다.

저는 아래 댓글에서 Robert Sevchík-Robajz의 훌륭한 제안을 따르도록 답변을 수정했습니다.

;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

데이터 항목이 짝수일 때 올바른 중위수 및 백분위수 값을 계산해야 합니다.전체 백분위수 분포가 아닌 중위수만 원하는 경우 최종 where 절의 주석을 제거합니다.

훨씬 더 좋은 것:

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

이츠크 벤간의 주인으로부터!

MS SQL Server 2012 이상에는 정렬된 값에 대한 특정 백분위수를 계산하는 PERCENTILE_DISC 함수가 있습니다.PERCENCILE_DISC(0.5)가 중위수를 계산합니다. - https://msdn.microsoft.com/en-us/library/hh231327.aspx

간편하고, 빠르고, 정확함

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)

SQL Server에서 Aggregate 생성 기능을 사용하려면 다음과 같이 하십시오.이렇게 하면 깨끗한 쿼리를 작성할 수 있다는 이점이 있습니다.이 공정은 백분위수 값을 매우 쉽게 계산할 수 있도록 조정할 수 있습니다.

새 Visual Studio 프로젝트를 만들고 대상 프레임워크를 로 설정합니다.NET 3.5(이는 SQL 2008용이며 SQL 2012에서는 다를 수 있음).)그런 다음 클래스 파일을 만들고 다음 코드 또는 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

그런 다음 컴파일하여 DLL 및 PDB 파일을 SQL Server 시스템에 복사하고 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

그런 다음 쿼리를 작성하여 다음과 같이 중위수를 계산할 수 있습니다.중위수(필드) 시작 표

중앙값에 대한 세트 기반 솔루션을 찾던 중 이 페이지를 발견했습니다.여기서 몇 가지 해결책을 살펴본 후, 저는 다음과 같은 것을 생각해냈습니다.희망은 도움이 됩니다.

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

다음 쿼리는 한 열의 값 리스트에서 중위수를 반환합니다.집계 함수로 또는 집계 함수와 함께 사용할 수 없지만 내부 선택에 WHERE 절이 있는 하위 쿼리로 사용할 수 있습니다.

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

Justin Grant 솔루션이 견고해 보이지만 지정된 파티션 키 내에 중복 값이 여러 개 있을 때 ASC 중복 값의 행 번호가 순서를 벗어나 제대로 정렬되지 않는다는 것을 발견했습니다.

여기 제 결과의 단편이 있습니다.

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

저는 저스틴의 코드를 이 솔루션의 기초로 사용했습니다.파생 테이블을 여러 개 사용할 경우 효율적이지는 않지만 발생한 행 순서 문제를 해결합니다.저는 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

UDF에 다음과 같이 기록합니다.

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

위의 저스틴의 예는 매우 좋습니다.그러나 기본 키의 필요성은 매우 명확하게 명시되어야 합니다.저는 키가 없는 코드를 야생에서 본 적이 있는데 결과가 좋지 않습니다.

Percentile_Cont에 대한 불만 사항은 데이터 세트에서 실제 값을 얻을 수 없다는 것입니다.데이터 집합에서 실제 값인 "중간값"으로 이동하려면 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

단일 문 사용 - 한 가지 방법은 ROW_NUMBER(), COUNT() 윈도우 기능을 사용하고 하위 쿼리를 필터링하는 것입니다.다음은 급여의 중간값을 확인하기 위한 것입니다.

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

저는 FLOOR와 CEMINGLE을 사용하여 비슷한 솔루션을 인터넷을 통해 보았지만 단일 문을 사용하려고 했습니다.(계속)

중위수 소견

이 방법은 속성의 중위수를 찾는 가장 간단한 방법입니다.

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)

SQL에서 중위수 계산을 위한 다른 솔루션인 "MySQL로 중위수를 계산하는 간단한 방법"을 참조하십시오(솔루션은 대부분 벤더에 의존하지 않습니다).

위의 Jeff Atwood의 답변을 바탕으로 각 그룹의 중위수를 구하는 GROUP BY 및 관련 하위 쿼리가 있습니다.

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

연속 변수의 경우/'table1'의 'col1' 측정

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

종종 전체 표뿐만 아니라 일부 ID에 대한 집계에 대한 중위수를 계산해야 할 수도 있습니다.즉, 테이블의 각 ID에 대한 중위수를 계산합니다. 여기서 각 ID는 많은 레코드를 가지고 있습니다. (@gdoron이 편집한 솔루션을 기준으로 함: 우수한 성능과 많은 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;

도움이 되길 바랍니다.

대규모 데이터 세트의 경우 다음 GIST를 사용할 수 있습니다.

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

이는 집합에서 찾을 수 있는 고유한 값(예: 나이, 출생 연도 등)을 집계하여 작동하며 SQL 창 기능을 사용하여 쿼리에서 지정한 백분위수 위치를 찾습니다.

직원 표에서 급여의 중위수 값을 가져오는 방법

with cte as (select salary, ROW_NUMBER() over (order by salary asc) as num from employees)

select avg(salary) from cte where num in ((select (count(*)+1)/2 from employees), (select (count(*)+2)/2 from employees));

혼자 해결책을 찾고 싶었지만 도중에 머리가 걸려 넘어졌습니다.효과가 있는 것 같은데 아침에 설명해달라고 하지 마세요 :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)

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

이 기능은 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

)

기본적인 것들을 배우는 저 같은 신입생들에게, 저는 개인적으로 이 예를 따르는 것이 더 쉽다고 생각합니다. 정확히 무슨 일이 일어나고 있고 중위수 값이 어디서 나오는지를 이해하는 것이 더 쉽기 때문입니다.

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

하지만 위의 몇몇 코드에 절대적으로 경외감을 느낍니다!!!

이것은 제가 생각해 낼 수 있는 가장 간단한 답입니다.내 데이터로 잘 작동했습니다.특정 값을 제외하려면 내부 선택에 where 절을 추가합니다.

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

다음 솔루션은 다음과 같은 가정 하에서 작동합니다.

중복 값 없음
NULL 없음

코드:

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

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

여러 가지 대안을 시도해 보았지만, 데이터 레코드에 반복적인 값이 있기 때문에 ROW_NUMBER 버전은 선택할 수 없는 것 같습니다.다음은 제가 사용한 쿼리(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;

질문에 대해, Jeff Atwood는 이미 간단하고 효과적인 해결책을 제시했습니다.그러나 중위수를 계산할 수 있는 다른 방법을 찾고 있는 경우 SQL 코드 아래에서 도움이 됩니다.

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;

MySQL에서 중위수를 계산하려는 경우 이 Github 링크가 유용합니다.

언급URL : https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server

'source' 카테고리의 다른 글

상위 1개 필드를 선택하고 로컬 변수에 할당 (0)	2023.05.04
mongodb 커서 ID가 유효하지 않은 오류입니다. (0)	2023.04.29
Eclipse에서 변수 이름을 빠르게 대문자로 바꿀 수 있는 방법이 있습니까? (0)	2023.04.29
원하는 diff 도구/뷰어를 사용하여 'gitdiff' 출력을 보려면 어떻게 해야 합니까? (0)	2023.04.29
SQL Server에서 쿼리 실행 계획을 얻으려면 어떻게 해야 합니까? (0)	2023.04.29

현재글SQL Server에서 중위수 계산 기능

JavaScript, C, Wordpress, Excel, java, REACTJS, Vuex, sql-server, spring-boot, MariaDB, vuejs2, asp.net, git, MySQL, Python, oracle, jQuery, AngularJS, json, php,

Today :
Yesterday :

factcode

SQL Server에서 중위수 계산 기능

SQL Server에서 중위수 계산 기능

'source' 카테고리의 다른 글

'source'의 다른글

티스토리툴바

« 2024/07 »
일	월	화	수	목	금	토
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

SQL Server에서 중위수 계산 기능

SQL Server에서 중위수 계산 기능

'source' 카테고리의 다른 글

'source'의 다른글

관련글

티스토리툴바