计算 SQL 公用表表达式中列的中位数

Question

在 MSSQL2008 中，我尝试使用经典中值查询从公共表表达式计算一列数字的中值，如下所示：

WITH cte AS
(
   SELECT number
   FROM table
) 

SELECT cte.*,
(SELECT 
  (SELECT (   
    (SELECT TOP 1 cte.number  
     FROM     
     (SELECT TOP 50 PERCENT cte.number     
      FROM cte
      ORDER BY cte.number) AS medianSubquery1   
    ORDER BY cte.number DESC)  
    +   
  (SELECT TOP 1 cte.number
   FROM     
    (SELECT TOP 50 PERCENT cte.number    
     FROM cte   
     ORDER BY cte.number DESC) AS medianSubquery2   
   ORDER BY cte.number ASC) ) / 2)) AS median

FROM cte
ORDER BY cte.number

我得到的结果集如下：

NUMBER    MEDIAN
x1        x1
x1        x1
x1        x1
x2        x2
x3        x3

换句话说，“中值”列是相同的当我期望中位数列一直向下为“x1”时，作为“数字”列。我使用类似的表达式来计算模式，并且它在相同的公用表表达式上运行良好。

Answer 1

这是一种稍微不同的方法：

WITH cte AS
(
   SELECT number
   FROM table1
)
SELECT T1.number, T3.median
FROM cte T1, 
(
    SELECT AVG(number) AS median
    FROM
    (
        SELECT number, ROW_NUMBER() OVER(ORDER BY number) AS rn
        FROM cte
    ) T2
    WHERE T2.rn = ((SELECT COUNT(*) FROM table1) + 1) / 2
    OR T2.rn = ((SELECT COUNT(*) FROM table1) + 2) / 2
) T3

Answer 2

您的查询的问题是您正在执行

SELECT TOP 1 cte.number FROM... ，

但它与子查询不相关，它与外部查询相关，因此子查询不相关。这就解释了为什么你最终会得到相同的值。删除cte.（如下）得出 CTE 的中值。这是一个恒定值。你想做什么？

WITH cte AS
    ( SELECT NUMBER
    FROM master.dbo.spt_values
    WHERE TYPE='p'
    )

SELECT cte.*,
(SELECT 
  (SELECT (   
    (SELECT TOP 1 number  
     FROM     
     (SELECT TOP 50 PERCENT cte.number     
      FROM cte
      ORDER BY cte.number) AS medianSubquery1   
    ORDER BY number DESC)  
    +   
  (SELECT TOP 1 number
   FROM     
    (SELECT TOP 50 PERCENT cte.number    
     FROM cte   
     ORDER BY cte.number DESC) AS medianSubquery2   
   ORDER BY number ASC) ) / 2)) AS median
FROM cte
ORDER BY cte.number

退货

NUMBER      median
----------- -----------
0           1023
1           1023
2           1023
3           1023
4           1023
5           1023
6           1023
7           1023

Answer 3

这并不是一个全新的答案，因为它主要扩展了 Mark Byer 的答案，但有几个选项可以进一步简化查询。

首先是真正利用 CTE。您不仅可以有多个 CTE，而且它们可以互相引用。考虑到这一点，我们可以创建一个额外的 CTE 来根据第一个结果计算中值。这封装了中值计算，并让实际的 SELECT 只做它需要做的事情。请注意，ROW_NUMBER() 必须移至第一个 CTE。

;WITH cte AS
(
   SELECT number, ROW_NUMBER() OVER(ORDER BY number) AS rn
   FROM table1
),
med AS
(
    SELECT AVG(number) AS median
    FROM cte
    WHERE cte.rn = ((SELECT COUNT(*) FROM cte) + 1) / 2
    OR cte.rn = ((SELECT COUNT(*) FROM cte) + 2) / 2
)
SELECT cte.number, med.median
FROM cte
CROSS JOIN med

为了进一步降低复杂性，您“可以”使用自定义 CLR 聚合来处理中位数（例如 http://www.SQLsharp.com/ [我是其作者]）。

;WITH cte AS
(
   SELECT number
   FROM table1
),
med AS
(
    SELECT  SQL#.Agg_Median(cte.number) AS median
    FROM    cte
)
SELECT cte.number, med.median
FROM cte
CROSS JOIN med