MySQL statistics for a continuous date (day) Count

First DECLARE reference: http://www.oschina.net/question/573517_118821

Table Definition

CREATE TABLE ' Date_add ' (
' id ' int (one) not NULL auto_increment,
' uid ' int (one) not NULL DEFAULT ' 0 ',
' Date ' date is not NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' idx_ud ' (' uid ', ' date ')
) Engine=innodb auto_increment=23 DEFAULT charset=latin1

Insert statement

INSERT into ' date_add ' (' id ', ' uid ', ' date ') VALUES

(1, 1, ' 2015-09-01 '),

(2, 1, ' 2015-09-02 '),

(17, 1, ' 2015-09-03 '),

(18, 1, ' 2015-09-04 '),

(3, 2, ' 2015-09-01 '),

(4, 2, ' 2015-09-02 '),

(19, 2, ' 2015-09-03 '),

(20, 2, ' 2015-09-04 '),

(5, 3, ' 2015-09-01 '),

(6, 3, ' 2015-09-02 '),

(21, 3, ' 2015-09-03 '),

(22, 3, ' 2015-09-04 '),

(7, 4, ' 2015-09-01 '),

(8, 4, ' 2015-09-02 '),

(9, 5, ' 2015-09-01 '),

(10, 5, ' 2015-09-02 '),

(11, 6, ' 2015-09-01 '),

(12, 6, ' 2015-09-02 '),

(13, 7, ' 2015-09-01 '),

(14, 7, ' 2015-09-02 '),

(15, 8, ' 2015-09-01 '),

(16, 8, ' 2015-09-02 ');

Table below

ID UID date

1 1 2015-09-01

2 1 2015-09-02

1 2015-09-03

1 2015-09-05

3 2 2015-09-01

4 2 2015-09-02

2 2015-09-03

2 2015-09-04

5 3 2015-09-01

6 3 2015-09-02

3 2015-09-03

3 2015-09-04

7 4 2015-09-01

8 4 2015-09-02

9 5 2015-09-01

5 2015-09-02

6 2015-09-01

6 2015-09-02

7 2015-09-01

7 2015-09-02

8 2015-09-01

8 2015-09-02

First, SQL follows

select uid, ' Date ', @countday: = (Case when (@ Last_uid:=uid and datediff (' Date ', @last_date) =1) then  (@countday + 1) else 1 end )  as countday , (@group_id: = (@group_id +if (@countday =1,1,0))  asgroup_id, @last_uid:=uid  As last_uid, @last_date: = ' Date '  as last_date from  (select  ' uid ', ' Date '  from  date_add order by uid, ' date '  ) as t1, ([Email protected]:=0, @group_id: =0,@ Last_uid:= ', @last_date: = ')  as t2 

Select UID, ' Date ', @countday: = (case if (@last_uid: =uid and DATEDIFF (' Date ', @last_date) =1) then (@countday + 1) Else 1 end As Countday, (@group_id: = (@group_id +if (@countday =1,1,0))) asgroup_id, @last_uid: =uid as Last_uid, @last_date: = ' Date ' As Last_date from (SELECT ' UID ', ' date ' from Date_add order by UID, ' Date ') as T1, ([email protected]:=0, @group_id: =0, @last_u Id:= ", @last_date: =") as T2

Results

UID date countday group_id last_uid last_date

1 2015-09-01 1 1 1 2015-09-01

1 2015-09-02 2 1 1 2015-09-02

1 2015-09-03 3 1 1 2015-09-03

1 2015-09-05 1 2 1 2015-09-05

2 2015-09-01 1 3 2 2015-09-01

2 2015-09-02 2 3 2 2015-09-02

2 2015-09-03 3 3 2 2015-09-03

2 2015-09-04 4 3 2 2015-09-04

3 2015-09-01 1 4 3 2015-09-01

3 2015-09-02 2 4 3 2015-09-02

3 2015-09-03 3 4 3 2015-09-03

3 2015-09-04 4 4 3 2015-09-04

4 2015-09-01 1 5 4 2015-09-01

4 2015-09-02 2 5 4 2015-09-02

5 2015-09-01 1 6 5 2015-09-01

5 2015-09-02 2 6 5 2015-09-02

6 2015-09-01 1 7 6 2015-09-01

6 2015-09-02 2 7 6 2015-09-02

7 2015-09-01 1 8 7 2015-09-01

7 2015-09-02 2 8 7 2015-09-02

8 2015-09-01 1 9 8 2015-09-01

8 2015-09-02 2 9 8 2015-09-02

Then, the SQL

select uid,min (date)  as mindate   max (date)  as maxdate,max (countday)  countday,group_concat (date)  dates from  ( select uid, ' Date ', @countday: = (Case when (@last_uid: =uid anddatediff (' Date ', @last_date) =1) then  (@countday + 1) else 1 end )  as countday , (@group_id: = (@group_id +if (@ countday=1,1,0))  as group_id, @last_uid: =uid as last_uid, @last_date: = ' Date '  aslast_ date from  (select  ' uid ', ' Date '  from date_add order by uid, ' date '   ) As t1, (select  @countday: =0, @group_id: =0, @last_uid: = ', @last_date: = ')  as t2)  as  t3 group by group_id 

Select Uid,min (date) as MinDate, Max (date) as Maxdate,max (Countday) countday,group_concat (date) dates from (select UID, ' Date ', @countday: = (@last_uid: =uid anddatediff (' Date ', @last_date) =1) then (@countday + 1) Else 1 end) as Countday , (@group_id: = (@group_id +if (@countday =1,1,0))) as group_id, @last_uid: =uid as Last_uid, @last_date: = ' Date ' aslast_date From (the Select ' UID ', ' Date ' from the Date_add Order by UID, ' Date ') as T1, (select @countday: =0, @group_id: =0, @last_uid: = ", @last _date:= ") as T2) as T3 GROUP by group_id

Final result (countday consecutive days)

UID mindate maxdate countday dates

1 2015-09-01 2015-09-03 3 2015-09-01,2015-09-02,2015-09-03

1 2015-09-05 2015-09-05 1 2015-09-05

2 2015-09-01 2015-09-04 4 2015-09-03,2015-09-04,2015-09-01,2015-09-02

3 2015-09-01 2015-09-04 4 2015-09-03,2015-09-04,2015-09-01,2015-09-02

4 2015-09-01 2015-09-02 2 2015-09-02,2015-09-01

5 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02

6 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02

7 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02

8 2015-09-01 2015-09-02 2 2015-09-01,2015-09-02

MySQL statistics for a continuous date (day) Count