SQL Server 2000 中的中位数

发布于 2024-12-03 04:30:08 字数 600 浏览 2 评论 0原文

对于偶数行，下表的中位数公式为 (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 Server 2008，但不适用于 SQL Server 2000，因为它不理解 row_number( ） 并结束。

我们怎样才能改变下面的代码使其在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

原文

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

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一身仙ぐ女味 2024-12-10 04:30:08

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

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 中计算中位数的函数

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

Source: Function to Calculate Median in Sql Server

回复收藏 0 原文

星 2024-12-10 04:30:08

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;

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;

回复收藏 0 原文

~没有更多了~