CODEWITHSUNDEEP
mysql
Paulo Bueno
Paulo Bueno

Reputation: 2569

Yet another count/rank for consecutive events in the last 'n' days in MySQL

I'm having trouble in to setup a query to count/rank the consecutive days an event had happened in the last 'n' days. I.e: If an event had happened consecutively 'k' times in the last 'n' days, with no gaps in relation to the reference date.

Visually (0 means no event, and 1 means an event) from current date to the past 'n' days:

case A - 01111 (count as 0 since there's no event in the previous date).
case B - 11001 (count as 2 consecutive days)
case C - 11110 (count as 4 consecutive days)
case D - 00110 (also count as 0)
case E - 10111 (count as 1)... and so on

Considering a 'pseudo' structure:

Table1
uid
eventid

table2
eventid
datetime

What I need as result is a 'rank' of uid wich has consecutive events in the last n days so if I got these values on Table1 and Table2:

Table1
(1, 100)
(1, 101)
(1, 102)
(1, 103)
(2, 104)
(2, 105)
(2, 106)

Table2
(100, '2015-09-11 00:00:00')
(101, '2015-09-10 00:00:00')
(102, '2015-09-09 00:00:00')
(103, '2015-09-07 00:00:00')
(104, '2015-09-11 00:00:00')
(105, '2015-09-10 00:00:00')
(106, '2015-09-05 00:00:00')

The result shoud be:

uid - consecutive
1 - 3
2 - 2

That means uid 1 has events 100, 101, and 102 consecutively in the last days but eventid 103 is out because it is not consecutive.

I've looking for similar questions and tried to apply some of them, but none consider the consecutive days based on a reference (current) date.

The near I've come is to make a left join using the date for each case something similar to:

SELECT * FROM
(SELECT * FROM Table1 LEFT JOIN Table2 USING (uid) WHERE date(datetime)=curdate()-interval 3 day) 
LEFT JOIN (SELECT * FROM Table1 LEFT JOIN Table2 USING (uid) WHERE date(datetime)=curdate()-interval 2 day)
LEFT JOIN (SELECT * FROM Table1 LEFT JOIN Table2 USING (uid) WHERE date(datetime)=curdate()-interval 1 day)

Also my knowledge in using var inside SQL are almost none, and if possible I'd like to avoid using procedures, making a single call from inside php to mysql.

Thx

Upvotes: 0

Views: 258

Answers (1)

uri2x
uri2x

Reputation: 3202

You can use a counter variable to do just that.

First we'll get a table with uid and datetime columns, ordered properly:

SELECT `uid`,`datetime` FROM `table1`
INNER JOIN `table2` ON `table1`.`eventid`=`table2`.`eventid`
ORDER BY `uid`, `datetime`

Now we'll use that as a subquery, advancing a counter each time the uid is the same as last row, and the datetime is 1 day ahead of the last row's date. Here's how it's done:

SET @counter = 1;
SET @lastDate = '2010-01-01 00:00:00';
set @lastUid = 0;

SELECT
  `uid`,
  IF (((DATE_ADD(@lastDate, INTERVAL 1 DAY)=`datetime`) AND (`uid`=@lastUid)),
    @counter := @counter+1, @counter := 1),
  (@lastUid := `uid`),
  (@lastDate := `datetime`),
  @counter as `counter`
FROM (
  SELECT `uid`,`datetime` FROM `table1`
  INNER JOIN `table2` ON `table1`.`eventid`=`table2`.`eventid`
  ORDER BY `uid`, `datetime` ASC
  ) `uidDateTable`

Now all we have left to do is to use that query as a subquery and find the MAX value of counter per uid. So that's the actual query you need:

SET @counter = 1;
SET @lastDate = '2010-01-01 00:00:00';
set @lastUid = 0;

SELECT `uid`, max(`counter`)
FROM (
  SELECT
    `uid`,
    IF (((DATE_ADD(@lastDate, INTERVAL 1 DAY)=`datetime`) AND (`uid`=@lastUid)),
      @counter := @counter+1, @counter := 1),
    (@lastUid := `uid`),
    (@lastDate := `datetime`),
    @counter as `counter`
  FROM (
    SELECT `uid`,`datetime` FROM `table1`
    INNER JOIN `table2` ON `table1`.`eventid`=`table2`.`eventid`
    ORDER BY `uid`, `datetime` ASC
    ) `uidDateTable`
  ) `uidCounters`
GROUP BY `uid`;

Upvotes: 1

Related Questions