HackerRank SQL(Intermediate, Medium) 解法 | Wheather Observation Station 20

問題

www.hackerrank.com

解法(MySQL)

  • そこまで複雑なクエリではないが,中央値の計算はよくやると思うので自分用のメモとして残す.
with
sorted_lat as (
    select
        lat_n,
        @i := @i+1 as r
    from
        station,
        (select @i := 0) as tmp_var
    order by
        lat_n asc
),
number_of_data as (select max(r) as num from sorted_lat)

select
    round(avg(lat_n), 4) as median
from
    sorted_lat
where
    r = (select num+1 from number_of_data) div 2
    or r = ((select num from number_of_data) div 2)+1
;