[sql] SQL Server에서 중앙값을 계산하는 함수

2019 업데이트 : 이 답변을 작성한 후 10 년 동안 더 많은 솔루션이 발견되어 더 나은 결과를 얻을 수 있습니다. 또한 이후 SQL Server 릴리스 (특히 SQL 2012)에서는 중앙값을 계산하는 데 사용할 수있는 새로운 T-SQL 기능이 도입되었습니다. SQL Server 릴리스는 다양한 중앙 솔루션의 성능에 영향을 줄 수있는 쿼리 최적화 프로그램도 개선했습니다. Net-net, 2009 년 원본 게시물은 여전히 ​​문제 없지만 최신 SQL Server 앱에 대한 더 나은 솔루션이있을 수 있습니다. https://sqlperformance.com/2012/08/t-sql-queries/median 에서 훌륭한 리소스 인 2012 년부터이 기사를 살펴보십시오.

이 기사에서는 다음 패턴이 다른 모든 대안보다 훨씬 빠르며 최소한 테스트 한 간단한 스키마에서 더 빠르다는 것을 발견했습니다. 이 솔루션은 PERCENTILE_CONT테스트 된 가장 느린 솔루션 ( ) 보다 373 배 더 빠릅니다 (!!!) . 이 트릭에는 두 가지 별도의 쿼리가 필요하지만 모든 경우에 실용적이지는 않을 수 있습니다. 또한 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 이상인 경우 마일리지가 다를 수 있습니다. perf가 중앙값 계산에 중요한 경우에는이 기사에서 권장하는 몇 가지 옵션을 시도하고 성능을 테스트하여 스키마에 가장 적합한 옵션을 찾아 보는 것이 좋습니다.

또한 위의 링크 된 기사 에서이 내장 함수가 가장 빠른 솔루션보다 373 배 느리기 때문에이 질문 PERCENTILE_CONT에 대한 다른 답변 중 하나에서 권장 되는 (SQL Server 2012의 새로운 기능) 기능 을 사용하는 데 특히주의 해야합니다. 이 불일치가 7 년 후에 개선되었을 수도 있지만 개인적으로 성능을 다른 솔루션과 비교할 때까지 큰 테이블에서이 기능을 사용하지 않을 것입니다.

2009 년 최초 게시일 :

성능을 극적으로 변화시키는 방법에는 여러 가지가 있습니다. 다음은 중간 값, ROW_NUMBER 및 성능 에서 최적화 된 솔루션 중 하나 입니다. 이는 실행 중에 생성 된 실제 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;

답변

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

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

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

;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

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

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)