mysql倒数第几天_mysql – 从时间戳中选择最后连续几天的数量(今天除外)

上下文

让连续登录期间为用户全天登录的时间段(在每个时段的A_DailyLogins中有一个条目),其中在同一用户的连续登录期之前或之后A_DailyLogins中没有条目

连续天数是连续登录期间的最大日期和最小日期之间的差异

连续登录期的最大日期在(顺序)之后没有立即登录条目.

连续登录期的最短日期之前(顺序)没有登录条目.

计划

left join A_DailyLogins to itself using same user and sequential dates where right is null to find maximums

analogous logic to find minimums

calculate row ordering over minimums and maximums with appropriate order by

join maximums and minimums on row number

filter where maximum login is yesterday/today

calculate date_diff between maximum and minimum in range

left join users to above resultset and coalesce over the case where user does not have a consecutive login period ending yesterday/today

输入

+----+------+------------+

| 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运行