find the beginning and ending values of sequential runs

Question

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set?

Given the sample data they used:

mysql> CREATE TABLE wp_blogs
    -> (
    ->   blog_id INTEGER
    -> );

mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
mysql> insert into wp_blogs values(8);
mysql> insert into wp_blogs values(10);

this query answers the question, "What are the gaps?":

mysql> SELECT a.blog_id+1 AS start, MIN(b.blog_id) - 1 AS end
    ->     FROM wp_blogs AS a, wp_blogs AS b
    ->     WHERE a.blog_id < b.blog_id
    ->     GROUP BY a.blog_id
    ->     HAVING start < MIN(b.blog_id);

+-------+------+
| start | end  |
+-------+------+
|     3 |    3 |
|     5 |    5 |
|     9 |    9 |
+-------+------+

What I want to know is what are the islands? I would like to see output like this:

+-------+------+  
| start | end  |  
+-------+------+  
|     1 |    2 |  
|     4 |    4 |  
|     6 |    8 |  
|    10 |   10 |  
+-------+------+  

but I can't quite figure out the query to do so. I am using MySQL 5.6.

Anyone have a solution?

Answer 1

To keep all the state information with the current row to avoid having conditions you can so something like:

SELECT start, max(end) FROM (
    SELECT 
        CASE WHEN blog_id = @prev + 1 THEN @start:=@start ELSE @start:=blog_id END AS start, 
        blog_id AS end,  
        @prev:=blog_id 
    FROM (SELECT @prev:=NULL, @start:=NULL) AS vars
    JOIN wp_blogs
) a 
GROUP BY start;

+---------+------------+
|   start |   max(end) |
|---------+------------|
|       1 |          2 |
|       4 |          4 |
|       6 |          8 |
|      10 |         10 |
+---------+------------+
4 rows in set
Time: 0.003s

Answer 2

@variables is the secret sauce. (Or "windowing functions" of MariaDB 10.0.2.)

You know how to get the 'gaps' 3, 5, 9. One less than each is the "end" of an island and one more is the start of the next island.

So, walk through the table of gaps, using an @variable to keep track of the 'previous' end, etc.

SELECT @prev+1 AS 'start',
       `start`-1 AS 'end',
       @prev := `end` AS junk
    FROM ( SELECT @prev := 0 ) AS init
    JOIN gaps

Two flaws in that query (left as an exercise to the reader):

• Fails to generate the last row. (Use UNION and ORDER BY)
• Generates an extra column. (Wrap this query in another.)
• I did not verify your 'gaps' query; if it fails, the roll the two queries together.