mySQL에서 median 구하기
- Sort the array
- 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
'Data Science > Mysql' 카테고리의 다른 글
[LeetCode-HARD] Department Top Three Salaries 부서의 top3 급여 구하기 (0) | 2022.05.03 |
---|---|
[HackerRank] Challenges 문제 코드 (0) | 2021.03.24 |
[STRATASCRATCH] MySQL Coding Problems 러닝포인트 정리 (0) | 2021.03.18 |
[MySQL] Advanced Level List (0) | 2021.03.14 |
[ Syntax 정리(1) ] LIMIT, OFFSET, Self-Join, JOIN, CAST, LOCATE, POSITION, FUNCTIONS(STRING, DATES, ARRAYS) (0) | 2021.03.04 |