[TIL] Null값과 연산하기 - COALESCE()활용 #SQL #SQLite #solvesql

solvesql - 폐쇄할 따릉이 정류소 찾기 2
👉 문제 바로가기(solvesql)

-- 2018년 데이터 테이블 생성
WITH t_2018 AS(
SELECT
  t1.station_id, t1.name, t1.local,
  CASE
    WHEN (COALESCE(t2.rent_count, 0) + COALESCE(t3.return_count, 0)) IS NULL THEN 0
    ELSE (COALESCE(t2.rent_count, 0) + COALESCE(t3.return_count, 0)) 
  END as 'count_18'
FROM
  station t1
LEFT OUTER JOIN
  (SELECT rent_station_id, COUNT(*) as rent_count
  FROM rental_history
  WHERE rent_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
  GROUP BY rent_station_id) t2 ON t1.station_id = t2.rent_station_id
LEFT OUTER JOIN
  (SELECT return_station_id, COUNT(*) as return_count
  FROM rental_history
  WHERE return_at BETWEEN '2018-10-01 00:00:00' AND '2018-10-31 23:59:59'
  GROUP BY return_station_id) t3 ON t1.station_id = t3.return_station_id
GROUP BY
  t1.station_id),


-- 2019년 데이터 테이블 생성
t_2019 AS(
SELECT
  t1.station_id, t1.name, t1.local,
  CASE
    WHEN (COALESCE(t2.rent_count, 0) + COALESCE(t3.return_count, 0)) IS NULL THEN 0
    ELSE (COALESCE(t2.rent_count, 0) + COALESCE(t3.return_count, 0)) 
  END as 'count_19'
FROM
  station t1
LEFT OUTER JOIN
  (SELECT rent_station_id, COUNT(*) as rent_count
  FROM rental_history
  WHERE rent_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
  GROUP BY rent_station_id) t2 ON t1.station_id = t2.rent_station_id
LEFT OUTER JOIN
  (SELECT return_station_id, COUNT(*) as return_count
  FROM rental_history
  WHERE return_at BETWEEN '2019-10-01 00:00:00' AND '2019-10-31 23:59:59'
  GROUP BY return_station_id) t3 ON t1.station_id = t3.return_station_id
GROUP BY
  t1.station_id)


-- 최종 데이터 조회 쿼리
SELECT
  t1.station_id,
  t1.name,
  t1.local,
  ROUND(((t2.count_19+0.00)/t1.count_18)*100,2) as usage_pct
FROM
 t_2018 t1,
 t_2019 t2
WHERE
  t1.station_id = t2.station_id AND
  t1.count_18 != 0 AND 
  t2.count_19 != 0 AND
  ((t2.count_19+0.00)/t1.count_18) <= 0.5

---

[ 논리 과정 ]

1. 2018년 10월 정류소정보/반납 및 대여 총합 임시 테이블 생성
2. 2019년 10월 정류소정보/반납 및 대여 총합 임시 테이블 생성
3. 최종 쿼리에서 생성한 두 임시 테이블 JOIN하여 최종 결과 조회

[ 문제 상황 ]

• 오답 부분 코드

-- 임시 테이블 생성 시 따릉이 대여 수 + 반납 수 산출
  CASE
    WHEN (t2.rent_count + t3.return_count) IS NULL THEN 0
    ELSE (t2.rent_count + t3.return_count)
  END as 'count_18'

• 임시 테이블 생성 시 두 컬럼값을 더하는 과정에서 NULL 값 처리에 오류 발생
• 의도 : 대여와 반납 수의 총합(t2.rent_count + t3.return_count)이 NULL 이면 0 반환
• 실제 결과 : 대여와 반납 수를 더하는 과정에서 어느 하나라도 NULL 이면 더한 값도 NULL이 되어버려 데이터 오류 발생

[ 해결 ]

• 대여 수와 반납 수를 더하기 전에 COALESCE() 함수를 대여수/반납수 컬럼값에 사용하여 각 대여수 혹은 반납수가 NULL이면 0으로 먼저 바꾼다.
• 이후 더한 값이 NULL이면 0으로 반환하는 CASE WHEN THEN 구문 활용

[ 회고 ]

• NULL과의 연산이 데이터에 왜곡, 오류를 가져올 수 있다는 사실을 크게 느꼈다.
• 각 수를 더한 값이 NULL인지 확인하는 CASE WHEN THEN 구문 대신 각 컬럼값을 더한 값 전체에다가도 COALESCE()함수를 활용하면 더 간단하게 쿼리를 작성할 수 있었다.