MySQL: counting the number of elements within a range

Question

I am trying to parse some weblogs from my web server. I ran all the logs last week in the mysql database and I am analyzing the logs.

I created a table sessionIDand session length using this mysql command:

SELECT 
        Log_Analysis_RecordsToSesions.sessionID, 
        ABS(TIMEDIFF(
                MIN(Log_Analysis_Records.date), 
                MAX(Log_Analysis_Records.date)
        )) as session_length
FROM 
        Log_Analysis_RecordsToSesions, 
        Log_Analysis_Records 
WHERE 
        Log_Analysis_RecordsToSesions.recordID=Log_Analysis_Records.recordID 
GROUP BY 
        sessionID;

-

+-----------+----------------+
| sessionID | session_length |
+-----------+----------------+
|         1 |    2031.000000 | 
|         2 |    1954.000000 | 
|         3 |     401.000000 | 
...

Now I want to change the instruction so that it produces something like this:

Range (time)     Number of Sessions
0 to 2           10
2 to 4            4
4 to 6           60
...

The range will be a fixed amount of time, and I want to count the number of sessions in this range. My first thought is to iterate through all this with php, but it seems very time consuming and rude. Is there a way to do this in mysql?

Answer 1

, , . , - :

SELECT 
        Log_Analysis_RecordsToSesions.sessionID, 
        ABS(TIMEDIFF(
                MIN(Log_Analysis_Records.date), 
                MAX(Log_Analysis_Records.date)
        )) as session_length,
        CONCAT(session_length DIV 2, ' to ', session_length DIV 2 + 2) as range
FROM 
        Log_Analysis_RecordsToSesions, 
        Log_Analysis_Records 
WHERE 
        Log_Analysis_RecordsToSesions.recordID=Log_Analysis_Records.recordID 
GROUP BY 
         range
ORDER BY session_length;

Answer 2

, ranges:

CREATE TABLE ranges (
   `range` int
);

INSERT INTO ranges VALUES (2), (4), (6), (8);

ranges :

SELECT   CONCAT(r.`range` - 2, ' to ', r.`range`) `range`,
         COUNT(session_length) number_of_sessions
FROM     ranges r
LEFT JOIN (
    SELECT    rs.sessionID, 
              ABS(TIMEDIFF(MIN(ar.date), MAX(ar.date))) session_length
    FROM      Log_Analysis_RecordsToSesions rs, 
    JOIN      Log_Analysis_Records ar ON (rs.recordID = ar.recordID )
    GROUP BY  rs.sessionID;
) dt ON (dt.session_length > r.`range` - 2 AND 
         dt.session_length <= r.`range`)
GROUP BY r.`range`;

, :

CREATE TABLE sessions (
    session_id      int,
    session_length  int
);

INSERT INTO sessions VALUES (1, 2031);
INSERT INTO sessions VALUES (2, 1954);
INSERT INTO sessions VALUES (3, 401);
INSERT INTO sessions VALUES (4, 7505);

, , ranges :

SELECT   CONCAT(r.`range` - 2, ' to ', r.`range`) `range`,
         COUNT(session_length) number_of_sessions
FROM     ranges r
LEFT JOIN (
    SELECT session_id, session_length FROM sessions
) dt ON (dt.session_length / 1000 > r.`range` - 2 AND 
         dt.session_length / 1000 <= r.`range`)
GROUP BY r.`range`;

:

+--------+--------------------+
| range  | number_of_sessions |
+--------+--------------------+
| 0 to 2 |                  2 |
| 2 to 4 |                  1 |
| 4 to 6 |                  0 |
| 6 to 8 |                  1 |
+--------+--------------------+
4 rows in set (0.00 sec)

Answer 3

Run this query against your generated table:

SELECT 
  CONCAT((session_length div 2000)*2, ' to ', ((session_length+2000) div 2000)*2) AS `Range (time)`, 
  COUNT(*) AS `Number of sessions` 
FROM sessions 
GROUP BY session_length div 2000