mySQL - results per number of seconds.

Question

Hello, Im trying to work out (and failing) how I can get the number of sessions (just using that term generically, dont worry too much) per second. I.e. im looking for retention.

I have a table that is updated periodically with the number of seconds that a session is active.

I want to get a list of:-

MAX(`time) - i.e. the highest number of seconds for a session.

RESULTING IN: seconds : number of sessions on that second

The way I've been faffing with is

SELECT `projID`, COUNT(`sessionID`) AS `noSessions`, 
    MAX(`time`) as `endTime` 
FROM `MetricsLive` 
GROUP BY `sessionID` 
ORDER by `endTime` ASC

But that's nowhere near what I need, but I'm wholly lost. Any ideas?

FORGIVE FORMATTING:-

THE TABLE ROW DATA I HAVE IS AS:-

SESSSION \\ SECONDS 
ebsvmcrbob2irbobsrvu2hcfi5 \\ 4 
ebsvmcrbob2irbobsrvu2hcfi5 \\ 12 
ebsvmcrbob2irbobsrvu2hcfi5 \\ 18 
ebsvmcrbob2irbobsrvu2hcfi5 \\ 24 
ebsvmcrbob2irbobsrvu2hcfi5 \\ 32

etc. It's rolling inserting a new row everytime a new entry is made. The idea here is to deduce after how long the session ended. So, I want the result of

NoSessionsEnded \\ ATSECONDS 
4 \\ 12 
8 \\ 24 etc.

Answer 1

You can use the UNIX_TIMESTAMP() function to convert a date to the number of seconds since 1970. If you group on that, COUNT(*) will return the number of rows sharing the same second:

SELECT 
    projID, 
    COUNT(*) AS MaxSessionsPerSecond
FROM MetricsLive
GROUP BY UNIX_TIMESTAMP(endTime)
ORDER BY COUNT(*) DESC
LIMIT 1

The ORDER BY and LIMIT rows select the projID with the maximum number of sessions per second.

If you're looking for the maximum session rate for each project, try:

SELECT
    projID,
    MAX(SessionsPerSecond) as MaxSessionsPerSecond
FROM (
    SELECT 
        projID, 
        COUNT(*) AS SessionsPerSecond
    FROM MetricsLive
    GROUP BY UNIX_TIMESTAMP(endTime)
) sub