CODEWITHSUNDEEP
mysqlsql
Get Off My Lawn
Get Off My Lawn

Reputation: 36299

Count the number of user sessions

I have a table with ip's and a view date and for websites with a website id

+------------+-----------+---------------------+
| website_id | ip        | view_date           |
+------------+-----------+---------------------+
| 123        | 123123123 | 2014-01-01 10:23:00 |
+------------+-----------+---------------------+
| 123        | 987987987 | 2014-01-01 10:23:00 |
+------------+-----------+---------------------+
| 123        | 123123123 | 2014-01-01 10:26:00 |
+------------+-----------+---------------------+
| 123        | 987987987 | 2014-01-01 10:24:00 |
+------------+-----------+---------------------+
| 123        | 987987987 | 2014-01-01 10:25:00 |
+------------+-----------+---------------------+
| 123        | 123123123 | 2014-01-02 03:23:00 |
+------------+-----------+---------------------+

I would like to get sessions from the table, which would be a time someone starts on the site until they are done. To find out when they were done there is 30 minutes or more of no activity after one of the between 2 view dates when view_date is ordered desc.

So, with this example data I have 2 users 123123123 and 987987987

I have no idea how I would calculate that using MySQL. I all I know is to take the data order it by ip, then by view_date. After that I am lost.

When I say session I mean:

a period of time devoted to a particular activity.

And Not:

a way of tracking people between multiple web pages.

Desired Output:

+------------+-----------+----------+
| website_id | ip        | sessions |
+------------+-----------+----------+
| 123        | 123123123 | 2        |
+------------+-----------+----------+
| 123        | 987987987 | 1        |
+------------+-----------+----------+

Upvotes: 0

Views: 1943

Answers (4)

SQLChao
SQLChao

Reputation: 7837

Here's my solution

I do a count to find out the number of rows for a given website id and ip address that occur between the date and the date -30 minutes. If its 0 assign a 1 meaning its a new session else give it a 0. Then do a sum.

SQL Fiddle Demo

select website_id,
    ip,
    sum(newSession) as Sessions
from
(select *,
case 
    when (select count(*) 
          from yourTable ytb 
          where ytb.website_id = yta.website_id 
          and ytb.ip = yta.ip 
          and ytb.view_date < yta.view_date 
          and ytb.view_date > date_add(yta.view_date, INTERVAL -30 MINUTE)) = 0 then 1
    else 0
end as newSession
from yourtable yta) baseTable
GROUP BY website_id, ip

Upvotes: 2

nbirla
nbirla

Reputation: 610

After sorting according to time and IP you can use cursors to find the total number of sessions. You can get more information on mysql cursors from here.

More details : 

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE i INT;
  DECLARE prev_i INT;
  DECLARE d DATE;
  DECLARE prev_d DATE;
  DECLARE cur1 CURSOR FOR SELECT ip, view_date FROM table;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;

  read_loop: LOOP
    IF prev_i == NULL THEN
        FETCH cur1 INTO prev_i, prev_d;
    END IF;
    FETCH cur1 INTO i, d;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF prev_i == i && INTERVAL_BETWEEN(prev_d, d) > 30min THEN
      ADD_IN_SESSIONS_FOR_i ;
    END IF;
    prev_i = i;
    prev_d = d;
  END LOOP;

  CLOSE cur1;
END;

Upvotes: -1

fancyPants
fancyPants

Reputation: 51868

select ip, sum(session) + 1 as session
from (
    select
    t.*,
    if(@previp = ip and timestampdiff(minute, @prevview, view_date) >= 30, 1, 0) as session,
    @previp := ip,
    @prevview := view_date
    from
    Table1 t
    , (select @prevview := (select view_date from Table1 order by ip, view_date limit 1), 
       @previp := null) var_init_subquery
    order by ip, view_date
) sq
group by ip

Upvotes: 1

John Ruddell
John Ruddell

Reputation: 25842

SET @a := null;
SET @b := null;
SET @c := null;
SELECT website_id, ip, view_date, COUNT(counting) as num_sessions
FROM
(   SELECT website_id, ip, view_date, 
        @c := if(@a = ip AND @b BETWEEN view_date - interval 30 minute AND view_date, @c + 1, 1) as counting, 
        @a := ip, @b := view_date
    FROM
    (   SELECT * 
        FROM sessions
        ORDER BY ip, view_date
    )t
)t1
WHERE counting = 1
GROUP BY ip;

DEMO

Upvotes: 1

Related Questions