mysql计算一个月连续日期,MySQL计算当前连胜的连续日期

Given a MySQL table with timestamp and user columns, I'd like to be able to count how many consecutive days (must end with today) records exist for a given user.

All of the examples on stackoverflow/google involve finding previous streaks, or counting total streaks, but I need to know about their current streak;

I can use this to find all days of which there are records for the day prior:

select date(start_of_votes.date_created)

from votes start_of_votes

left join votes previous_day

on start_of_votes.username = previous_day.username

and date(start_of_votes.date_created) - interval 1 day = date(previous_day.date_created)

where previous_day.id is not null

and start_of_votes.username = "bob"

group by date(start_of_votes.date_created) desc

But I need to only count ranges that include a record for today.

Per request, some sample data:

bob 2014-08-10 00:35:22

sue 2014-08-10 00:35:22

bob 2014-08-11 00:35:22

mike 2014-08-11 00:35:22

bob 2014-08-12 00:35:22

mike 2014-08-12 00:35:22

Today being Aug 12 2014:

bob has a streak of 3 days

sue has no current streak

mike has a streak of 2 days

This data is per-user, so I'll run a query for bob and get 3 as the result. I don't need a result broken down by user.

解决方案

The query keeps the streak count in a variable and as soon as there's a gap it resets the count to a large negative. It then returns the largest streak.

Depending on how many votes a user can have you might need to change -99999 to a larger (negative) value.

select if(max(maxcount) < 0, 0, max(maxcount)) streak

from (

select

if(datediff(@prevDate, datecreated) = 1, @count := @count + 1, @count := -99999) maxcount,

@prevDate := datecreated

from votes v cross join

(select @prevDate := date(curdate() + INTERVAL 1 day), @count := 0) t1

where username = 'bob'

and datecreated <= curdate()

order by datecreated desc

) t1;

Update

Another variation

select * from (

select datecreated,

@streak := @streak+1 streak,

datediff(curdate(),datecreated) diff

from votes

cross join (select @streak := -1) t1

where username = 'bob'

and datecreated <= curdate()

order by datecreated desc

) t1 where streak = diff

order by streak desc limit 1

Note, fiddle will only return correct streaks if run at the date of this post :)

Update 2

The query below works with tables that allow multiple votes per day by the same user by selecting from a derived table where duplicate dates are removed.

select * from (

select date_created,

@streak := @streak+1 streak,

datediff(curdate(),date_created) diff

from (

select distinct date(date_created) date_created

from votes where username = 'pinkpopcold'

) t1

cross join (select @streak := -1) t2

order by date_created desc

)

t1 where streak = diff

order by streak desc limit 1

You may want to replace select * with select streak + 1 depending on whether you want to include the 1st vote in the streak.