SQL-запрос для группировки непрерывного диапазона чисел в разные наборы группировок

У меня есть таблица примерно с более чем миллионом записей. Ниже приведены некоторые из примеров значений:

Group    MemberNo
ABC           100
ABC           101
ABC           200
ABC           201
ABC           202
ABC           203
XYZ           100
XYZ           101
ABC           204
XYZ           301
XYZ           302
ABC           500
ABC           600

Я хочу сгруппировать непрерывный диапазон значений с одной и той же группой в такие наборы:

Group  FromMemberNo      ToMemberNo
ABC             100             101
ABC             200             204
XYZ             100             101
XYZ             301             302
ABC             500             500
ABC             600             600

Из приведенной выше таблицы видно, что, поскольку 100 и 101 непрерывны, она была сгруппирована в одну запись ABC от 100 до 101. Я пробовал этот поток и у меня работает нормально. Но это занимает гораздо больше времени, чем хотелось бы.
Пожалуйста, помогите мне добиться этого.

Заранее спасибо.


tsql gaps-and-islands sql-server-2005
person Nagesh    schedule 22.01.2013    source источник
comment
Принятый ответ в вопросе, на который вы ссылаетесь, вероятно, является наиболее эффективным способом сделать это. Сколько времени это займет и как долго вы ожидаете, что это займет? Как выглядит план выполнения? У вас есть индекс Group,MemberNo?   -  person Martin Smith    schedule 22.01.2013
comment
Привет @MartinSmith, есть еще 4 столбца, и для простоты я добавил только один столбец. У меня есть индексы по всем этим столбцам, но запрос все равно занимает от 4 до 5 секунд.   -  person Nagesh    schedule 22.01.2013
comment
Вам нужно каждый раз обрабатывать миллион строк?   -  person Hamlet Hakobyan    schedule 22.01.2013
comment
Привет @HamletHakobyan, я планирую реализовать это как представление на уровне данных, не затрагивая логику моего приложения. Вы правы, что не требуется каждый раз обрабатывать все строки.   -  person Nagesh    schedule 22.01.2013
comment
В вашем приложении, я думаю, вам нужно отфильтровать его по группе. У вас есть индекс на группу? Попробуйте протестировать с разницей условий фильтрации по группам.   -  person Hamlet Hakobyan    schedule 22.01.2013
comment
Пожалуйста, опубликуйте свою фактическую структуру таблицы вместе с индексами, запросом, который вы выполняете, и планом выполнения, и мы можем посмотреть и посмотреть, отсутствуют ли у вас какие-либо индексы, которые позволили бы избежать сортировки.   -  person Martin Smith    schedule 22.01.2013
comment
возможный дубликат запроса SQL для группировки результатов на основе последовательности   -  person Andriy M    schedule 22.01.2013

Ответы (1)


arrow_upward
2
arrow_downward

другое решение. Я могу понять о производительности, но, похоже, выполняет свою работу (только sql 2012)

declare @t table (g varchar(3), mn int)

insert into @t values 
('ABC',           100),
('ABC',           101),
('ABC',           200),
('ABC',           201),
('ABC',           202),
('ABC',           203),
('XYZ',           100),
('XYZ',           101),
('ABC',           204),
('XYZ',           301),
('XYZ',           302),
('ABC',           500),
('ABC',           600),
('XYZ',           400);


with ctet as (
    select 
        row_number() over (order by g, mn) rn,
        *, 
        case when lag(mn, 1) over (order by g, mn) <> mn - 1 then 1 else 0 end as d 
    from 
        @t
)

select g, min(mn), max(mn)
from
    (
    select 
        *,
        (select sum(d) from ctet vv where vv.rn <= ctet.rn) s
    from 
        ctet
    ) v
group by g, s

Я совершенно уверен, что есть более разумное решение с отставанием или опережением, но я не могу его найти.

===== РЕДАКТИРОВАТЬ =====

ваще работает и на 2005

 with ctet as (
    select 
        row_number() over (order by t.g, t.mn) rn,
        t.*, 
        case when tt.g is null then 1 else 0 end as d
    from 
        @t t
        left join @t tt on t.g = tt.g and t.mn = tt.mn + 1
)
person tschmit007    schedule 22.01.2013
comment
Спасибо, но решение @Andriy M сработало с небольшими изменениями! - person Nagesh; 01.02.2013