SQL GROUP BY: intervals in continuity?

Question

The idea is that say you have the following table.

-------------
| oID | Area|
-------------
| 1 | 5     |
| 2 | 2     |
| 3 | 3     |
| 5 | 3     |
| 6 | 4     |
| 7 | 5     |
-------------

If grouping by continuity is possible this pseudo query

SELECT SUM(Area) FROM sample_table GROUP BY CONTINUITY(oID)

would return

-------------
| SUM(Area) |
-------------
|  10       |
|  12       |
-------------

Where the continuity break arises at oID or rather the lack thereof an entry representing oID 4.

Does such functionality exist within the standard functions of Sql?

Hasdrubal

Answer 1

There is no such functionality in "standard functions of SQL", but it is possible to get the desired result set by using some tricks.

With the subquery illustrated below we create a virtual field which you can use to GROUP BY in the outer query. The value of this virtual field is incremented each time when there is a gap in the sequence of oID. This way we create an identifier for each of those "data islands":

SELECT  SUM(Area), COUNT(*) AS Count_Rows
FROM    (
        /* @group_enumerator is incremented each time there is a gap in oIDs continuity */
        SELECT  @group_enumerator := @group_enumerator + (@prev_oID != oID - 1) AS group_enumerator,
                @prev_oID := oID AS prev_oID,
                sample_table.*
        FROM    (
                SELECT  @group_enumerator := 0,
                        @prev_oID := -1
                ) vars,
                sample_table
        /* correct order is very important */
        ORDER BY
                oID
        ) q
GROUP BY
        group_enumerator

Test table and data generation:

CREATE TABLE sample_table (oID INT auto_increment, Area INT, PRIMARY KEY(oID));
INSERT INTO sample_table (oID, Area) VALUES (1,5), (2,2), (3,3), (5,3), (6,4), (7,5);

I need to thank Quassnoi for pointing out this trick in my related question ;-)

UPDATE: added test table and data and fixed duplicate column name in example query.

Quench

Answer 2

Here's a blog post that provides a very thorough explanation and example related to grouping by contiguous data. If you have any issues comprehending it or implementing it, I can attempt to provide an implementation for your problem.

Stephens

this is also possible without using temporary tables. – Quench