SQL 코드카타_03

SQL 코드카타_Weather Observation Station 20

Weather Observation Station 20

문제 : Weather Observation Station 20

# 목적, 목표 : 북위도 ( LAT_N ) 의 중앙값, 소수점 4자리에서 반올림 적용
# 필요한 컬럼 : LAT_N
# 컬럼 조건 : ROW_NUMBER, COUNT, AVG
# 사용할 테이블 : STATION
# join key : 없음
# 테이블 조건 : ROW_NUMBER = 중앙
# 필요한 그룹 : 없음
# 필요한 그룹 조건 : 없음
# 정렬 기준 : 없음
# 주의사항 : 중앙값 = 데이터 개수가 홀수일 때, 짝수일 때 구하는 방법이 다름

• 홀수일 때, 데이터 정렬해서 n/2 위치에 있는 값
• 짝수일 때, 데이터 정렬해서 n/2, n/2+1 위치에 있는 값들의 평균

내가 짠 쿼리

WITH A AS (  # ROW_NUMBER 매기기
SELECT LAT_N, ROW_NUMBER() OVER(order by LAT_N) AS ROW_NUMBER
FROM STATION),
B AS (  # 행 개수 구하기
SELECT COUNT(*) AS cnt
FROM STATION),
C AS (  # 홀수일 때
SELECT ROUND(LAT_N, 4) as ans1
FROM A
WHERE ROW_NUMBER = ROUND((SELECT cnt FROM B)/2, 0)),
D AS (  # 짝수일 때
SELECT ROUND(AVG(LAT_N), 4) as ans2
FROM A
WHERE ROW_NUMBER in ((SELECT cnt FROM B)/2, (SELECT cnt FROM B)/2 + 1)

SELECT CASE WHEN (SELECT cnt FROM B) %2 != 0 THEN (SELECT ans1 FROM C)  # 홀수면 C에서 가져옴
        ELSE (SELECT ans2 FROM D) END AS answer  # 짝수면 D에서 가져옴
FROM STATION

[ 문제 ]

• 코드가 너무 길어짐

[어려웠던 점]

• 홀수일 때, 짝수일 때 중앙값 계산 방법이 달라서 로직 구상에 어려움이 있었음
• with 구문으로 각각의 테이블을 생성한 후, case when 구문으로 홀수일 때, 짝수일 때의 조건문으로 로직을 구성함

[Chat GPT 쿼리 로직 ]

• ROW_NUMBER 매기기
• 행 개수 구하기
• 중앙값 구하기 및 소수점 4자리에서 반올림 적용
  • AVG, FLOOR, CEIL 활용
    • 홀수일 때 : FLOOR((7+1)/2) = 4, CEIL((7+1)/2) = 4
      • 중앙값 = 4번째 위치 값의 평균
    • 짝수일 때 : FLOOR((6+1)/2) = 3, CEIL((6+1)/2) = 4
      • 중앙값 = 3, 4번째 값의 평균

WITH A AS (
SELECT LAT_N, ROW_NUMBER() OVER (ORDER BY LAT_N) AS rn
FROM STATION),
B AS (
SELECT COUNT(*) AS cnt
FROM STATION)

SELECT ROUND(AVG(LAT_N), 4) AS median
FROM A, B
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2));

[코드 뜯어보기]

# A 테이블 : ROW_NUMBER 매기기
WITH A AS (
SELECT LAT_N, ROW_NUMBER() OVER (ORDER BY LAT_N) AS rn
FROM STATION),

# B 테이블 : 행 개수 구하기
B AS (
SELECT COUNT(*) AS cnt
FROM STATION)

# 중앙값 구하기 및 소수점 4자리에서 반올림
SELECT ROUND(AVG(LAT_N), 4) AS median
FROM A, B
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2));

[새로 알게된 점]

• FLOOR와 CEIL을 활용하여 같은 실수값에 대해 +1의 범위를 생성할 수 있음을 알게 됨
  • FLOOR : 주어진 숫자보다 작거나 같은 가장 큰 정수를 반환
    • FLOOR(3.1) = 3
  • CEIL : 주어진 숫자보다 크거나 같은 가장 작은 정수를 반환
    • CEIL(3.1) = 4