Вычислить медиану столбца В общем табличном выражении SQL

В 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 " полностью вниз. Я использую аналогичное выражение для вычисления режима, и оно отлично работает над тем же самым общим табличным выражением.
3 2
sql sql-server sql-server-2008 common-table-expression median

3 ответа:

Проблема с вашим запросом заключается в том, что вы делаете

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
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
3

Это не совсем новый ответ, поскольку он в основном расширяет ответ Марка Байера, но есть несколько вариантов для дальнейшего упрощения запроса.

Первое-это действительно использовать CTE. не только у вас может быть несколько CTE, но они могут ссылаться друг на друга. Имея это в виду, мы можем создать дополнительный CTE для вычисления медианы на основе результатов первого. Это инкапсулирует медианное вычисление и оставляет фактический выбор делать только то, что ему нужно делать. Обратите внимание, что 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 для обработки медианы (например, тот, который предоставляется в бесплатной библиотеке SQL# в 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
0