CODEWITHSUNDEEP
mysqlsqlgroup-by
ahajib
ahajib

Reputation: 13500

SQL Group By on consecutive rows only

So I have a table which looks like:

SITE      LOWER   UPPER   SIZE
a.com     0.1     0.2     10
a.com     0.2     0.3     10
a.com     0.3     0.4     10
a.com     0.7     0.8     10
a.com     0.9     1.0     10
b.com     0.1     0.2     20
a.com     0.6     0.7     30

My desired output would be:

SITE      LOWER   UPPER   SIZE
a.com     0.1     0.4     10
a.com     0.7     1.0     10
b.com     0.1     0.2     20
a.com     0.6     0.7     30

So for a SITE and SIZE pair, I need to compress LOWER and UPPER values where LOWER of the grouped by row is the minimum of the first row and UPPER is the maximum of the last row.LOWER and UPPER are like lower and upper bound values of some buckets (e.g. [0.1, 0.2]) and I only need consecutive buckets to be compressed together. Any help would be much appreciated in this regard.

Upvotes: 0

Views: 189

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can assign a group id to the adjacent rows using variables:

select t.*,
        (@grp := IF(@sru = CONCAT_WS(':', site, rev, lower), 
                    IF(@sru := CONCAT_WS(':', site, rev, upper), @grp, @grp),
                    IF(@sru := CONCAT_WS(':', site, rev, upper) , @grp + 1, @grp + 1)
                   )
            ) as grp
from (select t.*
      from t
      order by site, size, lower
     ) cross join
     (select @grp := 0, @sru := '') params;

You can then aggregate to get what you want:

select site, size, min(lower) as lower, max(upper) as upper
from (select t.*,
             (@grp := IF(@sru = CONCAT_WS(':', site, rev, lower), 
                         IF(@sru := CONCAT_WS(':', site, rev, upper), @grp, @grp),
                         IF(@sru := CONCAT_WS(':', site, rev, upper) , @grp + 1, @grp + 1)
                        )
             ) as grp
      from (select t.*
            from t
            order by site, size, lower
           ) cross join
           (select @grp := 0, @sru := '') params
     ) t
group by size, size, grp;

Here is a SQL Fiddle.

Upvotes: 2

Related Questions