思路:将数据正向、反向排序,序号相减。
|1|2|3|4|5|
|5|4|3|2|1|
|1|2|3|4|5|6|
|6|5|4|3|2|1|
以上面的数为例,
位数为单的时候,排序结果相减为0即为中位数。
位数为双的时候,排序结果相减为1,-1的值求均值即为中位数
select user_id,avg(record_seconds) from
(select user_id,
record_seconds,
# 求差值
cast(a as signed)-cast(a as signed) as diff
from
(select user_id,
record_seconds,
# 升序
row_number() over(partition by user_id order by record_seconds)a,
# 降序
row_number() over(partition by user_id order by record_seconds)b
from tb1)p1
)p2
# 差值为-1,0,1 则求均值(位数为单只有0,位数为双则有1,-1)
where diff in(-1,0,1)
group by user_id;