MEDIAN() window function on stable Maria DB

Question

I need to perform a MEDIAN calculation as part of a query. My developer told me this is possible with Maria DB 10.3.3 but my server admin told me they can upgrade only to 10.3.2 since that is the latest stable release. I see 10.3.3 was released on 23 Dec 2017, how is it still just a beta?

Anyway, what will be my best options to use some kind of MEDIAN functionality? Making sub-queries will be both ugly, slow and consume more CPU.

Answer 1

You can use window functions. For a numeric value:

select avg(col)
from (select t.*
             row_number() over (order by col) as seqnum,
             count(*) over () as cnt
      from t
     ) t
where seqnum in ( cnt / 2, (cnt + 1) / 2, (cnt + 2) / 2);

For a string or date, it is harder to use interpolation, so I would simply recommend:

select col
from (select t.*
             row_number() over (order by col) as seqnum,
             count(*) over () as cnt
      from t
     ) t
where seqnum in ( cnt / 2, (cnt + 1) / 2);