[HACKERRANK] median 구하기

mySQL에서 median 구하기

  1. Sort the array
  2. Find the value of the middle item in the array.

WITH

SET the variable

rowindex

FLOOR

CEIL

SET @rowindex := -1 ;    
WITH CTE AS (                                                        

            SELECT  
                @rowindex := @rowindex+1 AS rowindex
                , LAT_N 
            FROM STATION
            # 1. SORT THE ARRAY
            ORDER BY LAT_N                                 
)

SELECT ROUND(AVG(LAT_N),4) as median
FROM CTE
# 2. Find the value of the middle item in the array
WHERE CTE.rowindex IN (Floor(@rowindex/2), CEIL(@rowindex/2));      

-- the length of array(rowindex) is even >> middle two indicies are actually equal
-- the length of array(rowindex) is odd >> average the two middle indices