[SQL] SQL로 추출한 데이터를 검수하는 과정

이번 포스팅에서는 SQL로 원하는 데이터를 추출했을 때 이 데이터가 제대로 추출된 데이터인가? 를 확인하는 과정을 담아보려 합니다.

 

---

0. 들어가며

데이터 분석가들이 업무를 함에 있어 가장 기본적으로 갖추어야 하는 부분은 데이터 추출이 아닐까라는 생각을 하는데요.

쿼리를 작성하면서 정확한 데이터를 추출하려 노력하겠지만 사람인지라 실수를 하는 경우도 당연히 발생합니다.

그런데 참 어려운 것이 추출한 데이터의 Row수가 너무 많다 보니 이것을 눈으로 보면서 제대로 뽑혔는지를 확인할 수 없고,

그렇다 보니 잘못되었지만 추출된 데이터를 가지고 분석을 할 때 까지도 잘못된 지 모르고 분석을 하다가 되돌아가는 경우가 생깁니다.

(최악의 경우에는 분석결과를 다 만들어 놓고도 모르는 경우가 있을 수 있지 않을까요?)

 

저렇게 확인이 어렵다는 부분은 분석가들에게 두려움을 주는 요소이기도 한데요.

저의 경우에는 첫 회사에서 데이터를 추출하고 가공하여 판매를 하는 데이터 비즈니스를 했었기 때문에 무엇보다 데이터를 정확히 추출하는 것이 중요했습니다.

만약 데이터가 이상하다면 데이터를 구매한 회사에서 컴플레인 전화가 오거나 설명을 요구하는 경우가 발생했죠.

 

이 부분이 저에겐 입사 초반에 꽤나 압박감으로 다가왔었는데요.

그러한 압박감을 해소하기 위해서 값을 검수하는 방법에 대해 고민했던 부분들을 작성해보려 합니다.

쿼리 고수분들께는 당연한 내용일 수 있기 때문에 SQL 추출이 익숙하지 않고 아직까지 두려움을 가지고 있는 분들이 읽어주시면 좋을 것 같습니다!

 

---

 

1. 데이터를 검수하는 방법

제가 업무를 하면서 주로 사용하는 데이터 검수 방법에 대해서 이야기해보겠습니다.

데이터를 추출하기 전에 당연히 테이블 내 컬럼 정의를 확인하고, 지표의 계산식을 정확히 이해하는 것은 당연하기 때문에 여기서는 다루지 않을 예정이고요.

이번 글에서는 SQL 쿼리 작성 과정에서 활용할 수 있는 방법들만 작성해 보겠습니다.

당연히 이 방법이 정답은 아니기 때문에 더 좋은 방법이 있다면 댓글로 남겨주시면 감사하겠습니다 : )

 

1.1. 값이 과대집계/과소집계 된 것 같을 때

SQL을 활용해서 데이터를 추출하고 나면 가끔 값이 이상할 만큼 크거나 작아지는 경우가 발생합니다.

그럴 때에는 아래와 같은 부분을 확인해 보는 것이 좋은데요.

 

* JOIN 한 부분을 의심하기

값이 과대/과소집계 되었을 때 가장 먼저 확인해 볼 부분은 JOIN을 했던 부분들입니다.

SQL을 사용하다 보면 JOIN을 정말 많이 사용하게 되는데요.

이 JOIN을 잘못하게 되면 데이터의 행(=Row)들이 복제되어 늘어나거나 줄어드는 상황이 발생합니다.

그리고 이 이유들은 보통 JOIN 과정에서 Key 값에 문제가 있는 경우가 대부분인데요. (경험상 7~80% 정도?)

JOIN 시 Key 값 문제
* Key 값이 엉뚱하게 설정된 경우
   - INNER JOIN 시 붙는 값이 없어서 행이 사라짐
   - OUTER JOIN 시 붙는 값이 없어서 Null 값이 붙음
   => 이러한 상황에서 집계를 하게 되면 값이 과소집계 됨

* Key 값에 중복이 있는 경우 (= Unique 하지 않은 경우)
   - INNER JOIN 이든 OUTER JOIN 이든 중복된 Key 값의 행(=Row) 수만큼 행이 복제됨 (= Row가 뻥튀기됨)
   => 이러한 상황에서 집계를 하게 되면 값이 과대집계 됨

그래서 저의 경우엔 이 부분을 해결하기 위해서 아래의 방법들을 사용하는데요.

 

