sql - T-SQL 中的中值

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

我写了这个查询，它在计算正确的中位数时
行数是奇数:

WITH    a AS ( SELECT   Total ,
                        ROW_NUMBER() OVER ( ORDER BY CAST(Total AS FLOAT) ASC ) rownumber
               FROM     [Table] A
             ),
        b AS ( SELECT TOP 2
                        Total ,
                        isodd
               FROM     ( SELECT TOP 50 PERCENT
                                    Total ,
                                    rownumber % 2 isodd
                          FROM      a
                          ORDER BY  CAST(Total AS FLOAT) ASC
                        ) a
               ORDER BY CAST(total AS FLOAT) DESC
             )
    SELECT  *
    FROM    b

查找中位数的一般 T-SQL 查询是什么
两种情况？就像当行数是奇数时
当行数是偶数时？

我的查询是否可以扭曲，以便它可以在偶数行和奇数行情况下对中位数起作用？

Answer 1

几年前我写了一篇关于均值、中值和众数的博客。我鼓励你阅读它。

Calculating Mean, Median, and Mode with SQL Server

SELECT ((
        SELECT TOP 1 Total
        FROM   (
                SELECT  TOP 50 PERCENT Total
                FROM    [TABLE] A
                WHERE   Total IS NOT NULL
                ORDER BY Total
                ) AS A
        ORDER BY Total DESC) +
        (
        SELECT TOP 1 Total
        FROM   (
                SELECT  TOP 50 PERCENT Total
                FROM    [TABLE] A
                WHERE   Total IS NOT NULL
                ORDER BY Total DESC
                ) AS A
        ORDER BY Total ASC)) / 2