[SQL#170] SQL에서 중앙값 계산하기 (SET)

Weather Observation Station 20

TIL (Today I Learned)

문제 이해

• lat_n의 중앙값 (Median)을 구하는 문제입니다.

논리 구현

1. 인덱스 생성:

   • SET 키워드를 통해 사용자 변수 @rowindex를 초기화하고, SELECT 문에서 각 행에 인덱스를 부여합니다.
   • 중앙값을 찾기 위해 정렬된 lat_n 값에 대한 인덱싱을 서브쿼리를 통해 수행합니다.

2. 중앙값 조건 설정:

   • 인덱스를 통해 데이터의 중간 위치를 찾습니다. 이때 데이터의 길이가 짝수인 경우, 두 개의 중앙값의 평균을 구해야 하므로 FLOOR()와 CEIL() 값을 계산하여 각각 WHERE 조건으로 설정합니다.
   • 홀수의 경우에도 FLOOR()와 CEIL()의 결과는 같으므로, 평균을 구해도 동일한 값이 반환됩니다.

3. 중앙값 계산:

   • 중앙에 위치한 두 개의 lat_n 값을 선택하고, 그 값들의 평균을 구해 중앙값을 계산합니다.

정답 코드

SET @rowindex := -1;

SELECT
    ROUND(AVG(new_table.lat_n), 4) AS Median
FROM
    (SELECT 
        @rowindex := @rowindex + 1 AS rowindex,
        lat_n
    FROM station
    ORDER BY lat_n) new_table 
WHERE 
    new_table.rowindex IN (FLOOR(@rowindex / 2), CEIL(@rowindex / 2));

정답 코드에 등장하는 모든 개념 정리

1. SET 키워드

• 사용자 변수 초기화: SET @rowindex := -1을 사용하여 사용자 변수 @rowindex를 초기화합니다. 이 변수는 각 행에 대한 인덱스를 부여하기 위해 사용됩니다.
• 사용자 변수: @로 시작하는 변수를 사용자 변수라고 하며, 쿼리의 세션 내에서 임시적으로 값을 저장하고 계산하는 데 사용됩니다.

2. @rowindex := @rowindex + 1

• 값 할당 및 증가:
  • @rowindex를 증가시키는 구문입니다.
  • @rowindex는 초기값이 -1이므로, 첫 번째 행에서 0이 되고 이후에는 계속 1씩 증가합니다. 이렇게 각 행마다 인덱스를 부여하는 역할을 합니다.

3. AS rowindex

• 별칭 지정:
  • 서브쿼리에서 계산된 @rowindex 값을 rowindex라는 별칭으로 사용합니다.
  • SELECT 문에서 생성된 값에 대한 별칭을 부여하여 외부 쿼리에서 참조할 수 있게 합니다.

4. ORDER BY lat_n

• 정렬:
  • lat_n 값을 기준으로 오름차순으로 정렬합니다.
  • 중앙값을 찾기 위해서는 반드시 값들을 정렬된 상태로 만들어야 하므로, ORDER BY가 중요합니다.

5. AVG(new_table.lat_n)

• 평균 계산:
  • 두 개의 중앙값의 평균을 구하기 위해 사용됩니다.
  • AVG() 함수는 주어진 값들의 평균을 계산하며, 이 경우 중앙에 위치한 두 값의 평균을 구해 중앙값(Median)으로 반환합니다.

6. ROUND(..., 4)

• 반올림:
  • ROUND(AVG(new_table.lat_n), 4)를 사용하여 평균값을 소수점 4자리까지 반올림합니다.
  • 중앙값을 소수점 이하 4자리까지 표현하여 결과의 정밀도를 유지합니다.

7. FLOOR(@rowindex / 2)와 CEIL(@rowindex / 2)

• 중앙값의 인덱스 계산:
  • FLOOR(): 소수점을 내림하여 가장 가까운 작은 정수를 반환합니다. 데이터 길이가 짝수일 때 하위 중앙값의 인덱스를 구하는 데 사용됩니다.
  • CEIL(): 소수점을 올림하여 가장 가까운 큰 정수를 반환합니다. 데이터 길이가 짝수일 때 상위 중앙값의 인덱스를 구하는 데 사용됩니다.
  • 이렇게 FLOOR()와 CEIL()을 함께 사용하면 짝수 개수의 데이터에서 두 개의 중앙값을 찾을 수 있습니다. 홀수 개수의 데이터에서는 두 값이 같아지므로, 동일한 인덱스를 선택하게 됩니다.

8. WHERE new_table.rowindex IN (FLOOR(@rowindex / 2), CEIL(@rowindex / 2))

• 조건 필터링:
  • 서브쿼리에서 각 행에 부여된 rowindex 값이 중앙 인덱스(FLOOR(@rowindex / 2)와 CEIL(@rowindex / 2))에 해당하는 행을 선택합니다.
  • 짝수 개의 데이터인 경우, 두 인덱스에 해당하는 값들을 가져오고, 홀수 개의 경우 동일한 인덱스를 두 번 참조하게 되어 그 값 하나만 반환합니다.

요약

• SET과 사용자 변수를 이용해 인덱스를 부여하고, 이를 기반으로 중앙값의 인덱스를 찾는 방식입니다.
• 서브쿼리를 통해 각 행에 인덱스를 부여하고, 중앙값을 찾기 위한 필터링을 통해 데이터를 선택합니다.
• 중앙값은 AVG()와 ROUND()를 사용해 계산되며, 짝수/홀수 경우 모두에 대응하도록 설계되었습니다.