mysql 会话数,计算用户在MySQL中连续的每日会话数

How do I calculate the number of sessions a user has which are 1 day apart from each other? This is what I have so far. The answer should be 46, but this code returns just the last ranked record and the difference between it and the very first record. I'd like to get the number 46 as the correct output.

set @pk1 =''; set @rn1 = 1; set @days = '';

select c.user_id, c.day_session, datediff(d.day_session, c.day_session)

from

(select user_id, day_session, rank

FROM

(select user_id, day_session,

@rn1 := if(@pk1=user_id, if(@days=day_session, @rn1, @rn1+1),1) as rank,

@pk1 := user_id,

@days := @day_session

from

(select user_id, date(reg_utc_timestamp) AS day_session

from mobile_traffic

where user_id = 'abcdxyz'

group by 1,2) a) b) c

inner join (select user_id, day_session, rank

FROM

(select user_id, day_session,

@rn1 := if(@pk1=user_id, if(@days=day_session, @rn1, @rn1+1),1) as rank,

@pk1 := user_id,

@days := @day_session

from

(select user_id, date(reg_utc_timestamp) AS day_session

from mobile_traffic

where user_id = 'abcdxyz'

group by 1,2) x) y) d on c.user_id = d.user_id and d.rank = c.rank + 1

When I run just this snippet I get those 46 records.

set @pk1 ='';

set @rn1 = 1;

set @days = '';

select Respondent_ID, day_session, rank

FROM

(select user_id,

day_session,

@rn1 := if(@pk1=user_id, if(@days=day_session, @rn1, @rn1+1),1) as rank,

@pk1 := user_id,

@days := @day_session

from

(select user_id, date(reg_utc_timestamp) AS day_session

from mobile_traffic

where user_id = 'abcdxyz'

group by 1,2) a) b

Here is the example data for this user:

abcdxyz 2017-11-19 1

abcdxyz 2017-11-20 2

abcdxyz 2017-11-21 3

abcdxyz 2017-11-22 4

abcdxyz 2017-11-23 5

abcdxyz 2017-11-24 6

abcdxyz 2017-11-27 7

abcdxyz 2017-11-28 8

abcdxyz 2017-11-29 9

abcdxyz 2017-11-30 10

abcdxyz 2017-12-01 11

abcdxyz 2017-12-02 12

abcdxyz 2017-12-03 13

abcdxyz 2017-12-04 14

abcdxyz 2017-12-05 15

abcdxyz 2017-12-06 16

abcdxyz 2017-12-07 17

abcdxyz 2017-12-08 18

abcdxyz 2017-12-09 19

abcdxyz 2017-12-10 20

abcdxyz 2017-12-11 21

abcdxyz 2017-12-12 22

abcdxyz 2017-12-13 23

abcdxyz 2017-12-14 24

abcdxyz 2017-12-15 25

abcdxyz 2017-12-16 26

abcdxyz 2017-12-17 27

abcdxyz 2017-12-18 28

abcdxyz 2017-12-19 29

abcdxyz 2017-12-20 30

abcdxyz 2017-12-21 31

abcdxyz 2017-12-22 32

abcdxyz 2017-12-23 33

abcdxyz 2017-12-24 34

abcdxyz 2017-12-25 35

abcdxyz 2017-12-26 36

abcdxyz 2017-12-27 37

abcdxyz 2017-12-28 38

abcdxyz 2017-12-29 39

abcdxyz 2017-12-30 40

abcdxyz 2017-12-31 41

abcdxyz 2018-01-01 42

abcdxyz 2018-01-02 43

abcdxyz 2018-01-03 44

abcdxyz 2018-01-04 45

abcdxyz 2018-01-05 46

解决方案

I believe the solution is to use a join on the records that are exactly one day before the date in question. Try this:

SELECT COUNT(*) FROM mobile_traffic m1

INNER JOIN mobile_traffic m2 ON m1.user_id = m2.user_id

AND DATE(m1.reg_utc_timestamp) =

(DATE(m2.reg_utc_timestamp) + INTERVAL 1 DAY)

WHERE m1.user_id =