I need to find any 5 rows that match where clause and they occur "in a row" (they are neighbors)

Question

I have a MySQL table for fictional fitness app. Let's say that app is monitoring user progress on doing pushups day by day.

TrainingDays

id  | id_user  |  date  |  number_of_pushups

Now, I need to find if user have ever managed to do more than 100 pushups 5 days in a row.

I know this is probably doable by fetching all days and then making some php loops, but I wonder if there is possibility to do this in plain mysql...

maybe this could help :- http://stackoverflow.com/questions/19541762/mysql-counting-the-consecutive-number-rows-that-match — Yash Sodha, Oct 10 '14 at 19:39

score 2 · Accepted Answer · answered Oct 10 '14 at 20:04

In MySQL, the easiest way is to use variables. The following gets all sequences of days with 100 or more pushups:

select grp, count(*) as numdaysinarow
from (select (date - interval rn day) as grp, td.*
      from (select td.*,
                   (@rn := if(@i = id_user, @rn + 1
                              if(@i := id_user, 1, 1)
                             ) as rn
            from trainingdays td cross join
                 (select @rn := 0, @i := NULL) vars
            where number_of_pushups >= 100
            order by id_user, date
           ) td
     ) td
group by grp;

This uses the observation that when you subtract a sequence of numbers from a series of dates that increment, then the resulting value is constant.

To determine if there are 5 or more days in a row, use max():

select max(numdaysinarow)
from (select grp, count(*) as numdaysinarow
      from (select (date - interval rn day) as grp, td.*
            from (select td.*,
                         (@rn := if(@i = id_user, @rn + 1
                                    if(@i := id_user, 1, 1)
                                   ) as rn
                  from trainingdays td cross join
                       (select @rn := 0, @i := NULL) vars
                  where number_of_pushups >= 100
                  order by id_user, date
                 ) td
           ) td
      group by grp
     ) td;

Your app can then check the value against whatever minimum you like.

Note: this assumes that there is only one record per day. The above can easily be modified if you are looking for the sum of the number of pushups on each day.

score -1 · Answer 2 · answered Oct 10 '14 at 20:00

-1

Order of records shouldn't be relied on, e.g. with ORDER BY you can change the sequence.

However, you have many functions at hand in a database, which also enables you to use less PHP. What you want is SUM function. Combined with a WHERE clause, this should get you started:

SELECT SUM(number_of_pushups) AS sum_pushups
FROM TrainingDays
WHERE date >= :start_day
AND user_id = :user_id

answered Oct 10 '14 at 20:00

Ali Ben

86
3

This only provides the total number of pushups, not the number of consecutive days where a limit of pushups has been exceeded. – andy Oct 10 '14 at 20:14

I need to find any 5 rows that match where clause and they occur "in a row" (they are neighbors)

2 Answers2