MySQL Median value by session length by group (hours)

Question

I need to calculate the median of user session length (ts_last-ts_first) by days (ts_first) in MySQL. Trying something like this but it does not work.

Thanks!

SET @row_number:=0;
SET @medin_group:=’’;

SELECT @row_number:=CASE 
WHEN @median_group= FROM_UNIXTIME(first_ts, '%t') THEN @row_number+1
ELSE 1
END AS count_by_time,
@median_group:= FROM_UNIXTIME(first_ts, '%t') AS median_group,
FROM_UNIXTIME(first_ts, '%t') AS by_time,
AVG(last_ts-first_ts) AS length,
(SELECT 
              COUNT (*)
         FROM 
                  User_sessions
           WHERE 
                      a.by_time=by_time) AS total_by_time
FROM 
   (SELECT   FROM_UNIXTIME(first_ts, '%t') AS by_time, AVG(last_ts-first_ts) AS length
FROM 
     User_sessions
  ORDERS BY by_time, length) AS

What version of MySQL are you using? Variables are deprecated in the most recent version. — Gordon Linoff, Mar 20 '20 at 15:51

Jannes Botis · Answer 1 · 2020-03-20T23:42:32.220

To get the Median value of duration, "last_ts - first_ts", for table User_sessions(id, first_ts, last_ts):

Select MAX(totalSessions.duration) AS median
from
(
    SELECT User_sessions.last_ts - User_sessions.first_ts AS duration, @counter := @counter +1 AS counter
    FROM (select @counter:=0) initvar, User_sessions
    ORDER BY duration ASC
) totalSessions
where (50/100 * @counter) > counter

This works by:

1) Inner select: Assigning a incremental counter for each row of User_sessions ordered by duration,

lowest duration counter=1
2nd lowest counter=2
3rd lowest counter=3

2) At the end @counter = total rows of User_sessions.

3) An outer select, filters in the rows with counter < @counter.

References

Select TOP X (or bottom) percent for numeric values in MySQL

MySQL Median value by session length by group (hours)

1 Answers1