MySQL计算当前连胜的连续日期

本文介绍了MySQL计算当前连胜的连续日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

给定具有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;

sqlfiddle/#!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

sqlfiddle/#!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

sqlfiddle/#!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.