Почему рекурсивные 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. Опять же, это не вопрос процедурного или нет, просто вопрос о возможном плане выполнения.
Надеюсь, это ответит на ваш вопрос, позвольте мне, если это не так:)
у
