SQL Server 2000 中的中位数 [英] Median in SQL Server 2000

问题描述

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

For even rows formula for median is (104.5 + 108)/2 for table below and For odd rows it is 108 for table below

Total       Total

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

下面的代码在 SQL Server 2008 中有效，但在 SQL Server 2000 中无效，因为它不理解 row_number() 和 over.

Code below works in SQL Server 2008 but not in SQL Server 2000 as it does not understand row_number() and over.

我们如何更改较低的代码以使其在 SQL Server 2000 上运行?

How can we change the lower code to make it work on 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

推荐答案

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

If you only want a median, you may use this simple query.

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

来源:Sql Server 中计算中值的函数

这篇关于SQL Server 2000 中的中位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！