데이터 분석을 위한 SQL 쿼리 - 1. 데이터 정합성 검사

데이터 분석을 위한 SQL 쿼리 <1. 데이터 정합성 검사> 

 

'SQL을 가장 많이 사용할때?!' 

feat. 저의 주관적 생각이 많이 담겨있습니다!

안녕하세요 ALEX 입니다! 예전부터 지금까지 'SQL을 어떤용도로 가장 많이 사용할까?' 생각해봤습니다.

SQL은 단순히 전처리용이다?

SQL의 용도를 '전처리' 로만 생각하시는 분들이 많으실 것 같은데. 그보다도 더 중요하고, 많이 쓰이는 순간이 있습니다. (아주 단순하지만 중요한 절차!) 

만들어진 데이터도 다시보자. 확인하는 습관을 들이자. SQL과 함께

제가 생각하기에는 보여주려고 하는 데이터 값의 결과가 정확한가? 내가 생각하는 논리에 맞는가? 이상치는 없는가?를 기본적으로 빠르게 확인하는 과정에서 꼭 필요하고 가장 많이 쓰입니다.  보통 항상 저는 Data 결과를 다시한번 확인하는 과정을 가졌는데, 이 과정을 SQL을 통해서 진행해야 가장 정확하고 빠른 답을 얻을 수 있습니다. 

만들어진 데이터가 내가 표현하려고 하는 값이 정확히 들어갔는지, 의심하고 한번더 확인한다.

프로젝트를 진행하다보니 '무작정 데이터를 의심'하는 태도를 가지게 되는데, 전처리 도중 실수하거나 생각하지 못했던 오류를 찾아내기도하며 데이터 매핑하는 과정에서 데이터가 누락되거나 뻥튀기(?) 되는 경우를 찾아내기도 합니다. 때문에 SQL을 통해 데이터를 뽑아쓴다. 형변환하고 분석하기 좋은 MART를 만들어낸다.와 같은 본래의 용도보다는, 완벽한 분석결과(오차를 최대한 없애는)를 만들어내기 위한 확인 작업으로 더 많이 쓰였던 것 같습니다. 

---

데이터 Quality 분석만 진행해도 프로세스 오류를 어느정도는 알게된다. 

또한 프로젝트를 본격적으로 시작하기에 앞서서도 데이터 정합성 분석은 꼭 필요합니다! 지금 적재되고 있는 데이터의 형태와 결측치, NULL 값만 확인한다고 하더라도 데이터 수집 프로세스의 문제점들(이를테면 멤버십 가입시 회원정보의 정합성 문제, 중복 데이터 존재)이 보이곤 합니다! 저는 이러한 과정을 분석 자체를 더 튼튼하고 완벽하게 만들어낼 수 있는 과정을 초기에 구축하는 것이라 생각합니다. 

Data quality report

위의 Data Quality 분석 결과를 살펴보면 중복된 핸드폰 번호가 아주 많죠? 회원가입 절차에서 핸드폰 번호 본인확인 절차가 생략되어 있었기 때문에 저런 결과가 발생하는 것을 살펴볼 수 있습니다. 저렇게 계속 회원정보가 누락된다면 기업 입장에서는 고객과의 Contact point를 제대로 알기 힘들기 때문에 문제를 확인하고 프로세스 자체를 수정하는 경우가 많습니다. 개개인의 회원정보를 정확히 가지고 있는 것도 확실히 기업의 큰 자산이기 때문입니다. 

 

---

1. 데이터의 양(Quantity)을 측정 < COUNT 함수 >

SELECT  COUNT(MEM_NO)
		, COUNT(EMAIL) EM
		, COUNT(MEM_NM) NM
		, COUNT(BIRTH) BIRTH
		, COUNT(MOBL) MOBL 
		, COUNT(GD) GD
		, COUNT(ADDR) ADDR
		, COUNT(PRE_PLC) PRE_PLC
		, COUNT(MARRYSTATUS) MARRYSTATUS
		, COUNT(LIVESTATUS) LIVESTATUS
		, COUNT(LIVEWITH) LIVEWITH
		, COUNT(YOUNGEST_CHILD_AGE) YOUNGEST_CHILD_AGE
		, COUNT(INTEREST) INTEREST
  FROM  MEM_TEMP
 WHERE  JOIN_PLC = '고양'

가장 처음 입력된 데이터 양을 측정하는 방법입니다. COUNT 함수는 ROW수를 세어주지만 NULL를 생략합니다. NULL 값, 즉 누락되어 있는 데이터를 살펴볼 수 있는 방법입니다. 위의 경우 MEM_NO와 같은 유일한 COLUMN (이런 경우 한계정당 MEM_NO는 무조건 하나라고 생각하시면 됩니다.)의 양보다 적게 데이터가 입력되어 있다면 누락된 데이터의 양을 확인할 수 있습니다. 즉, 어떤 컬럼에 얼마나 데이터가 비어있고 이 데이터가 중요한 데이터인지 아닌지 확인하는 과정으로 볼 수 있습니다.  

결과를 보니 결혼여부, 생년월일에 누락된 값이 존재하는군요! 해당 컬럼을 더 자세히 들여다 보아야겠습니다!

2. 데이터 형태 확인 < LEN 함수 > 

SELECT  LEN(MEM_NM)
		, COUNT(DISTINCT MEM_NO)
  FROM  MEM_TEMP 
 GROUP
	BY  LEN(MEM_NM)
 ORDER
	BY  1 DESC

LEN 함수를 사용하면 어떤 길이로 데이터가 들어가 있는지 확인할 수 있습니다. GROUP BY 함수를 사용하여 회원 이름길이에 따른 회원수를 살펴볼 수 있겠군요! 너무 길게, 혹은 너무 짧게 입력되어 있는 데이터가 있다면 정확한 데이터가 아닐수도 있겠단 생각이 듭니다!

 

3. 데이터 중복 확인 < DISTINCT COUNT * > 

SELECT  MEM_NO
		, MOBL 
		, STATUS
		, COUNT(DISTINCT MEM_NO)
  FROM  MEM_TEMP
 WHERE  LEFT(MOBL,3) NOT IN ('010', '011', '017', '018', '019', '016') 
		--AND (LEN(REPLACE(MOBL,'-','')) BETWEEN 10 AND 11 )
		AND STATUS = 'ACTIVE' 
 GROUP
	BY  MEM_NO,
		MOBL 
		, STATUS
 ORDER
	BY  3, 4 DESC 

 

 

 COUNT(DISITNCT) 함수는 제가 여태까지 가장 많이 사용했던 함수라고 생각합니다. 데이터 MART 생성 등 분석 전단계에서의 중복값 발생은 가장 많이 발생하는 실수이기도 합니다.  위에서는 핸드폰 번호 별로 COUNT(DISTINCT MEM_NO) 함수를 사용하여 ORDER BY 구문과 함께 사용하였습니다. 이렇게 나타내면 아래처럼 이상한 번호로 입력한 회원 수를 알 수 있겠죠? 

이처럼 가장 기본적이면서 중요한 데이터 정합성 검사를 위한 SQL 쿼리 구문을 알아보았습니다. 이후에도 분석에 필요한 SQL 쿼리문이 있다면 추가적으로 업로드할 예정입니다!