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

Question

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

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

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

In my digging, I could find nowhere that explains why this was chosen to work the way it does? This is more of a procedural approach in a set based language, so this works against my SQL thought process and is quite confusing in my opinion. Does anybody know and/or can anybody explain why the recursive CTE treats analytic functions at the recursion level in a procedural fashion?

---

Here is the code to help visualize this:

Обратите внимание, что RowNumber column in each one of these code outputs.

Here is the SQLFiddle for the CTE (only showing the 2nd level of the recursion)

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;

Here is the second SQLFiddle for what I would expect the CTE to do (again only need the 2nd level to display the issue)

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;

I always envisioned the SQL recursive CTE to run more like this while loop

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;

Answer 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. Вот почему подзапросы, содержащие аналитические функции, всегда будут полностью разрешены и никогда не будут объединены с остальными.

Обновить

Глядя на 2-й запрос:

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;

Это нужно разделить, чтобы сбросить число.

Рекурсивные запросы не работают в цикле 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;

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

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

Y