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

一尘不染

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

sql

给定一个带有timestamp和user列的MySQL表，我希望能够计算给定用户存在的连续记录天数（今天必须到今天结束）。

stackoverflow / google上的所有示例都涉及查找以前的条纹或计算总条纹，但是我需要了解它们 当前的 条纹；

我可以使用它来查找前一天有记录的所有日期：

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

但是我只需要计算包含今天记录的范围。

根据请求，一些示例数据：

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日：

bob有3天的连胜， sue没有当前连胜， mike有2天的连胜，

这些数据是每个用户的，因此我将对其进行查询bob并获得3结果。我不需要按用户细分的结果。

阅读 278

2021-03-17

共1个答案

一尘不染

该查询将条纹计数保留在变量中，并且一旦存在间隔，它将计数重置为较大的负数。然后返回最大条纹。

根据用户可拥有的票数，您可能需要更改-99999为更大的（负数）值。

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

请注意，如果在这篇文章的日期运行小提琴，则只会返回正确的条纹:)

更新2

下面的查询适用于允许同一用户每天通过从删除重复日期的派生表中进行选择而每天多次投票的表。

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取决于是否要在连胜中包括第一票。

2021-03-17