Geek Answers Handbook

A group sequentially assigns values ​​in MySQL and adds an identifier to such groups

I have a simple table and I need to identify groups of four rows (groups are not goals), but each row of each row has a value of +1. For instance:

  ----------------------
 |  language |  id |
 ----------------------
 |  C |  16 |
 |  C ++ |  17 |
 |  Java |  18 |
 |  Python |  19 |
 |  HTML |  65 |
 |  Javascript |  66 |
 |  Php |  67 |
 |  Perl |  68 |
 ----------------------

I want to add a column that indicates a group or set, how can I get this output using MySQL ?:

  ----------------------------
 |  language |  id |  set |
 ----------------------------
 |  C |  16 |  1 |
 |  C ++ |  17 |  1 |
 |  Java |  18 |  1 |
 |  Python |  19 |  1 |
 |  HTML |  65 |  2 |
 |  Javascript |  66 |  2 |
 |  Php |  67 |  2 |
 |  Perl |  68 |  2 |
 ----------------------------

Please note that in this example there are only 2 sets (this can be 1 or more sets), and they did not start with 16 (such values ​​are not known, but the restriction is that each id value of each row has this form n, n + 1, n + 2 and n + 3).

I studied the Gaps and Islands problem, but did not understand how to solve it using their solutions. I am also looking through stackoverflow, but the closest question I found is How to find spaces in sequential numbering in mysql?

thanks

+4
source share
3 answers
select language, @n:=if(@m+1=id, @n, @n+1) `set`, (@m:=id) id from t1, (select @n:=0) n, (select @m:=0) m

Demo on sqlfiddle

+1
source 
 SELECT language,id,g FROM ( SELECT language,id, CASE WHEN id=@lastid +1 THEN @n ELSE @n: =@n +1 END AS g, @lastid := id As b FROM t, (SELECT @n:=0) r ORDER BY id ) s

EDIT

If you want only 4 per group to add a variable number of rows:

 SELECT language,id,g,rn FROM ( SELECT language,id, CASE WHEN id=@lastid +1 THEN @n ELSE @n: =@n +1 END AS g, @rn := IF(@lastid+1 = id, @rn + 1, 1) AS rn, @lastid := id As dt FROM t, (SELECT @n:=0) r ORDER BY id ) s Where rn <=4

Fiddle

+2
source

You can use the following query:

 SELECT l.*, s.rn FROM languages AS l INNER JOIN ( SELECT minID, @rn2: =@rn2 +1 AS rn FROM ( SELECT MIN(id) AS minID FROM ( SELECT id, id - IF (true, @rn1: =@rn1 +1, 0) AS grp FROM languages CROSS JOIN (SELECT @rn1:=0) AS var1 ORDER BY id) t GROUP BY grp HAVING COUNT(grp) = 4 ) u CROSS JOIN (SELECT @rn2:=0) AS var2 ) s ON l.id BETWEEN minID AND minID + 3

The above query identifies islands of exactly 4 consecutive records and returns only records. It is easy to modify accounting for a different number of consecutive entries.

Also note the use of the IF conditional expression: it ensures that @rn1 initialized first and then used to evaluate the grp field.

Demo here

0
source

More articles:


All Articles