Группировать SQL по непрерывному диапазону целых значений

#sql #sqlite #gaps-and-islands

#sql #sqlite #пробелы и острова

Вопрос:

У меня есть таблица, подобная:

 id  number  owner
47   100      1
48   101      1
49   102      1
50   103      2
51   104      2
52   105      2
53   106      1
54   107      1
55   108      2
56   109      2
57   110      2

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

 from_number  to_number    owner
   100          102         1
   103          105         2
   106          107         1
   108          110         2

Я пробовал: SELECT MIN(number), MAX(number), owner FROM table GROUP BY owner , но получил результат:

 MIN(number)  MAX(number)  owner 
   100          107        1
   103          110        2

Ответ №1:

Это проблема типа «групп и островов». Вы можете сделать это, вычитая последовательность из number . Разница постоянна, когда number s являются последовательными:

 select owner, min(number) as from_number, max(number) as to_number
from (select t.*,
             row_number() over (partition by owner order by number) as seqnum
      from t
     ) t
group by owner, (number - seqnum);

1. Спасибо! Но не могли бы вы объяснить, что (number - seqnum) означает и как это работает, пожалуйста?

2. @Googlom . , , Который вычитает последовательность из чисел. Запустите подзапрос, и вы увидите, как это определяет нужные вам группы.

Ответ №2:

вы можете попробовать сгенерировать номер двух строк и взять их разницу

 select owner, min(number) as min_number, max(number) as max_number
from (select a.*,
             row_number() over (partition by owner order by number) as rn1,
             row_number() over(order by number) rn2
      from table_name a
     ) t
group by owner, (rn2 - rn1);