실무에서 데이터를 다루다 보면 필연적으로 결측 데이터를 만나게 됩니다. 핑계 없는 무덤이 없다는 속담이 있듯, 데이터가 결측인 이유도 정말 다양합니다. 특별한 경우에만 값이 있는 경우, 서버 장애로 관측되지 않은 경우, 응답자가 응답을 거부하는 경우, 데이터 구조가 바뀌면서 새로운 컬럼이 추가된 경우 등등 너무 다양하죠? 오늘 포스팅에서는 이와 같은 결측치를 처리하는 데에 자주 사용하는 두 함수 ‘COALESCE, COALESCEC’를 살펴보겠습니다.

1. 함수 구조

함수의 구조와 기능부터 알아보겠습니다. <표 1>은 COALESCE(C) 함수의 기본 구조와 각 함수의 인수 유형과 예시를 나타낸 표입니다.

<표 1> COALESCE, COALESCEC 함수의 구조

먼저 함수를 살펴보면, COALESCE(C) 함수는 여러 인수를 입력으로 받을 수 있으며, 구체적인 처리 절차는 다음과 같습니다.

• ‘인수-1’이 결측 인지 확인한 뒤 결측이 아니면 ‘인수-1’을 반환
• 만약, ‘인수-1’이 결측이면, 다음 ‘인수-1’을 ‘인수-2’로 대체
• ‘인수-2’도 ‘인수-1’에서와 같이 결측 인지 확인
• 만약, ‘인수-2’도 결측이면, 다음 인수로 현재 인수를 대체
• 다음 인수가 결측이 아니거나, 다음 인수가 없을 때까지 이 과정을 반복

<표 1>의 함수를 살펴보면, ‘<…’으로 표현된 부분을 확인할 수 있습니다. 이 표현은 필요에 따라 인수를 추가하거나 뺄 수 있다는 의미입니다. 즉, COALESCE 함수는 인수를 최소한 1개 지정해주면, 오류 없이 결과를 출력합니다. 하지만 결측이 있는 경우, 다른 값으로 대체하는 역할을 하는 함수인데, 값을 하나만 넣는다면 존재 이유가 없겠죠? 그래서 의미 있는 활용을 위해서는 적어도 두 개의 인수를 포함합니다.

다음으로 인수 유형은 COALESCE 함수는 숫자형 변수와 상수를 인수로 입력 받을 수 있습니다. 사용 예시의 ‘NV1’은 제가 임의로 정한 숫자 변수 이름입니다. 그리고 ‘100, 10’은 수치형 상수입니다. 이 구문의 경우 만약 NV1이 결측값을 가진다면, 100으로 값이 대체됩니다. COALESCEC 함수는 COALESCE 함수와 모든 기능이 동일합니다. 다만 인수가 문자형 변수와 상수라는 차이를 가집니다.

2. 예제로 배우는 함수 사용 방법

예제를 통해 COALESCE(C) 함수를 다루는 방법을 보다 자세히 알아보겠습니다. 사용 방법을 익히기 위한 예제 데이터는 다음과 같은 형태입니다.

<데이터 1> 예제 데이터

<데이터 1>의 NV1, NV2, NV3은 숫자 변수이고, ‘.’으로 표시된 부분은 결측입니다. 다음으로 CV1, CV2, CV3은 문자 변수로 결측은 공백으로 표현되어 있습니다. 데이터를 생성하기 위한 코드는 <코드 1>과 같습니다.

<코드 1> 예제 데이터 생성 코드

2.1. 결측 값을 다른 변수로 대체

결측 값을 다른 변수로 대체하는 방법을 알아보겠습니다. 이 기능은 <표 2>와 같이 앞서 등장한 변수 A가 결측인 경우, 결측이 아닌 다음 변수인 B의 값으로 결측 대체합니다.

<표 2> COALESCE 입력과 출력 예시

