5

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.

4

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.

  • Thanks, works fine, but efficiency is quite important, since the table is expected to contain millions of records. – Zbynek Aug 13 '15 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. – ypercubeᵀᴹ Aug 13 '15 at 14:24
  • When I create index and view for this statement, it is blazingly fast, thanks again! – Zbynek Aug 14 '15 at 6:56
4

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
  • 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 '15 at 14:03
  • You can't have variables in views in mysql – ughai Aug 13 '15 at 14:08
2

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.

  • min and max should be continuous else a new row is generated. check the output – ughai Aug 13 '15 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 – ypercubeᵀᴹ Aug 13 '15 at 13:50
  • ok, that should be fairly simple too, just a sec – user67871 Aug 13 '15 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. – ypercubeᵀᴹ Aug 13 '15 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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