mysql query count consecutive positive numbers in a column -

i have table called "results" , :

  id   -a- -b- -c- -d- -e- -f-   1   100 -76  34 -45 54  65  2    34  -43  57 -12 13 -21  3    104 -76  34 -45 -3  43  4    100 -76  -4 -45 54  65  5    34  -43  57 -12 13 -21  6    104 -76  34 -45 -3  43 

there more data, think enough idea.

what trying get, max count of consecutive positive numbers in each column.

what mysql query that?

thank reading this.

here idea. each number in column, add flag determine if beginning of sequence (which following negative number). cumulative sum of value "sequence count". then, maximum of value. going show code 1 column:

the problem implementation, requires multiple levels of correlated subqueries. first assign sequencestart:

select r.*, rprev.a preva, rprev.b prevb, rprev.c prevc, rprev.d prevd,        (case when (rprev.a < 0 or rprev.a null) 1 else 0 end) aseqstart,        (case when (rprev.b < 0 or rprev.b null) 1 else 0 end) bseqstart,        (case when (rprev.c < 0 or rprev.c null) 1 else 0 end) cseqstart,        (case when (rprev.d < 0 or rprev.d null) 1 else 0 end) dseqstart (select r.*,              (select max(id)               results r2               r2.id < r.id              )  previd       results r      ) r left outer join      results rprev      on r.previd = rprev.id; 

now, getting cumulative sum tricky because need value twice. unfortunately, mysql not allow subqueries in views. although can use same query below, let me assume results put table tempseq. following assign sequence each value.

select ts.*,        sum(tsprev.aseqstart) aseqid,        sum(tsprev.bseqstart) bseqid,        sum(tsprev.cseqstart) cseqid,        sum(tsprev.dseqstart) dseqid tempseq ts join      tempseq tsprev      on tsprev.id <= ts.id group ts.id; 

once again, let me assume results stored in temporary table, tempseqid, because have aggregate results multiple times. here example a:

select coalesce(max(seqlen), 0) (select aseqid, count(*) seqlen       tempseqid       > 0      ) t 

the condition on a might seem redundant. there off-by-one challenge -- sequences end final negative number. these, can subtract 1 count. however, final sequence may not end way, , undercount it. coalesce case values negative.

at point, let me queries feasible (as single query even) if data structure stored on row id , sequencename , 1 value on row.

edit:

the above reasoning how think problem. in mysql, can approach differently using variables. code simpler:

select max(aposcounter) amaxlen,        max(bposcounter) bmaxlen,        max(cposcounter) cmaxlen,        max(dposcounter) dmaxlen (select r.*,              @aposcounter := if(a > 0, @aposcounter + 1, 0) aposcounter,              @bposcounter := if(b > 0, @bposcounter + 1, 0) bposcounter,              @cposcounter := if(c > 0, @cposcounter + 1, 0) cposcounter,              @dposcounter := if(d > 0, @dposcounter + 1, 0) dposcounter       results r cross join            (select @aposcounter := 0, @amaxlen := 0,                    @bposcounter := 0, @bmaxlen := 0,                    @cposcounter := 0, @cmaxlen := 0,                    @dposcounter := 0, @dmaxlen := 0             end) const       order id      ) r 

this code uses variable logic keep length of "positive sequence length" on each row. aggregates data maximum.

here sqlfiddle demonstrate works.