MySQL按会话长度分组的中值(小时)

Question

我需要计算MySQL中用户会话长度(ts_last-ts_first(的天数中位数(ts_first(。尝试这样的东西，但没有效果。

谢谢！

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 

Answer 1

为表User_sessions(id，first_ts，last_ts(获取持续时间的中值"last_ts-first_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

这项工作由：

1( 内部选择：按持续时间、为每行User_sessions分配一个增量计数器

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

2( 在末尾@counter=User_sessions的总行数。

3( 外部选择，使用counter<柜台

参考

• 为MySQL中的数值选择TOP X(或底部(百分比