计算用户在MySQL中连续的每日会话数 [英] Calculate the number of consecutive daily sessions a user has in MySQL

问题描述

如何计算用户相隔1天的会话数？到目前为止，这就是我所拥有的。答案应该是46，但是此代码仅返回最后排序的记录以及它与第一条记录之间的差。我想将数字46作为正确的输出。

  set @ pk1 =’’;设置@ rn1 = 1;设置@days =‘’; 
 
选择c.user_id，c.day_session，datediff（d.day_session，c.day_session）
 
 from 
 
（选择user_id，day_session，从
 
排名
 
（选择user_id，day_session，
 @ rn1：= if（@ pk1 = user_id，if（@ days = day_session，@ rn1，@ rn1 + 1），1）作为等级，
 @ pk1：= user_id，
 @days：= @day_session 
 
从
 
（选择user_id ，date（reg_utc_timestamp）AS day_session 
 
来自mobile_traffic 
 
，其中user_id ='abcdxyz'
 
组1,2）a）b）c 
 
内部联接（选择user_id，day_session，等级
 
从
 
（选择user_id，day_session，
 @ rn1：= if（ @ pk1 = user_id，if（@ days = day_session，@ rn1，@ rn1 + 1），1）作为等级，
 @ pk1：= user_id，
 @days：= @day_session 
 
 from 
 
（选择user_id，date（reg_utc_timestamp）AS day_session 
 
来自mobile_traffic 
 
其中user_id ='abcdxyz'
 
乘以1,2） x）y）d on c.user_id = d.user_id和d.rank = c.rank + 1 
  

当我只运行此代码片段时，我得到了46条记录。

  set @ pk1 =''; 
 set @ rn1 = 1; 
 set @days =’’; 
 
选择受访者ID，day_session，等级
 
 FROM 
 
（选择user_id，
 day_session，
 @ rn1：=如果（@ pk1 = user_id，if（@ days = day_session，@ rn1，@ rn1 + 1），1）作为等级，
 @ pk1：= user_id，
 @days：= @day_session 
 
 from 
 
（选择user_id，date（reg_utc_timestamp）AS day_session 
 
来自mobile_traffic 
 
其中user_id ='abcdxyz'
 
组的1,2）a）b 
  

以下是示例数据该用户：

  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 
 abc dxyz 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 
  

 从mobile_traffic m1中选择COUNT（*）
 INNER JOIN mobile_traffic m2在m1上。user_id= m2。 user_id 
 AND DATE（m1.reg_utc_timestamp）= 
（DATE（m2.reg_utc_timestamp）+间隔1天）
 W1位置m1.user_id =< some_user_id> 
  

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 = <some_user_id>

这篇关于计算用户在MySQL中连续的每日会话数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！