(1) 조인을 하기 전 key 값으로 잡을 컬럼들이 유니크한지 살펴보기

위에서 이야기했듯 Key 값이 제대로 설정되지 않아서 문제가 발생하는데요.

보통 값이 과대집계되는 경우에는 Key 값으로 설정한 컬럼들이 Unique 하지 않아서 발생하는 경우가 많습니다.

그렇기 때문에 아래와 같은 쿼리를 활용해서 JOIN 하기 전에 key 값이 유니크한지 확인해 줍니다. 

SELECT 'Key 값으로 잡을 컬럼-1'
	, 'Key 값으로 잡을 컬럼-2'
    , ...
    , 'Key 값으로 잡을 컬럼-N' 
    , COUNT(1) as n

FROM '조인할 테이블'

GROUP BY 1,2,...,N
ORDER BY n DESC

 

그리고 조인을 한 뒤에는 count() 함수를 이용해서 행수가 늘어나지 않았는지를 확인해 주는 식으로 과대집계의 가능성을 줄여나갑니다.

SELECT COUNT(1) as n

FROM '조인후 결과로 나온 테이블'

위 쿼리의 결과값을 JOIN 하기 전의 테이블들의 Row수와 비교하여 커지진 않았는지, 작아지지 않았는지 등을 확인하며 Key 값의 문제 여부를 확인해갑니다.

 

 

그런데 만약 급하게 쿼리를 짜느라고 또는 확신에 차서 쿼리를 짜고 보니 집계가 과대집계/과소집계 된 것 같을 땐 어떻게 해야할까요?

 

(2) 왔던 JOIN 의 발자취를 되짚어가기

그런 경우라면 왔던 길을 다시 되돌아가야하는데요.

'어떻게 오류를 딱 짚어내지..?'라는 생각에 막막할 수 있지만 차근차근 돌아가보면 어렵지 않게 문제의 원인을 찾아낼 수 있습니다.

보통 저는 의심이 가는 중간 지점 (보통은 JOIN의 지점)부터 쿼리를 실행해보고 '값들이 이 부분에서도 중복이 되었었나?'를 확인해봅니다.

만약 의심가는 지점이 없다면 쿼리 내의 가장 안쪽(=서브쿼리의 가장 안쪽 부분)부터 확인을 해보고,

WITH 절로 묶여있는 쿼리라면 앞쪽 WITH 절부터 차근차근 값들을 확인해봅니다.

 

그렇게 확인을 하다 어느 순간에 값이 중복되거나 사라지는 것을 확인할 수 있고,

그 부분에서 쿼리를 뜯어보면서 문제의 원인을 찾아보는 방식으로 진행합니다.

 

 

 

* WHERE 절 확인하기 

보통 쿼리를 작성하다 보면 하나의 쿼리 안에서 비슷한 형태의 쿼리를 여러번 작성하게 되는 경우가 있는데요.

이 경우에 그 구문을 복사해서 사용하는 경우가 생기고 그 과정에서 넣어야할 부분을 누락하거나, 제외해야 하는 부분을 그대로 넣어두는 경우가 발생합니다.

특히 날짜/시간의 조건을 넣어줄 때 이러한 경우가 많이 발생하는데요.

쿼리가 길어지다 보면 몇 군데 WHERE 절을 수정하지 못한채 쿼리를 실행하게 되는 경우가 발생하게 되죠.

 

이 부분은 또 Key 값에 영향을 미쳐 JOIN 의 문제로 이어질 수 있는데요.

Key 값을 날짜로 잡았을 때 WHERE 절을 수정하지 않은 탓에 제대로 JOIN 이 되지 않고,

INNER JOIN 을 했을 때 값이 통째로 사라지거나, OUTER JOIN 시에는 Null 값만 들어있는 등의 문제가 생깁니다.

 

그래서 이 부분을 해소하기 위해서 보통 아래와 같은 방법을 활용하는데요.

 

(1) WITH 절 활용하기

반복해서 작성해야 하는 구문이 있다면 WITH 절로 묶어서 필요한 조건들을 한 번에 적용시킨 뒤, 그 WITH 절을 활용해서 쿼리 작성하는 방법입니다.

WITH 절에 대해 궁금증이 생기신다면 이 게시글도 함께 보시는 것을 추천드립니다!

[SQL] with 절을 효율적으로 사용하기

