Как найти границы групп смежных последовательных чисел?

У меня есть таблица со следующим определением

CREATE TABLE mytable
  (
     id     INT IDENTITY(1, 1) PRIMARY KEY,
     number BIGINT,
     status INT
  )

и примеры данных

INSERT INTO mytable
VALUES (100,0),
       (101,0),
       (102,0),
       (103,0),
       (104,1),
       (105,1),
       (106,0),
       (107,0),
       (1014,0),
       (1015,0),
       (1016,1),
       (1017,0)

Глядя только на строки, где status = 0, как я могу свернуть значения Number в диапазоны смежных последовательных номеров и найти начало и конец каждого диапазона?

т. е. для примера данных результаты будут

         FROM      to 
Number    100      103
Number    106      107
Number    1014     1015
Number    1017     1017

sql-server sql-server-2008 gaps-and-islands
person Mike Station    schedule 11.06.2013    source источник
comment
Я делал это раньше, несколько месяцев назад. Я не думаю, что сохранил запрос, но он все еще может быть в моих локальных журналах. Держись крепче.   -  person Joe Frambach    schedule 11.06.2013
comment
google для пробелов и островов сервера sql   -  person OzrenTkalcecKrznaric    schedule 11.06.2013
comment
жаль не могу найти.   -  person Joe Frambach    schedule 11.06.2013
comment
Ух ты, для этого вопроса на SO есть целый тег: stackoverflow.com/questions/tagged/gaps-and-islands< /а>   -  person Joe Frambach    schedule 11.06.2013
comment
SQL очень неэффективен при просмотре данных по строкам без курсора (а курсоры — это плохо!). Я настоятельно рекомендую вам придумать способ либо хранить диапазоны в другой коллекции, либо вытащить все данные и обработать числа (т.е. вычислить диапазоны) самостоятельно. С учетом сказанного, вы, вероятно, можете обойтись без использования рекурсивного CTE для выполнения работы. Как именно, я не уверен. Как было предложено, проверьте пробелы и острова.   -  person Eli Gassert    schedule 11.06.2013
comment
давай гений, я знаю, ты можешь найти решение ;)   -  person Mike Station    schedule 11.06.2013
comment
@EliGassert Это совершенно неправда. Проблемы пробелов и островов могут быть очень эффективно решены в SQL, прибегая либо к курсорам, либо к еще более неэффективным рекурсивным CTE. Ицак Бен-Ган много писал о том, как это можно сделать. Здесь: sqlmag.com/article/tsql3/calculating-concurrent- сессии-часть-3   -  person RBarryYoung    schedule 11.06.2013

Ответы (1)


arrow_upward
33
arrow_downward

Как упоминалось в комментариях, это классическая проблема пробелов и островов.

Решение, популяризированное Ициком Бен Ганом, состоит в том, чтобы использовать тот факт, что ROW_NUMBER() OVER (ORDER BY number) - number остается постоянным в пределах «острова» и не может появляться на нескольких островах.

WITH T
     AS (SELECT ROW_NUMBER() OVER (ORDER BY number) - number AS Grp,
                number
         FROM   mytable
         WHERE  status = 0)
SELECT MIN(number) AS [From],
       MAX(number) AS [To]
FROM   T
GROUP  BY Grp
ORDER  BY MIN(number)

NB: Если number не гарантируется уникальность, замените ROW_NUMBER на DENSE_RANK в приведенном выше коде.

person Martin Smith    schedule 11.06.2013
comment
+1: Да, это лучший подход. Никаких проклятых курсоров или неэффективной рекурсии. - person RBarryYoung; 11.06.2013
comment
Вот одно из мест, где об этом говорит Ицак: sqlmag.com /статья/tsql3/вычисление-параллельных-сессий-часть-3. Я не буду упоминать, кому он приписывает ее решение (кроме Бена Фланагана и Арнольда Фриббла). ;-) - person RBarryYoung; 11.06.2013
comment
@RBarryYoung - Очень скромно! Извиняюсь за указание не того человека в моем ответе! - person Martin Smith; 11.06.2013
comment
@MartinSmith Да, моя единственная претензия на славу. :-) Кроме того, Ицак Бен-Ган действительно гений ИМХО. - person RBarryYoung; 11.06.2013
comment
почему вы использовали - в своем запросе? - person Mike Station; 12.06.2013
comment
@MikeStation - В отличие от чего? Значение GRP должно быть чем-то постоянным в пределах острова и уникальным для острова, который его достигает. например Посмотрите результаты здесь и поэкспериментируйте с изменением данных примера, и вы увидите, как это работает. sqlfiddle.com/#!6/f1040/2 - person Martin Smith; 12.06.2013