Search code examples
mysqlmultiple-columnssequential

MySQL multi-column maxium sequential runs

I've been banging my head against the wall for days now trying to write a query for the following problem (new to MySQL).

I have a table A which has the following data

id, r1, r2, r3, r4, r5, r6, r7, r8, r9, max
 1,  1,  2,  3,  6,  7,  8,  9,  0
 2,  1,  3,  4,  5,  6,  7,  8,  0
 3,  2,  4,  5,  7,  9, 11, 12,  0

I need to set the max field to the maximum sequence in the row. for example, row id 1 would have a max sequence of 4 (6,7,8,9) so the max field will display 4. The numbers are always listed from smallest to largest (left to right) in the rows.

I suspect it would be something along the lines of;

SET @r:=0
SET @s:=0

IF(r1 + 1 = r2, IF(@r > @s, @s:=@r, @r:= @r + 1),
IF(r2 + 1 = r3, IF(@r > @s, @s:=@r, @r:= @r + 1),
IF(r3 + 1 = r4, IF(@r > @s, @s:=@r, @r:= @r + 1),
IF(r4 + 1 = r5, IF(@r > @s, @s:=@r, @r:= @r + 1),`

and so forth... but it doesn't seem to work as the increment only applies per line??

FOR i <= count(column)
IF count > sequence 
THEN sequence = count
ELSE count ++
NEXT i

Any help would be greatly appreciated. Thanks.

Solution

  • You probably can do this in MySQL entirely, but I think this will be rather messy.

    If I were you, I would do this on a client. This should work as Perl implementation:

    use DBI;
my $dbh = DBI->connect("dbi:mysql", "user", "password");
my $sth = $dbh->prepare("SELECT * FROM mytable");
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {
    my $id = $row->{id};
    my @r;
    push @r, $row->{r1}, $row->{r2}, $row->{r3},
             $row->{r4}, $row->{r5}, $row->{r6},
             $row->{r7}, $row->{r8}, $row->{r9};
    my $max = 1; # max run
    my $t = 1;   # local max run
    for (my $i = 1; $i < 9; $i++) {
        if ($r[$i] == $r[$i-1]+1) {
            $t++;
        } else {
            $t = 1;
        }
        if ($t > $max) {
            $max = $t;
        }
    }
    $dbh->do(qq{ UPDATE mytable SET max = ? WHERE id = ?}, undef,
                                   $max,         $id);
}
$sth->finish();
$dbh->disconnect();