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 combining by continuity is possible, this pseudo query

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

will return

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

If a continuity gap occurs with an OID, or rather, in the absence of its record representing oID 4

Does this functionality exist in standard Sql functions?

Answer 1

There is no such function in the "standard SQL functions", but you can get the desired result using some tricks.

In the subquery shown below, we create a virtual field that you can use for GROUP BY in an external query. The value of this virtual field increases every time there is a space in the oID sequence. Thus, we create an identifier for each of these "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 Kwasnui for pointing out this trick in my related question ; -)

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

Answer 2

Here's a blog post that contains a very detailed explanation and an example related to grouping continuous data . If you have any problems with understanding or implementation, I can try to ensure that your problem is implemented.