조건절을 한 번의 WITH 절에만 적용해놓으면 되기 때문에 조건절 수정 시 실수를 줄일 수 있다는 장점이 있습니다.

 

 

(2) 변수 선언해서 사용하기 

쿼리가 길어지면 고쳐야하는 조건절을 못보고 넘어가는 등의 문제가 발생할 수 있는데요.

저의 경우엔 날짜를 바꾸는 부분을 까먹는 경우가 많았습니다.

그래서 이 부분을 해결하기 위해 변수를 선언하고 그 변수를 WHERE 절에 넣어주는 방법을 사용하는데요.

빅쿼리의 경우 Declare 구문을 이용해서 변수 생성/설정을 해줄 수 있습니다.

 

< 기존의 쿼리 >

-- 기존의 쿼리
SELECT *
FROM `table1`
WHERE stnd_ymd = '2024-03-31'
;

< 매개변수 활용 >

-- 매개변수 활용
DECLARE base_ymd DATE DEFAULT '2024-03-31';

SELECT *
FROM `table1`
WHERE stnd_ymd = base_ymd
;

쿼리가 짧을 땐 크게 효과를 못보지만 쿼리가 아주 길어지다보면 큰 효과를 볼 수 있는 방법이라고 생각합니다.

 

 

1.2. 데이터 추출을 완료했지만 값에 자신이 없을 때

위의 과정을 거치면서 쿼리상으론 값의 문제가 없다는 생각은 강하게 들지만 무언가 불안할 때가 있습니다.

그럴 때에는 아래와 같은 방법을 활용해봅니다.

 

(1) 월별 집계 or 그룹별 집계해보기

최종적으로 추출한 데이터에서 기준을 잡아 집계를 해보는 방식인데요.

여기서 기준이라는 것은 스스로 정하기 나름이지만 저의 경우엔 아래와 같은 기준들을 보통 활용하고 있습니다.

< 값 확인을 위한 집계 기준 >
* 날짜(월별 or 연도별)별 집계
   - 월별로 집계값의 합계 or 평균치를 구해보고 월이 지남에 따라 추세가 크게 변하는 부분이 없는지 확인하기

* 그룹별 집계
   - 각 그룹별로 합계 or 평균치를 구해보고 그룹별로 값들이 상식적인 수준인지 확인하기

위의 방법대로 확인을 하다보면 미처 발견하지 못했던 JOIN 의 문제를 발견할 수 있는데요.

기준에 따라 집계를 해서 확인해보면 특정한 그룹에서 값이 과대집계 되는 케이스를 발견하는 경우가 생기고,

무언가 유사해야할 것 같은 그룹별 집계치가 너무 크게 차이가 난다면 다시 JOIN 부분을 확인하러 가는 식으로 값을 확인할 수 있습니다.

 

 

(2) Null 값이 있는지 확인해보기 

최종 집계된 값들에 Null 값이 있는지 확인해보는 것도 꽤나 편리한 방법입니다.

쿼리를 작성한 뒤 Null 값이 없어야하는데 있다면 어떤 케이스에서 Null 값이 생겼는지를 확인해보고 문제 여부를 살펴보는 것이죠.

이렇게 Null 값을 확인했을 때 Null값이 존재한다면 그 부분을 확인하며 다른 몰랐던 문제들을 살펴볼 수 있기도 합니다.

 

즉, 최소한의 필터링으로 전체적으로 문제가 있을 수 있는 가능성이 있는지를 확인할 수 있는 것이죠.

 

 

---

2. 끝맺으며

이렇게 이번 포스팅에서는 SQL을 활용해서 데이터를 추출할 때 오류를 최소화하는 몇 가지 방법에 대해서 정리를 해봤는데요.

각 부분들이 글로만 설명해서는 와닿지 않을 수 있지만 작업을 하면서 중간중간 이 방법들을 적용해가는 방식으로 이 포스팅을 활용해보면 좋을 것 같습니다.

 

그리고 데이터 추출에 대한 두려움을 느끼고 있다면 위와 같은 방법들을 통해서 정확한 데이터 추출 경험을 쌓아나가보는 것을 추천드립니다!

저도 처음엔 두려웠지만 그 두려움을 안고 계속 추출을 하다보니 작은 성공경험들이 쌓였고, 지금은 추출에 대한 두려움은 없다고 이야기할 수 있을 정도이거든요.

 

아무튼 이번 글이 비슷한 고민을 하고계신 분들께 도움이 되기를 바라며 글을 마칩니다.