COALESCE(C) 함수는 SAS 데이터문과 PROC SQL 모두에서 사용할 수 있습니다. 먼저 앞서 살펴본 ‘EXAMPLE’ 데이터의 NV1, NV2, NV3 변수를 순서대로 COALESCE 함수에 투입하여 결과를 살펴보겠습니다.

<예제 1> COALESCE 사용 방법

기본적인 DATA 구문에 대한 설명은 생략하겠습니다. <예제 1>의 코드를 살펴보면, KEEP 문을 통해 NV1, NV2, NV3 변수만을 선택하여, SET으로 가져온 것을 알 수 있습니다. 그리고 OUT_NUM 변수에 COALESCE 함수에 세 변수 NV1, NV2, NV3를 인수로 투입한 결과를 저장합니다. 결과를 살펴보면, OUT_NUM에는 ‘1, 1, 9’라는 값이 순서대로 저장된 것을 알 수 있습니다. 첫 번째 행에서는 NV1이 결측이 아니었기 때문에 OUT_NUM에는 1이 저장된 것입니다. 반면 두 번째 행은 NV1이 결측이기 때문에 NV2의 1이 저장되었습니다. 그렇다면 변수 순서를 바꾸면 결과는 어떻게 바뀔까요?

<예제 2> 변수의 순서를 바꾼 경우의 결과 확인

<예제 2>의 결과를 살펴보면 OUT_NUM이 기존 ‘1, 1, 9’에서 ’2, 1, 9’로 바뀐 걸 알 수 있습니다. 이와 같이 COALESCE(C) 함수는 변수 투입 순서에 따라 결측치를 채우는 우선순위가 달라지고, 결과 또한 바뀔 수 있습니다. 이런 성질을 이용하면, 우선순위가 높은 정보를 먼저 가져오고, 없는 것보다는 있는 게 나은 정보를 다음 순위로 가져오는 것이 가능합니다. 하지만 결측치는 다른 변수의 값으로도 대체하지만, 상수로 대체하는 경우도 많습니다. 이어서 상수로 결측을 대체하는 법을 알아보겠습니다.

2.2. 결측치를 상수로 대체하는 방법

결측치를 처리하는 방법은 앞서 설명한 다른 변수 값으로 대체하는 방법 외에도 특정 상수로 대체하는 방법이 있습니다. 예를 들어 성별 변수는 보통 ‘남, 여’ 두 가지 값을 가집니다. 근데, 간혹 고객이 성별을 넣지 않아 결측인 경우도 있습니다. 이 경우, 결측을 그대로 두면 해당 관측치는 학습에 활용되지 않을 수 있습니다. 때문에 이런 문제를 미연에 막기 위해, ‘모름, 미입력’ 등의 상수로 결측을 대체하기도 합니다. 이번에는 상수 값으로 결측을 대체하는 방법을 알아보겠습니다.

<예제 3> COALESCE 사용 방법

<예제 3>의 코드를 먼저 살펴보면, 문자형 변수 CV1, CV2, CV3를 이용하기 때문에 COALESCEC 함수를 사용한 것을 알 수 있습니다. 또한 상수 ‘미입력’을 함수의 두 번째 인수로 넣어, CV1이 결측인 경우, ‘미입력’으로 값을 채우게 하였습니다. 상수를 인수로 넣으면, 상수 다음에 투입된 인수(이 경우 CV3)는 결과에 영향을 주지 않습니다. 상수가 결측인 경우는 없기 때문입니다. 결과를 살펴보면, OUT_NUM의 첫 번째 행은 CV1이 ‘A’라는 값을 가지기 때문에 ‘A’가 반환된 것을 알 수 있습니다. 반면, 2~3번째 행은 CV1이 모두 결측이기 때문에 앞서 지정한 상수 ‘미입력’이 값을 대체한 것을 알 수 있습니다.

3. 어떻게 활용할까?

앞서 살펴본 예제를 통해 함수 사용 방법을 익혔습니다. 그렇다면, 이 함수는 어떤 경우에 유용하게 쓰일 수 있을까요? 이번에는 활용 사례에 대해 알아보겠습니다.

