Selecting the number of last days in a row from a timestamp (excluding today)
I have a table A_DailyLogins
with ID
(auto increment), Key
(userid) and Date
(timestamp) columns . I need a query that will return the number of last days in a row from that timestamp based on Key
, for example, if it has a row from yesterday, one two days ago and another three days ago, but the last one is not from four days ago, it will return 3 because this is the number of the last days the user was logged in.
My attempt was to create a query selecting the last 7 rows of players ordered by Date
DESC (this is what I wanted in the first place, but then I thought it would be great to have all the last consecutive days) and then I got the result query and compared dates (converted to year / month / day with functions from that language [Pawn]) and incremented the number of consecutive days when a date precedes another with one day. (but this is very slow compared to what I think it can be done directly with just MySQL)
The closest I have found is this: Check x days in a row - given timestamps in the database . But this is still not how I want it, it is still very different. I tried to change it, but it is too difficult for me, I don't have that much experience in MySQL.
source to share
context
let consecutive login period
be the period when the user is registered on all days (has an entry in A_DailyLogins for every day in the period), where there is no entry in A_DailyLogins immediately before or after consecutive login period
with the same user
and number of consecutive days
- the difference between the dates of the maximum and minimum inconsecutive login period
the maximum date consecutive login period
has no login immediately after (sequentially) to it.
the minimum date consecutive login period
has no input record immediately preceding (sequentially) to it.
plan
- left join
A_DailyLogins
for yourself using the same custom and sequential dates where right is null to find the maximum values- similar logic for finding minimums
- calculate row order by minimums and maximums with corresponding order
- Maximum and minimum values for the number of rows
- filter where the maximum login is yesterday / today
- calculate date_diff between maximum and minimum ranges
- on the left are appended to the above result set and merged in the case where the user has no
consecutive login period
ending yesterday / today
entrance
+----+------+------------+
| 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 |
+----+------+------------+
request
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`
;
Output
+------+------------------+
| Key | last_consecutive |
+------+------------------+
| eric | 2 |
| nusa | 0 |
+------+------------------+
acts as launched 2016-01-03
source to share