从时间戳中选择连续的天数(今天除外)

Question

我有一个表A_DailyLogins，列为ID (自动增量)、Key (userid)和Date (时间戳)。我想要一个查询，它将根据Key从那些时间戳中返回最后的天数，例如，如果他昨天有一行，一个是两天前的，另一个是三天前的，但是最后一个不是四天前的，它将返回3，因为这是用户登录的最后一天的数目。

我的尝试是创建一个查询，选择由Date DESC订购的最后7行球员(这是我首先想要的，但我认为所有的最后一天都很好)，然后检索查询结果，比较日期(转换为年/月/日与该语言典当中的函数)，并增加了一个日期在另一个日期之前有一天的连续天数。(但与我认为只能用MySQL直接完成的工作相比，这是极其缓慢的)

我发现的最接近的东西是：在数据库中连续检查x天给定的时间戳。但这仍然不是我想要的，它还是很不一样的。我试图修改它，但这对我来说太难了，我在MySQL方面没有那么多经验。

Answer 1

上下文

将consecutive login period设为用户在所有日期登录的期间(在期间的每一天都有A_DailyLogins中的条目)，其中在紧接consecutive login period之前或之后的同一用户中没有A_DailyLogins条目。

number of consecutive days是consecutive login period中最大日期和最小日期之间的差异。

consecutive login period的最大日期在(顺序)之后没有登录项。

consecutive login period的最小日期没有立即(顺序)到它的登录项。

计划

• 使用相同的用户和顺序日期将A_DailyLogins连接到自己，其中右为null以查找最大值
• 求极小的类比逻辑
• 计算具有适当顺序的极小和最大值上的行排序
• 连接行数上的最大值和最小值
• 筛选昨天/今天的最大登录次数
• 计算范围内最大和最小之间的date_diff
• 将join用户放到上面的结果集中，并在用户没有consecutive login period结束的情况下合并

输入

+----+------+------------+
| ID | Key  | Date       |
+----+------+------------+
| 25 | eric | 2015-12-23 |
| 26 | eric | 2015-12-25 |
| 27 | eric | 2015-12-26 |
| 28 | eric | 2015-12-27 |
| 29 | eric | 2016-01-01 |
| 30 | eric | 2016-01-02 |
| 31 | eric | 2016-01-03 |
| 32 | nusa | 2015-12-27 |
| 33 | nusa | 2015-12-29 |
+----+------+------------+

查询

select all_users.`Key`, 
coalesce(nconsecutive, 0) as nconsecutive
from
(
  select distinct `Key`
  from A_DailyLogins
) all_users
left join
(
  select
  lower_login_bounds.`Key`,
  lower_login_bounds.`Date` as from_login,
  upper_login_bounds.`Date` as to_login,
  1 + datediff(least(upper_login_bounds.`Date`, date_sub(current_date, interval 1 day))
                     , lower_login_bounds.`Date`) as nconsecutive
  from
  (
    select curr_login.`Key`, curr_login.`Date`, @rn1 := @rn1 + 1 as row_number
    from A_DailyLogins curr_login
    left join A_DailyLogins prev_login
    on curr_login.`Key` = prev_login.`Key`
    and prev_login.`Date` = date_add(curr_login.`Date`, interval -1 day)
    cross join ( select @rn1 := 0 ) params
    where prev_login.`Date` is null
    order by curr_login.`Key`, curr_login.`Date`
  ) lower_login_bounds
  inner join
  (
    select curr_login.`Key`, curr_login.`Date`, @rn2 := @rn2 + 1 as row_number
    from A_DailyLogins curr_login
    left join A_DailyLogins next_login
    on curr_login.`Key` = next_login.`Key`
    and next_login.`Date` = date_add(curr_login.`Date`, interval 1 day)
    cross join ( select @rn2 := 0 ) params
    where next_login.`Date` is null
    order by curr_login.`Key`, curr_login.`Date`
  ) upper_login_bounds
  on lower_login_bounds.row_number = upper_login_bounds.row_number
  where upper_login_bounds.`Date` >= date_sub(current_date, interval 1 day)
  and   lower_login_bounds.`Date` < current_date
) last_consecutive
on all_users.`Key` = last_consecutive.`Key`
;

输出

+------+------------------+
| Key  | last_consecutive |
+------+------------------+
| eric |                2 |
| nusa |                0 |
+------+------------------+

有效期为2016-01-03

木琴