Неожиданные данные при типичной рекурсии

Мне трудно использовать слова, чтобы описать это, поэтому вот образец:

select *
into t
from (values (10, 'A'),
             (25, 'B'),
             (30, 'C'),
             (45, 'D'),
             (52, 'E'),
             (61, 'F'),
             (61, 'G'),
             (61, 'H'),
             (79, 'I'),
             (82, 'J')
) v(userid, name)

Обратите внимание, что F, G и H имеют один и тот же идентификатор пользователя.

Теперь рассмотрим следующий рекурсивный запрос:

with tn as 
(
    select t.userId,t.name, row_number() over (order by userid,newid()) as seqnum
    from t
),
cte as 
(
        select userId, name,  seqnum as seqnum
        from tn 
        where seqnum = 1
    union all
        select tn.userId, tn.name,tn.seqnum
        from 
            cte 
            inner join tn on tn.seqnum = cte.seqnum + 1
)
select *
from cte

Первый cte, tn, создает row_number, который включает в себя компонент рандомизации, который заставит F/G/H появляться в случайном порядке. Однако все они по-прежнему будут появляться по одному разу (это легко проверить, изменив последний и самый внешний from на from tn)

Второй cte, cte, рекурсивно сканирует tn. В этом нет ничего слишком сложного, потому что он исходит из свернутого примера. Однако очевидно, что элемент привязки вручную устанавливается как первая строка tn, а затем рекурсия сканирует все остальные строки.

Однако в окончательном наборе результатов F/G/H не появляются по одному разу! Все они появляются в 3 строках, но в любой комбинации. Можно FGH, но можно и FFH, и даже FFF! Вот пример FHH:

+--------+------+--------+
| userId | name | seqnum |
+--------+------+--------+
|     10 | A    |      1 |
|     25 | B    |      2 |
|     30 | C    |      3 |
|     45 | D    |      4 |
|     52 | E    |      5 |
|     61 | F    |      6 |
|     61 | H    |      7 |
|     61 | H    |      8 |
|     79 | I    |      9 |
|     82 | J    |     10 |
+--------+------+--------+

Почему?

Я не думаю, что поведение аналитической функции в ctes имеет какое-либо отношение с ним, потому что tn, который включает row_number, не является рекурсивным.

Для протокола, я получил этот вопрос из-за следующей последовательности событий: кто-то спросил вопрос, на который отлично ответил участник. Тот же ОП задал дополнительный вопрос, на что я мог бы ответить, немного подделав оригинал. Однако, немного покопавшись, я обнаружил поведение, которое не могу понять. Я сделал пример максимально минимальным.


sql-server tsql window-functions common-table-expression recursive-query
person George Menoutis    schedule 10.06.2020    source источник
comment
Пожалуйста, покажите нам свои текущие результаты и результаты, которые вы хотели бы получить.   -  person GMB    schedule 10.06.2020
comment
Существует рандомизация, поэтому результаты не всегда одинаковы. И это не то, что я хочу, а то, что я ожидал. В любом случае, я думаю, я поставлю один набор результатов.   -  person George Menoutis    schedule 10.06.2020
comment
Да, я понимаю вашу точку зрения. Повторно выполняя ваш запрос в этой скрипте базы данных, я воспроизвел проблему. Я думаю, что у Дэвида Брауна есть правильное решение здесь.   -  person GMB    schedule 10.06.2020

Ответы (1)


arrow_upward
3
arrow_downward

CTE не спулированы. Каждая ссылка на tn может привести к повторному запуску запроса и повторной рандомизации результатов.

Чтобы избежать этого, запустите рандомизирующий запрос один раз и загрузите временную таблицу. например

select t.userId,t.name, row_number() over (order by userid,newid()) as seqnum
into #tn
from t

и укажите это в своем последующем запросе

with tn as 
(
    select * from #tn
),
cte as 
(
        select userId, name,  seqnum as seqnum
        from tn
        where seqnum = 1
    union all
        select tn.userId, tn.name,tn.seqnum
        from 
            cte 
            inner join tn on tn.seqnum = cte.seqnum + 1
)
select *
from cte
person David Browne - Microsoft    schedule 10.06.2020