MySQL计算当前连胜的连续日期 [英] MySQL count consecutive dates for current streak
问题描述
给定具有timestamp
和user
列的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.
stackoverflow/google上的所有示例都涉及查找以前的条纹或计算总条纹,但是我需要了解它们的当前条纹;
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
今天是2014年8月12日:
Today being Aug 12 2014:
bob
连续3天
sue
没有当前条纹
mike
连胜2天
bob
has a streak of 3 days
sue
has no current streak
mike
has a streak of 2 days
此数据是针对每个用户的,因此我将对bob
运行查询并获得3
作为结果.我不需要按用户细分的结果.
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.
您可能需要将-99999
更改为较大(负)值,具体取决于用户可以拥有多少票.
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;
http://sqlfiddle.com/#!2/37129/6
更新
另一种变化
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
http://sqlfiddle.com/#!2/c6dd5b/20
请注意,如果在这篇文章发表之时,小提琴只会返回正确的条纹:)
Note, fiddle will only return correct streaks if run at the date of this post :)
更新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
http://sqlfiddle.com/#!2/5fc6d/7
您可能希望将select *
替换为select streak + 1
,具体取决于您是否希望在连胜中包括第一票.
You may want to replace select *
with select streak + 1
depending on whether you want to include the 1st vote in the streak.
这篇关于MySQL计算当前连胜的连续日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!