Select longest streak in days from mysql database?

Question

First off, I have found quite a few similar questions on both Google and Stackoverflow but I can't seem to grasp how to do this correctly.

I have a table that looks like this:

    id      ms_date
    ------------------
    1     2018-11-18
    2     2018-11-18
    3     2018-11-20
    4     2018-11-22
    5     2018-11-25
    6     2018-11-26
    7     2018-11-26
    8     2018-11-27
    9     2018-11-28
   10     2018-11-29

What i need to do is to get the longest streak in Days from that table.

So in the above example, the longest streak is 4 days.

I found this: https://dzone.com/articles/how-to-find-the-longest-consecutive-series-of-even

and it explains what he's trying to do which is SIMILAR to what I am trying to achieve but then it is so badly explained/written that I cannot make sense of it.

I also need to find the GAP between those dates and start counting the streak again. So again in the example above, because there's a gap between the dates, the Current Streak should be 3 Days.

I've tried to used the code from the link above but that is based on SQL and also it has some strange WITH words in the code which doesn't make sense at all.

Is there an easy way to achieve this using PHP and Mysql ?

Could someone please advice on this issue?

Thanks in advance.

EDIT:

I can't seem to find the software version in my PHPMYADMIN but this is what i can see under the Database server section:

Server: Localhost via UNIX socket
Server type: MariaDB
Server connection: SSL is not being used Documentation
Server version: 10.0.37-MariaDB-0+deb8u1 - (Debian)
Protocol version: 10
User: freemind@localhost
Server charset: UTF-8 Unicode (utf8)

Second EDIT:

Tried the following based on the below answer and I get nothing at all:

$sql_COUNT = "SELECT COUNT(*) max_streak 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = val - 1 THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=val  
         FROM 
            ( SELECT DISTINCT ms_date FROM MY_TABLE ) x
         JOIN 
            ( SELECT @prev:=null,@i:=0 ) vars 
        ORDER 
           BY ms_date
     ) a 
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC LIMIT 1";
$query_COUNT = mysqli_query($db_conx, $sql_COUNT);
$productCount_COUNT = mysqli_num_rows($query_COUNT); // count the output amount

echo $productCount_COUNT;

Third Edit:

The following code echo's 1 on my page BUT based on what I have in MYSQL database, it should echo 4:

$sql_COUNT = "SELECT COUNT(*) AS max_streak 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = ms_date - 1 THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=ms_date  
         FROM 
            ( SELECT DISTINCT ms_date FROM MY_TABLE ) x
         JOIN 
            ( SELECT @prev:=null,@i:=0 ) vars 
        ORDER 
           BY ms_date
     ) a 
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC LIMIT 1";
$query_COUNT = mysqli_query($db_conx, $sql_COUNT);

$productCount_COUNT = mysqli_num_rows($query_COUNT); // count the output amount

echo $productCount_COUNT;

Fourth EDIT:

I'm not sure if the answers below was tested but it doesn't work for me.

i tried the folowings and even though I get no errors, I only see a blank page which means the code is not working:

$sql_COUNT = "SELECT COUNT(*) max_streak 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = ms_date - INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=ms_date  
         FROM 
            ( SELECT DISTINCT ms_date FROM MY_TABLE ORDER BY ms_date ) x
         JOIN 
            ( SELECT @prev:=null,@i:=0 ) vars 
     ) a 
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC";
$query_COUNT = mysqli_query($db_conx, $sql_COUNT);

$count = mysqli_fetch_array($query_COUNT);
echo $count[0];

Strawberry · Accepted Answer

Consider the following...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,val INT NOT NULL
);

INSERT INTO my_table VALUES
(1   , 11),
(2   , 11),
(3   , 12),
(4   , 13),
(5   , 14),
(6   , 17),
(7   , 18),
(8   , 20),
(9   , 21),
(10  , 22);

SELECT COUNT(*) max_streak 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = val - 1 THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=val  
         FROM 
            ( SELECT DISTINCT val FROM my_table ) x
         JOIN 
            ( SELECT @prev:=null,@i:=0 ) vars 
        ORDER 
           BY val
     ) a 
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC LIMIT 1;
+------------+
| max_streak |
+------------+
|          4 |
+------------+
1 row in set (0.01 sec)

EDIT:

If you're using dates, then the logic is the same, but you just have to substitute a bit of date arithmetic...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,val DATE NOT NULL
);

INSERT INTO my_table VALUES
(1   , '2018-11-11'),
(2   , '2018-11-11'),
(3   , '2018-11-12'),
(4   , '2018-11-13'),
(5   , '2018-11-14'),
(6   , '2018-11-17'),
(7   , '2018-11-18'),
(8   , '2018-11-20'),
(9   , '2018-11-21'),
(10  , '2018-11-22');

SELECT COUNT(*) max_streak 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = val - INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=val  
         FROM 
            ( SELECT DISTINCT val FROM my_table ) x
         JOIN 
            ( SELECT @prev:=null,@i:=0 ) vars 
        ORDER 
           BY val
     ) a 
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC LIMIT 1;
+------------+
| max_streak |
+------------+
|          4 |
+------------+

Purists would argue (correctly) that I'm using @variable assignments in an incorrect manner - but it's just a bad habit I picked up. That said, I think a correct assignment method is as follows, but no doubt someone will let me know if I'm mistaken...

SELECT COUNT(*) max_streak 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = val - INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=val  
         FROM 
            ( SELECT DISTINCT val FROM my_table ORDER BY val ) x
         JOIN 
            ( SELECT @prev:=null,@i:=0 ) vars 
     ) a 
 GROUP 
    BY i 
 ORDER 
    BY max_streak DESC LIMIT 1;

If you want to bundle this up inside some PHP, I guess it might look like this (although it should be obvious from what follows that application code is not really my forte)...



-- outputs

Array ( [max_streak] => 4 )

Select longest streak in days from mysql database?

Answers (1)

Related Questions