CODEWITHSUNDEEP
mysqlsql
japem
japem

Reputation: 1111

MySQL - find ranges of rows

Imagine I have this table:

Table
Date           B    C    rownum
1-1-2014       8    2    1
1-2-2014       8    4    2
1-3-2014       8    2    3
1-4-2014       8    5    4
1-1-2014       9    2    5
1-2-2014       9    0    6
1-3-2014       9    0    7
1-4-2014       9    1    8

This is kind of complicated: I want to find ranges of rows where the total sum of column C is <= 1/3 of the number rows in the range, but also to group by column B.

So in this case, the only ranges would be row 6 by itself, row 7 by itself, the range row 6 to row 7, and the range row 6 to row 8 - I ideally want to limit the ranges that are returned to those longer than 2 rows, which would leave just row 6 to row 8.

How on EARTH do I do this in MySQL? Is it possible? I've been trying for a little while but can find nothing that would give me anything resembling what I want.

Thanks in advance.

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Assuming that by sequence you mean ordered by the date column and having the same value of column b, you can generate all the sequences with a self-join:

select t.b, t.date as startdate, t2.date as enddate
from table t join
     table t2
     on t.b = t2.b and t.date <= t2.date;

You can then use another join and aggregation to get the sums:

select t.b, t.date as startdate, t2.date as enddate,
       sum(tt.c) as sumc, count(*) as numrows
from table t join
     table t2
     on t.b = t2.b and t.date <= t2.date join
     table tt
     on tt.b = t.b and tt.date between t.date and t2.date
group by t.b, t.date, t2.date;

You can then add a having clause to get what you want:

having sum(tt.c) <= count(*) / 3

Upvotes: 1

Related Questions