3.1. 결측치 처리

COALESCE(C) 함수는 포스팅 제목으로 알 수 있듯, ‘결측치 대체’에 자주 활용합니다. 이 함수는 결측치를 상수(어떤 값) 또는 다른 변수로 대체할 때 유용합니다. 보통 수치형 변수는 평균이나 중위수를 많이 사용하고, 문자형 변수는 ‘결측, 값 없음’ 등과 같이 결측치를 명시적으로 나타내는 값을 이용합니다.

3.2. FULL 조인을 이용하는 경우

FULL 조인은 조인하는 테이블의 행의 개수가 다를 수 있습니다. 예를 들어 A, B 두 테이블을 FULL 조인한다면, A 속하는 관측치가 B에 없을 수 있고, B에 속하는 관측치가 A에 없을 수 있습니다. 또한 둘 다 있을 수도 있습니다. 예제를 통해, FULL 조인에서 활용하는 방법을 한 번 살펴보겠습니다.

<예제 4> FULL 조인에서의 COALESCE의 유용성

<예제 4>의 A 테이블은 ID가 ‘1, 3, 5, 8’이고, B 테이블은 ‘1, 4, 5, 10’입니다. ID가 ‘3, 8’인 관측치는 A에만 있고, ‘4, 10’번 관측치는 B에만 있습니다. 그 결과 ID를 A의 ID로만하면, ‘4, 10’이 누락되고, B로 설정하면 ‘3, 8’이 누락됩니다. 이 경우 아래 <코드>와 같이 COALESCE 함수를 사용하면, KEY 값을 쉽게 통합할 수 있습니다. 이 방법은 표준 SQL에서도 유효합니다.

<코드> FULL JOIN에 COALESCE 활용하기

<코드>의 VALUE에서 사용한 COALESCE는 결측을 0으로 대체한 뒤 더해주는 방법입니다. 두 변수를 더하는 경우, 한 변수가 결측이면 그 결과도 결측 처리됩니다. 따라서 <코드>와 같이 결측을 0으로 대체한 뒤 더해주는 것이 조인에서는 안정적인 방법입니다.

3.3. 최신 정보를 우선 반영하기

COALESCE 함수의 마지막 세 번째 활용 방법은 최신 정보에 우선 반영하는 것입니다. 예를 들어 처음 가입할 때 사용한 주소나 전화번호는 가입 기간이 길어지면 변경되었을 가능성이 높습니다. 고객이 이사를 갔을 수도 있고, 전화번호를 바꿨을 수도 있기 때문입니다. 하지만 최근 ‘집’이라고 입력한 배송지의 주소는 비교적 정확할 것입니다. 전화번도 같은 원리로 최근 주문에서 사용한 번호가 더 정확합니다. 하지만 최근에 주문이 없을 수도 있고, 극단적으로 가입만 하고 주문이 없는 경우도 있습니다. 이 경우 아쉬운 대로 가입할 때 사용한 정보라도 사용하는 것이 결측을 줄이는 방법입니다. 이런 상황에서 COALESCE 함수는 다음과 같이 사용할 수 있습니다.

4. 정리

오늘 포스팅에서는 COALESCE(C) 함수의 구조와 사용 방법 등을 알아보았습니다. COALESCE(C) 함수는 인수 유형에 따라 COALESCE(유형: 수치)와 COALESCEC(유형: 문자) 두 가지 유형의 함수로 나뉘었습니다. 이 함수는 입력된 인수의 순서대로 결측이 있는지 확인하고, 없다면 해당 변수 값을 반환합니다. 그 결과 실무에서 결측치 처리, FULL 조인에서 조인키 결합, 최신 정보를 우선 반영하도록 하고 싶은 경우에 주로 사용했습니다. 이 외에도 COALESCE(C) 함수는 다양한 창의적인 방법으로 응용할 수 있을 것이라 짐작됩니다. 감사합니다.