MySQL: Count the number of items within a range

I am attempting to analyze some web logs from my webserver. I pushed all the logs for the last week into a mysql database and I am analyzing the logs.

I have generated a table of sessionIDs and the length of the session 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 | 
...

What I want to do now is modify the statement so that it will produce something like this:

Range (time)     Number of Sessions
0 to 2           10
2 to 4            4
4 to 6           开发者_StackOverflow中文版60
...

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

I've edited your post to add an alias, it makes the results more readable. Now I think you could try something like this:

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;

Run this query over 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

You may want to create another table, calling it ranges:

CREATE TABLE ranges (
   `range` int
);

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

Then you may want to wrap your query as a derived table, and left join the ranges table with the derived table:

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`;

For a test case, let's create a dummy table with a bunch of random session lengths, as in your example:

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);

Then we can do the following, assuming that the ranges table has already been created:

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`;

Result:

+--------+--------------------+
| 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)