SQL로 빈칸 채우기_COALESCE 함수의 활용

데이터를 이용하기 위해서는 기본적으로 데이터에 대한 전처리가 필수적이다. 데이터 이용의 대표격인 머신러닝 등이 있을텐데, 데이터 전처리(Data Preprocessing)는 앞서 설명한 ML 알고리즘만큼 중요하다는 것이다. 왜냐하면 데이터 이용 알고리즘은 데이터에 기반한 경우가 많은데, 어떤 데이터 입력으로 가지느냐에 따라 결과가 크게 달라질 수 있기 때문이다. 흔히들 얘기하는 Garbage In, Garbage Out이 되지 않기 위해서라도 데이터의 전처리에 대해 중요성은 더 말할 필요는 없을 것이다.

 

전처리에서 우리가 처리해야 할 형태는 매우 다양하지만, 여기서 살펴볼 내용은 빈칸이 발생한 경우에 대해 어떻게 처리할 것인가에 대한 것을 살펴보고자 한다. 빈칸을 그대로 두게 되면, 평균이나 분산 등 여러 통계치와 다른 알고리즘 적용에 그다지 좋지 않은 결과를 낼 수 있다. (물론, 빈칸이 전체 데이터 개수 대비 매우 적다면 무시하는 방법도 고려할 수 있겠지만)

 

Photo by Neven Krcmarek on Unsplash

예시를 들어가기에 앞서, COALESCE라는 함수에 대해 알아보고자 한다. 한국에서만 영어를 배웠던 나에게는 그리 친숙하지 않은 단어인데, 영어 단어상으로 '더 큰 덩어리로 합치다, 연합하다' 이런 뜻을 가진 단어라고 한다. 

 

(그래서 나는 여러 가지 NULL에 대해

 

사용방법: COALESCE(p1, p2 , .... pN) 

결론적으로 이야기하자면, Null이 나올 때 대입할 값만 pN으로 지정해주고, 나머지 값들은 그대로 간다는 뜻이다. 익숙한 CASE WHEN 구문하고 유사하다고 할 수 있는데, 이걸 CASE WHEN으로 바꿔보면 다음과 같다.

 

CASE
WHEN (p1 IS NOT NULL) THEN p1
WHEN (p2 IS NOT NULL) THEN p2
...
ELSE pN
END

우리에게 익숙한 CASE WHEN으로 처리하는 것이 평소엔 편하겠지만, 코드가 조금만 길어지거나 복잡해진다면 COALLESCE 함수를 이용해 단순화하는 것도 좋은 방법이 될 것이다.

---

그렇다면 간단한 예시를 살펴보고, 코드를 통해 학습한 다음 나중에 적용해보자. 상황은 다음과 같다. 

 

2004년에 덴마크(DEN), 노르웨이(NOR), 스웨덴(SWE) 3국이 금메달을 딴 개수를 Country와 Gender 필드에 대해 정렬해서 나타내고자 한다. 거기서 빈칸에 대해서는 Country에 대해서는 'All countries'로, Gender에 대해서는 'All genders'로 처리하도록 한다.

 

SELECT
  -- Replace the nulls in the columns with meaningful text
  COALESCE(Country, 'All countries') AS Country,
  COALESCE(Gender, 'All genders') AS Gender,
  COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2004
  AND Medal = 'Gold'
  AND Country IN ('DEN', 'NOR', 'SWE')
GROUP BY ROLLUP(Country, Gender)
ORDER BY Country ASC, Gender ASC;

위 코드에서 볼 수 있다시피, COALSESCE를 이용해 공백에 대해 처리가 가능함을 확인할 수 있다. Country 필드는 값 그대로 가되, 맨 마지막에 위치한(사실은 2번째지만) 'All countries'를 공백에 채워넣는 것으로 이해할 수 있다. 동일하게, Gender 필드도 처리되는 것이라 생각할 수 있을 것이다.

 

---

이 글의 주된 내용은 아니지만, GROUP BY ROLLUP()의 경우 안에 들어가 있는 필드에 대해 그룹으로 묶어주는데, ROLLUP을 통해 부분합을 제공할 수 있다.

 

 

마이크로소프트에서 제공하는 참조 문서이니 COALESCE 함수와 ROLLUP에 대해 적극적으로 사용하고 싶은 분들은 아래 링크를 통해 참고하도록 하자.

 

https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15

COALESCE (Transact-SQL) - SQL Server

 

https://docs.microsoft.com/ko-kr/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

GROUP BY(Transact-SQL) - SQL Server