Nistor Constantin Nistor Constantin - 5 years ago 120
SQL Question

Selecting the number of last consecutive days from timestamp (excepting today)

I have a table

A_DailyLogins
with the columns
ID
(auto increment),
Key
(userid) and
Date
(timestamp). I want a query which would return the number of last consecutive days from those timestamp based on the
Key
, for example if he has a row for yesterday, one for two days ago and another one for three days ago, but the last one isn't from four days ago, it would return 3, because this is the number of last days the user was logged in.

My attempt was to create a query selecting the last 7 rows of the players ordered by
Date
DESC (this is what I wanted in the first place, but then I thought that it would be great to have all the last consecutive days), and then I retrieved the query result and compared the dates (converted to year/month/day with functions from that language [Pawn]) and increased the number of consecutive days when a date is before the other one with one day. (but this is extremely slow compared to what I think that can be done directly only with MySQL)

The closest thing I found is this: Check for x consecutive days - given timestamps in database . But it still isn't how I want it to be, it's still pretty different. I tried to modify it, but it is way too hard for me, I don't have that much experience in MySQL.

Answer Source

context

let consecutive login period be a period where the user is logged in on all days ( has an entry in A_DailyLogins on every day in period ) where there is no entry in A_DailyLogins immediately before or after the consecutive login period with the same user

and number of consecutive days be the difference between the maximum and minumum dates in a consecutive login period

the maximum date of a consecutive login period has no login entry immediately after ( sequentially ) to it..

the minimum date of a consecutive login period has no login entry immediately previous ( sequentially ) to it..

plan

  • 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

input

+----+------+------------+
| 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 |
+----+------+------------+

query

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 |
+------+------------------+

valid as run on 2016-01-03

sqlfiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download