Почему рекурсивные CTE управляют аналитическими функциями (ROW_NUMBER) процедурно?

Вчера я ответил рекурсивному CTE, который выявил проблему с тем, как они реализованы в SQL Server (возможно, в других СУБД тоже?). В основном, когда я пытаюсь использовать ROW_NUMBER для текущего рекурсивного уровня, он выполняется против каждой строки подмножества текущего рекурсивного уровня. Я ожидал бы, что это будет работать в истинной логике SET и будет работать против всего текущего рекурсивного уровня.

Похоже, что из этой статьи MSDN, проблема, которую я нашел, - это назначенная функциональность:

Аналитические и агрегатные функции в рекурсивной части CTE применяется к набору для текущего уровня рекурсии, а не к набору для CTE. Функции, такие как ROW_NUMBER, работают только с подмножеством данные, переданные им по текущему уровню рекурсии, а не по всему набор данных, положенный на рекурсивную часть CTE. Для большего информация, см. J. Использование аналитических функций в рекурсивном CTE.

В моем копании я не мог найти нигде, что объясняет, почему это было выбрано для работы так, как это делается? Это скорее процедурный подход на языке, основанный на наборе, поэтому это работает против моего мыслительного процесса SQL и, по моему мнению, довольно запутанно. Кто-нибудь знает и/или может кто-нибудь объяснить, почему рекурсивный CTE обрабатывает аналитические функции на уровне рекурсии процедурно?

Вот код, который поможет визуализировать это:

Обратите внимание, столбец RowNumber в каждом из этих выходов кода.

Вот SQLFiddle для CTE (только показывающий 2-й уровень рекурсии)

WITH myCTE
AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
  FROM tblGroups
  WHERE ParentId IS NULL

  UNION ALL

  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID
 )
SELECT *
FROM myCTE
WHERE RecurseLevel = 2;

Вот второй SQLFiddle для того, что я ожидал бы от CTE (для отображения проблемы требуется только второй уровень)

WITH myCTE
AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
  FROM tblGroups
  WHERE ParentId IS NULL
 )
  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID;

Я всегда предполагал, что рекурсивный CTE SQL будет больше похож на на этот цикл while

DECLARE @RecursionLevel INT
SET @RecursionLevel = 0
SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, @RecursionLevel AS recurseLevel
INTO #RecursiveTable
FROM tblGroups
WHERE ParentId IS NULL

WHILE EXISTS( SELECT tblGroups.* FROM tblGroups JOIN #RecursiveTable ON #RecursiveTable.GroupID = tblGroups.ParentID WHERE recurseLevel = @RecursionLevel)
BEGIN

    INSERT INTO #RecursiveTable
    SELECT tblGroups.*, 
        ROW_NUMBER() OVER (ORDER BY #RecursiveTable.RowNumber , tblGroups.Score desc) AS RowNumber, 
        recurseLevel + 1 AS recurseLevel
    FROM tblGroups
        JOIN #RecursiveTable
            ON #RecursiveTable.GroupID = tblGroups.ParentID
    WHERE recurseLevel = @RecursionLevel
    SET @RecursionLevel = @RecursionLevel + 1
END

SELECT * FROM #RecursiveTable ORDER BY RecurseLevel;

Ответ 1

Аналитические функции являются особыми в том смысле, что они нуждаются в известном наборе результатов для решения. Они зависят от следующего, предшествующего или полного набора результатов, для измерения текущей величины. Тем не менее, слияние представления никогда не допускается в представлении, которое содержит аналитическую функцию. Зачем? Это изменит результат.

Пример:

    Select * from (
      select row_number() over (partition by c1 order by c2) rw, c3 from t) z
    where c3=123

не совпадает с

    select row_number() over (partition by c1 order by c2) rw, c3 from t 
    where c3=123

Эти 2 возвращают разные значения для rw. Поэтому подзапросы, содержащие аналитические функции, всегда будут полностью разрешены до и никогда не будут слиты с остальными.

Обновление

Глядя на второй запрос:

WITH myCTE
AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
  FROM tblGroups
  WHERE ParentId IS NULL
 )
  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID;

Он работает точно так, как если бы он был написан как (тот же план выполнения и результат):

SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber , tblGroups.Score desc) AS RowNumber, 
      RecurseLevel + 1 AS RecurseLevel
FROM tblGroups
JOIN (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel
    FROM tblGroups
    WHERE ParentId IS NULL
    )myCTE ON myCTE.GroupID = tblGroups.ParentID;

Этот раздел нужно разделить на reset номер журнала.

Рекурсивные запросы не работают в цикле while, они не процедурные. В базе они работают как рекурсивная функция, но в зависимости от таблиц, запросов, индексов они могут быть оптимизированы для работы так или иначе.

Если мы придерживаемся концепции, что представление не может быть объединено при использовании аналитических функций и рассматривается запрос 1. Он может запускаться только один раз, а во вложенном цикле.

WITH myCTE
AS
( /*Cannot be merged*/
  SELECT *, ROW_NUMBER() OVER (ORDER BY Score desc) AS RowNumber, 1 AS RecurseLevel,
  cast(0 as bigint) n
  FROM tblGroups
  WHERE ParentId IS NULL

  UNION ALL

/*Cannot be merged*/
  SELECT tblGroups.*, 
      ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber, tblGroups.Score desc) AS RowNumber,       RecurseLevel + 1 AS RecurseLevel,
  myCTE.RowNumber
  FROM tblGroups
      JOIN myCTE
          ON myCTE.GroupID = tblGroups.ParentID
 )
SELECT *
FROM myCTE;

Итак, 1-й выбор, нельзя слить 2-й, ни один. Единственный способ запустить этот запрос - в вложенном цикле для каждого элемента, возвращаемого на каждом уровне, следовательно reset. Опять же, это не вопрос процедурного или нет, просто вопрос о возможном плане выполнения.

Надеюсь, это ответит на ваш вопрос, позвольте мне, если это не так:)