SQL Server 2000中的Median

Question

对于偶数行，下表中位数的公式为(104.5 + 108)/2，对于奇数行，下表中位数的公式为108

Total       Total

100         100
101         101
104.5       104.5
108         108
108.3       108.3
112         112
            114

下面的代码可以在SQL Server2008中运行，但不能在SQL Server2000中运行，因为它不理解row_number()和over。

我们如何更改下面的代码以使其在SQL Server 2000上工作？

select avg(Total) median from
(select Total, 
rnasc = row_number() over(order by Total),
rndesc = row_number() over(order by Total desc)
 from [Table] 
) b
where rnasc between rndesc - 1 and rndesc + 1

Answer 1

如果你只想要一个中位数，你可以使用这个简单的查询。

SELECT
(
  (SELECT MAX(Total) FROM
    (SELECT TOP 50 PERCENT Total FROM [Table] ORDER BY Total) AS BottomHalf)
  +
  (SELECT MIN(Total) FROM
    (SELECT TOP 50 PERCENT Total FROM [Table] ORDER BY Total DESC) AS TopHalf)
) / 2.0 AS Median

来源：Function to Calculate Median in Sql Server

Answer 2

SELECT Median = AVG(Total) FROM 
( 
    SELECT Total FROM ( 
        SELECT TOP 1 Total = Total * 1.0 FROM 
        ( 
            SELECT TOP 50 PERCENT Total 
            FROM dbo.[Table] ORDER BY Total 
        ) AS sub_a 
        ORDER BY 1 DESC 
    ) AS sub_1 
    UNION ALL 
    SELECT Total FROM ( 
        SELECT TOP 1 Total = Total * 1.0 FROM 
        ( 
            SELECT TOP 50 PERCENT Total 
            FROM dbo.[Table] ORDER BY Total DESC 
        ) AS sub_b 
        ORDER BY 1 
    ) AS sub_2 
) AS median;