6

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.

3 Answers 3

5

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.

3
  • Thanks, works fine, but efficiency is quite important, since the table is expected to contain millions of records.
    – Zbynek
    Aug 13, 2015 at 14:11
  • You should compare/tests efficiencies. There may be cases when this is faster. It will depend on distribution of data, how many unique ringtype and user IDs, how many rows and contiguous ranges for each ringtype/user combination, etc. Aug 13, 2015 at 14:24
  • When I create index and view for this statement, it is blazingly fast, thanks again!
    – Zbynek
    Aug 14, 2015 at 6:56
6

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
2
  • Great! Would it be possible to create a view from this statement, so I would not need to initialize session variables each time?
    – Zbynek
    Aug 13, 2015 at 14:03
  • You can't have variables in views in mysql
    – ughai
    Aug 13, 2015 at 14:08
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.

4
  • min and max should be continuous else a new row is generated. check the output
    – ughai
    Aug 13, 2015 at 13:50
  • Yes, you are missing the "contiguous" part. There should be 2 rows in the result with ID_USER=1, ID_RingTYpe='AA Aug 13, 2015 at 13:50
  • ok, that should be fairly simple too, just a sec
    – user67871
    Aug 13, 2015 at 13:55
  • 1
    It would be best to add an ORDER BY id_user, id_ringtype, number in the internal select, to make sure that you get correct result. Aug 13, 2015 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.