ROW_NUMBER не работает в CTE

Я играл с отчетами, и оказалось, что row_number не работает в рекурсии.

!! Я упростил пример!!

Из таблицы с 3 записями:

declare @sometable table (id int, id2 int)
insert into @sometable
select 1 as id,   11 as id2   
union all
select 2,         22   
union all
select 3,        33

В CTE выберите All и отметьте первую запись, которая будет исключена на следующей итерации:

;with cte(iteration, ord, id, id2, deal) as 
(
select ordered.*
    , deal = (case when ord = 1 then 1 else 0 end)
from 
    (select 1 iteration,
        ord = ROW_NUMBER() OVER (ORDER BY id),
        st.*
    FROM @sometable st) ordered

)
select * from CTE
union all
    select 
    ordersinverted.nextIteration,
    ordersinverted.ord,
    ordersinverted.id,              
    ordersinverted.id2,
    deal = (case when ord = 1 then 1 else 0 end)
from (
    select 
        ROW_NUMBER() OVER (PARTITION BY ord ORDER BY iteration desc) as reversedIteration,
        ROW_NUMBER() OVER (ORDER BY cte.id) as ord,
        iteration + 1 as nextIteration,                 
        cte.id, 
        cte.id2                 
    from cte 
    where cte.deal = 0
) ordersinverted

Это дает мне ожидаемый результат для 3 итераций: Использовать row_number из результата CTE

Я бы очень хотел получить аналогичный результат и рекурсивно вызвать select. К сожалению, здесь есть подозрение на ошибку:

;with cte(iteration, ord, id, id2, deal) as 
(
    select ordered.*
        , deal = (case when ord = 1 then 1 else 0 end)
    from 
        (select 1 iteration,
            ord = ROW_NUMBER() OVER (ORDER BY id),
            st.*
        FROM @sometable st) ordered
union all
    select 
        ordersinverted.nextIteration,
        ordersinverted.ord,
        ordersinverted.id,              
        ordersinverted.id2,
        deal = (case when ord = 1 then 1 else 0 end)
    from (
        select 
            ROW_NUMBER() OVER (PARTITION BY ord ORDER BY iteration desc) as     reversedIteration,
            ROW_NUMBER() OVER (ORDER BY cte.id) as ord,
            iteration + 1 as nextIteration,                 
            cte.id, 
            cte.id2                 
        from cte 
        where cte.deal = 0
    ) ordersinverted
)
select * from CTE

Использовать row_number в результате CTE

Ой, извини. Это должно иметь формат вопроса: Итак, мой вопрос: это функция или ошибка?

Обратите внимание, что аналогичный запрос для Oracle будет работать так, как ожидалось:

with T (id,grp_id) as (
select 1 as id,1 as grp_id from dual union all
select 2 as id,1 as grp_id from dual union all
select 3 as id,1 as grp_id from dual union all
select 1 as id,2 as grp_id from dual union all
select 2 as id,2 as grp_id from dual union all
select 3 as id,2 as grp_id from dual )
,
rec (id,grp_id,rn) as (
 select id, grp_id, row_number()over(partition by grp_id order by id) rn from T where grp_id=1 
 union all
 select t.id, t.grp_id, row_number() over(partition by t.grp_id  order by t.id) rn from T inner join rec on t.id=rec.id and t.grp_id=rec.grp_id+1

)

PS. Это работает аналогично, если использовать функции max() или min()...


sql-server common-table-expression row-number with-statement
person user6821153    schedule 12.09.2016    source источник
comment
Я действительно не имею представления о том, чего вы пытаетесь достичь/выбрать, но я подозреваю, что ваша проблема заключается в смешивании подзапросов, row_number и рекурсивного cte, а сервер SQL порядка выполняет запрос. Я думаю вы излишне усложняете запрос, делая это. Но поскольку я действительно не знаю, что вы на самом деле пытаетесь сделать, основываясь на входных данных, я не могу указать лучше. Вместо того, чтобы пытаться сделать все в одном запросе, разбейте его на более мелкие шаги.   -  person Allan S. Hansen    schedule 12.09.2016
comment
я бы попробовал 1) создать CTE без row_number в нем 2) создать CTE2, который является выбором CTE с row_number в нем. Я не уверен, что вы могли бы попробовать это   -  person Cato    schedule 12.09.2016
comment
Спасибо за рекомендации по обходному пути. Однако это больше подходит для описания проблемы, существующей в MS SQL. Этот скрипт работает в Oracle. Добавлю сюда образец   -  person user6821153    schedule 13.09.2016

Ответы (1)


arrow_upward
3
arrow_downward

Это задокументированное поведение. Следовательно, это следует рассматривать как «особенность». Вот документация для этого случая: https://msdn.microsoft.com/en-us/library/ms175972.aspx

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

В параграфе K этой статьи есть хорошая демонстрация поведения функции ROW_NUMBER в рекурсивном CTE. Поскольку рекурсивный CTE обрабатывает данные построчно, ROW_NUMBER в рекурсивной части CTE всегда будет возвращать 1. Вы можете изменить ROW_NUMBER() OVER (ORDER BY id) на COUNT(*) OVER (), чтобы проверить, сколько строк SQL Server обрабатывает одновременно для привязки и для рекурсивной части CTE. КТР. Их будет соответственно 3 и 1.

person Denis Reznik    schedule 13.09.2016