Неожиданный вывод аналитической функции в общем табличном выражении

В SQL Server 2019 аналитические функции не возвращают результаты, которых я ожидал бы в контексте рекурсивных общих табличных выражений. Рассмотрим следующий нерекурсивный запрос T-SQL:

WITH SourceData (RowNum, Uniform, RowVal) AS (
  SELECT 1, 'A', 'A' UNION ALL
  SELECT 2, 'A', 'B' UNION ALL
  SELECT 3, 'A', 'C' UNION ALL
  SELECT 4, 'A', 'D'
),
RecursiveCte0 (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
  SELECT RowNum, Uniform, RowVal, RowVal, RowNum, CAST(RowNum AS BIGINT), 0
  FROM SourceData
),
RecursiveCte1 (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
  SELECT * FROM RecursiveCte0
  UNION ALL
  SELECT
    RowNum, Uniform, RowVal,
    MIN(MinVal) OVER (PARTITION BY Uniform),
    SUM(RowNum) OVER (PARTITION BY Uniform),
    ROW_NUMBER() OVER (PARTITION BY Uniform ORDER BY RowNum),
    RecursiveLevel + 1
  FROM RecursiveCte0
)
SELECT *
FROM RecursiveCte1
ORDER BY RecursiveLevel, RowNum;

Полученные результаты:

RowNum Uniform RowVal MinVal SomeSum RowNumCalc RecursiveLevel
1      A       A      A      1       1          0
2      A       B      B      2       2          0
3      A       C      C      3       3          0
4      A       D      D      4       4          0
1      A       A      A      10      1          1
2      A       B      A      10      2          1
3      A       C      A      10      3          1
4      A       D      A      10      4          1

Как и ожидалось, функции MIN, SUM и ROW_NUMBER генерируют соответствующие значения на основе всех строк из RecursiveCte0. Я ожидал бы, что следующий рекурсивный запрос будет логически идентичен нерекурсивной версии выше, но он дает разные результаты:

WITH SourceData (RowNum, Uniform, RowVal) AS (
  SELECT 1, 'A', 'A' UNION ALL
  SELECT 2, 'A', 'B' UNION ALL
  SELECT 3, 'A', 'C' UNION ALL
  SELECT 4, 'A', 'D'
),
RecursiveCte (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
  SELECT RowNum, Uniform, RowVal, RowVal, RowNum, CAST(RowNum AS BIGINT), 0
  FROM SourceData
  UNION ALL
  SELECT
    RowNum, Uniform, RowVal,
    MIN(MinVal) OVER (PARTITION BY Uniform),
    SUM(RowNum) OVER (PARTITION BY Uniform),
    ROW_NUMBER() OVER (PARTITION BY Uniform ORDER BY RowNum),
    RecursiveLevel + 1
  FROM RecursiveCte
  WHERE RecursiveLevel < 1
)
SELECT *
FROM RecursiveCte
ORDER BY RecursiveLevel, RowNum;

Полученные результаты:

RowNum Uniform RowVal MinVal SomeSum RowNumCalc RecursiveLevel
1      A       A      A      1       1          0
2      A       B      B      2       2          0
3      A       C      C      3       3          0
4      A       D      D      4       4          0
1      A       A      A      1       1          1
2      A       B      B      2       1          1
3      A       C      C      3       1          1
4      A       D      D      4       1          1

Кажется, что для каждой из трех аналитических функций группировка применяется только в контексте каждой отдельной строки, а не всех строк на этом уровне. Это неожиданное поведение также происходит, если я разбиваю (SELECT NULL). Я ожидал, что аналитические функции будут применяться ко всему уровню рекурсии согласно MSDN:

Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE.

Почему эти два запроса дают разные результаты? Есть ли способ эффективно использовать аналитические функции с рекурсивными общими табличными выражениями?

Нет, вы не можете сделать это так, как вам бы хотелось. Вы неправильно читаете MSDN, это специально исключает ваш случай. Это связано с тем, что rCTE обрабатывается стеком: каждое подмножество обрабатывается строка за строкой (гораздо больше похоже на Oracle CONNECT BY, чем на написанный SQL), поэтому номер строки применяется только к этой строке с ее непосредственным соединением.

Charlieface 30.03.2021 18:34

@Charlieface Спасибо; существующий вопрос имеет отношение, но, я думаю, не совсем то же самое. Предполагалось, что аналитическая функция должна применяться ко всей рекурсивной части rCTE. MSDN и ответ объясняют, что агрегатные / аналитические функции применяются только к текущему уровню рекурсии, как я ожидал, но не вижу. Ни в одном из них не указано, что область действия функции ограничена отдельными строками.

novog 30.03.2021 19:04

Правда, этот ответ на самом деле вообще не отвечает на вопрос, но вопрос тот же самый, если вы внимательно посмотрите. См. Также объяснятьextended.com/2009/11/18/…, ссылка на который есть там

Charlieface 30.03.2021 19:08

Я только что нашел еще один вопрос, ответ которого согласуется с построчным объяснением, а также указывает на документацию MSDN. Однако я все еще не думаю, что MSDN говорит об этом; он говорит «о подмножестве данных, переданных им на текущем уровне рекурсии», ничего не о построчном.

novog 30.03.2021 19:09

Я думаю, это типичная плохая формулировка в MSDN. В любом случае, вот как это работает, и как это всегда работало. Это не ошибка. Кстати, учитывая низкую производительность rCTE и вашу проблему, вам, вероятно, лучше использовать табличную переменную и цикл while, как бы ужасно это ни выглядело.

Charlieface 30.03.2021 19:11
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
0
6
15
0

Другие вопросы по теме