find contiguous ranges with MySQL

Question

I have a table of values (numeric) of different customers (customer_id):

CREATE TABLE dev.customer_resources (
  id int(11) NOT NULL AUTO_INCREMENT,
  customer_id int(11) DEFAULT NULL,
  value int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)

I want to get the ranges of numbers for a specific customer. so, if the table data is:

======================
|id|customer_id|value|
======================
| 1|         21|    1|
----------------------
| 2|         21|    2|
----------------------
| 3|         21|    3|
----------------------
| 4|         21|   20|
----------------------
| 5|         21|   21|
----------------------
| 6|         21|   22|
----------------------
| 7|         22|    5|
----------------------

i'll get a result that is something like this, for customer_id=21:

==========
start |end
==========
|    1| 3|
----------
|   20|22|
----------

and something like this for customer_id=22:

==========
start |end
==========
|    5| 5|
----------

After searching for a solution, i found the following code:

select l.value as start,
    (
        select min(a.value) as value
        from customer_resources as a
            left outer join customer_resources as b on a.value = b.value - 1
        where b.value is null
            and a.value >= l.value
    ) as end
from customer_resources as l
    left outer join customer_resources as r on r.value = l.value - 1
where r.value is NULL;

that does pretty much what i need, but does not include limiting the results to a specific customer_id. I tried adding the customer_id to the query, but i'm not quite sure where to add it, as all it did was break functionality.

Answer 1

LukStorms solution seems like the right kind of thing - it just seems a little more convoluted than is strictly necessary...

Here's a variation on the theme...

    DROP TABLE IF EXISTS my_table;

    CREATE TABLE my_table
    (id SERIAL PRIMARY KEY
    ,customer_id int NOT NULL
    ,value int NOT NULL
    );

    INSERT INTO my_table VALUES
    (1,         21,    1),
    (2,         21,    2),
    (3,         21,    3),
    (4,         21,   20),
    (5,         21,   21),
    (6,         21,   22),
    (7,         22,    5);

    SELECT customer_id
         , MIN(value) start
         , MAX(value) end 
      FROM 
         ( SELECT *
                , CASE WHEN value = @prev+1 THEN @i:=@i ELSE @i:=@i+1 END grp
                , @prev:=value prev
             FROM my_table
                , (SELECT @prev:= null,@i:=0) vars 
            ORDER 
               BY customer_id
                , id
         ) x 
     GROUP 
        BY customer_id,grp;
    +-------------+-------+------+
    | customer_id | start | end  |
    +-------------+-------+------+
    |          21 |     1 |    3 |
    |          21 |    20 |   22 |
    |          22 |     5 |    5 |
    +-------------+-------+------+

Answer 2

This appears to be a Gaps And Islands problem.

Here's a bit of old-school MySql specific solution that makes use of variables.

The start of an island is found by comparing the previous value and customer_id.

SELECT 
 customer_id, 
 value_start as `start`, 
 max(val) as `end`
FROM 
(
    SELECT 
      CASE 
      WHEN `value` = @prev_val + 1 AND customer_id = @prev_grp
      THEN @start_val:= @start_val 
      ELSE @start_val:=`value`
      END AS value_start, 
      @prev_grp:=customer_id as customer_id,
      @prev_val:=`value` as val
    FROM customer_resources
    CROSS JOIN (SELECT @prev_val:=NULL, @start_val:=NULL, @prev_grp:=NULL) AS v
    ORDER BY customer_id, `value`
) AS q 
GROUP BY customer_id, value_start;