Finding contiguous ranges in grouped data

Question

I have a table with the following structure:

CREATE TABLE `Rings` (
    ID_RingType CHAR(2),
    Number MEDIUMINT UNSIGNED,
    ID_User INT(11)
);

and with data:

INSERT INTO `Rings` VALUES
  ('AA',1,1),
  ('AA',2,1),
  ('AA',3,1),
  ('AA',11,1),
  ('AA',12,1),
  ('AA',13,1),
  ('AA',14,1),
  ('AA',15,1),
  ('AB',16,1),
  ('AB',17,1),
  ('AB',18,1),
  ('AB',19,1),
  ('AB',20,2),
  ('AB',21,2),
  ('AB',22,2);

I wish to group the data based on ID_User and ID_RingType and for each contiguous range of numbers list the MIN and MAX.

The results should look like:

ID_User | ID_RingType | MIN  | MAX
1       | 'AA'        | 1    | 3
1       | 'AA'        | 11   | 15
1       | 'AB'        | 16   | 19
2       | 'AB'        | 20   | 22

I went through several posts on this topic but was not able to tweak them to fit my data.

Any help would be appreciated.

Answer 1

The answer with the variables is going to be more efficient but here is an answer with pure SQL:

select 
    a.id_user, 
    a.id_ringtype, 
    a.number      as min,
    min(b.number) as max
from 
    rings as a 
  join rings as b 
    on  a.id_user = b.id_user 
    and a.id_ringtype = b.id_ringtype 
    and a.number <= b.number 
where not exists 
      ( select 1 
        from rings as c 
        where c.id_user = a.id_user 
          and c.id_ringtype = a.id_ringtype 
          and c.number = a.number - 1
      )
  and not exists 
      ( select 1 
        from rings as d 
        where d.id_user = b.id_user 
          and d.id_ringtype = b.id_ringtype 
          and d.number = b.number + 1
      ) 
group by 
    a.id_user, 
    a.id_ringtype, 
    a.number ;

Efficiency will depend on many factors (mainly distibution of data) but an index on (id_user, id_ringtype, number) is essential for this query.

Answer 2

Since MySQL doesn't support ROW_NUMBER(), You can use a variable to create a group like this.

Sample Data

CREATE TABLE `Rings` (
    ID_RingType CHAR(2),
    Number MEDIUMINT UNSIGNED,
    ID_User INT(11)
);

INSERT INTO `Rings` VALUES
  ('AA',1,1),
  ('AA',2,1),
  ('AA',3,1),
  ('AA',11,1),
  ('AA',12,1),
  ('AA',13,1),
  ('AA',14,1),
  ('AA',15,1),
  ('AB',16,1),
  ('AB',17,1),
  ('AB',18,1),
  ('AB',19,1),
  ('AB',20,2),
  ('AB',21,2),
  ('AB',22,2);

Query

SET @grp = 0;
SET @preNum = 0;

SELECT ID_User,ID_RingType,MIN(Number),MAX(Number) FROM
(
SELECT 
ID_RingType,
ID_User,
Number,
@grp := CASE WHEN Number = @preNum + 1   THEN @grp ELSE @grp + 1 END grp,
@preNum := Number 
FROM `Rings`
ORDER BY ID_RingType,ID_User,Number
)T
GROUP BY ID_RingType,ID_User,grp

Output

ID_User | ID_RingType | MIN  | MAX
1       | 'AA'        | 1    | 3
1       | 'AA'        | 11   | 15
1       | 'AB'        | 16   | 19
2       | 'AB'        | 20   | 22

Answer 3

This gets continuity by keeping track of the last number and creating a new column that is a continuity count - an increasing number which increases only when the continuity breaks. That way we can group by that continuity count also and have the results you want:

set @lastnumber=0;
set @continuous=0;
select 
    id_user, id_ringtype, min(number), max(number)
from 
  ( select 
        id_user, id_ringtype, number, 
        if(number=@lastnumber+1, @continuous, @continuous:=@continuous+1) 
            as continuityCount, 
        @lastnumber:=number 
    from Rings
  ) continuity
group by 
    id_user, id_ringtype, continuityCount ;

Since you want to do a view, I was trying to think of a way without variables, and this is the best i could come up with:

select id_ringtype, id_user, number, minnum, maxnum from
(select t1.id_ringtype, t1.number, t1.id_user, if(t0.number is null, t1.number, 0) as minnum, if(t2.number is null, t1.number, 0) as maxnum
   from Rings as t1
   left join Rings as t0 on t1.number = t0.number + 1 and t1.id_ringtype = t0.id_ringtype and t1.id_user = t0.id_user
   left join Rings as t2 on t2.number = t1.number + 1 and t2.id_ringtype = t1.id_ringtype and t2.id_user = t1.id_user
   order by number) PrevAndNext
where minnum<>0 or maxnum<>0

but the results that gives you look like this

AA  1   1   1   0
AA  1   3   0   3
AA  1   11  11  0
AA  1   15  0   15
AB  1   16  16  0
AB  1   19  0   19
AB  2   20  20  0
AB  2   22  0   22

Perhaps someone could add to this how we could coalesce those results. My brain is little fried right now... and it's only 10am... it's not going to be a good day.