SQL을 활용한 데이터 분석 사례

프로젝트 진행하면서... 내가 하기는 했지만 어디에도 기록이 남지 않고 내 에버노트에서 먼지만 쌓이고있는 소소한(?) 분석 사례를 블로그에 간단하게 정리해본다.

사실 지금에서야 생각해보면 분석이라고 하기도 애매한 '단순집계' 이지만..
그래도 데이터분석에 관심을 가지고 있는 누군가는 이 글을 통해 조금이나마 도움이 되지 않을까? 싶어 과거 수행했던 소단위 분석 중 한 가지를 본 피드에 정리해 보았다.

 

 

---

 

0.  Intro

0.1. 설명

A공사는 자국의 모든 수출입 기업을 관리하고 있다.

 

현재 A공사에서 관리하고 있는 내부 데이터 및 신규 수집한 외부 데이터를 활용하여 기업의 건전성, 성실성 등을 수치화하고, 이를 토대로 수출입 기업의 (우량/부실) 정도를 수치화 할 수 있는 평가지표를 개발하고 싶다.

 

 

    단계1. A공사 운영DB 내 주요 테이블로부터 기업의 (우량/부실)정도를 구분할 수 있는 특징적인 feature를 추출한다.

    단계2. 추출된 feature를 분류모델에 도입해보고, 그 타당성을 평가한다.

 

 

최종 선별된 feature는 기업 우량 등급을 관리하는데 사용될 것이다.

 

 

 

 

0.2. 분석 목표 설정

A공사에서 기사용되고 있는 feature 에는 다음의 점수가 존재한다.

• 기업 서베이 점수
• 수출평가 점수
• 내수평가 점수

 

사전 담당자 인터뷰에서는 수출평가 점수, 내수평가 점수 가 기업 서베이를 통해 산출되었음을 전달 받았으나 확실하지는 않다.

기업 서베이 데이터를 통해 기업 서베이 점수 를 산출하였음은 확인된 사항이다.

 

기업 서베이 데이터로부터 수출/내수 평가 점수가 산출되었는지 여부를 확인하고 싶다.

 

 

 

 

기업 서베이 데이터로부터 (수출평가 점수, 내수평가 점수) 를 산출하였는가?

 

 

 

 

서베이 데이터로부터 기업 평가점수를 산출했을시 다음의 문제가 예상된다.

1. feature간 관계(dependent) : feature 간 선형 or 비선형적 관계를 보유할 가능성 높아 최종 모델 성능에 영향을 줄 수 있음
2. 영향력 왜곡 가능성 : 관계가 있는 feature를 모델에 동시에 적용하는 경우 feature가 내포하고 있는 영향력이 중복 반영되어 영향력 과대평가 가능성 존재
   (예를 들어, 영향력 왜곡이 발생하는 경우 기업 서베이 이력이 존재한다는 사실만으로 점수가 과대평가 될 수 있음)

 

해당 데이터가 어떤 방식으로 업데이트 되는지 잘 알고 있는 담당자에게 물어보면 쉽게 해결되는 사항이긴 하지만.. 몇 번의 프로젝트를 진행하면서 느낀 부분은 담당자의 말을 모두 신뢰할 수는 없다는 것이다. 데이터까지 세세하게 관리하면서 경우는 별로 없었다는 것이다.
(공사든, 국내 커머스 1등 먹는 기업이든 상관없이 다 똑같더라..)

 

 

 

0.3. 문제 정의

위의 분석목표를 해결하기 위해 다음과 같이 문제를 단순화하였다.

 

 

테이블 정의

• TABLE0 : 서베이 테이블
• TABLE1 : 기업 수출역량 테이블
• TABLE2 : 기업 내수역량 테이블

 

질문

• TABLE0으로부터 TABLE1과 TABLE2가 생성되었는가?

 

전제 및 가정

• TABLE0과 TABLE1, TABLE2는 전체 raw 데이터로 구성되어 있다. (특정 목적에 의하여 집계, 추출된 테이블이 아님)
• TABLE0으로부터 TABLE1, TABLE2가 생성되었다면,
      a) TABLE0에 존재하는 고객의 수가 TABLE1, TABLE2에 존재하는 고객 수의 합과 같거나 많다.
      b) TABLE1, TABLE2에 존재하는 고객이 TABLE0에 반드시 존재한다.

 

 

위의 가정에 기반하여 본격적으로 데이터를 들여다 보았다.

 

 

 

---

 

1.  분석(1) : 분석 대상 선정

1.1. 전체 국내 기업 수 확인

가장 먼저 A공사 DB에 등록되어있는 전체 국내 기업 수를 확인한다.

 

TABLE0, TABLE1, TABLE2에 존재하는 기업 구분 key값에 잘못된 데이터가 들어간 경우 본 분석에 영향을 주기 때문에

비정상 레코드 처리를 위해 MASTER 테이블에 존재하지 않는 기업을 제외하고자 한다.

 

 

전체 국내기업에 대한 MASTER 정보는 MASTER_CORP 테이블에 존재한다.

 

MASTER_CORP 테이블에는 기업을 고유하게 식별하는 key 컬럼(BUSINESS_NO 등)이 존재하기 때문에

DISTINCT 함수를 활용하여 전체 기업의 수를 쉽게 확인할 수 있었다.

 

-- MASTER_CORP 기업 수 : 약 160,000
SELECT COUNT(DISTINCT BUSINESS_NO)
	FROM MASTER_CORP

 

 

 

1.2. BUSINESS_NO 필터링

TABLE0.BUSINESS_NO 컬럼은 테이블 상에서 char 형식으로 정의 되어 있으나, 실제로는 10자리 정수값으로 구성된 코드정보이다.

 

일부 데이터가 NULL, `0000`, `1`,  과 같은 잘못된 값으로 저장되어 있음을 확인하였다.

(MASTER 테이블에 신규 기업 데이터를 추가하는 과정에서 임의 값을 넣었다거나(human error), 폐업한 기업을 반영하는 과정에서 발생한 것으로 보인다.)

 

GROUP BY 를 활용하여 BUSINESS_NO의 길이에 따른 기업 수를 확인해보자.

 

SELECT LENGTH(BUSINESS_NO) AS LEN_BSNO
		, COUNT(*) AS CNT
    FROM MASTER_CORP
    GROUP BY LENGTH(BUSINESS_NO)

 

결과

LEN_BSNO	CNT
1	1,716
2	2
3	3
4	2
5	3
6	6
7	7
8	10
9	67
10	158,095
11	3
12	1
13	1
14	1
15	1
NULL	21,645

대부분의 경우 BUSINESS_NO의 길이가 10인 것으로 확인되나, 그렇지 않은 경우도 일부 존재한다.

BUSINESS_NO의 길이가 10이 아닌 데이터를 불필요 데이터로 간주하여 향후 분석에서 제외한다.

 

 

 

BUSINESS_NO의 길이가 10인,

약 158,000개 기업을 분석 대상 모집단으로 정의

 

 

 

-- MASTER_CORP 분석대상 기업 수 : 약 158,000
SELECT COUNT(DISTINCT BUSINESS_NO)
    FROM MASTER_CORP
    WHERE LENGTH(BUSINESS_NO) = 10

 

 

분석 대상 테이블인 TABLE0, TABLE1, TABLE2에 존재하는 모든 기업은 위의 158,000개 기업에 해당하였다.

 

 

 

1.3. 추가

길이는 10이지만 실제 BUSINESS_NO와 관련이 없는 데이터가 존재할 수 있다.

• CASE1. 길이가 10인 문자열
• CASE2. 길이가 10인 숫자형이지만, 실제 BUSINESS_NO가 아닌 경우

 

그러나 확인결과 type이 숫자형이 아닌 CASE는 존재하지 않았고,

10자리 숫자이지만 실제 BUSINESS_NO 정보가 아닌 CASE를 검증하는 것은 현재 상황에서 불가능하다.

 

그러므로 이후의 분석은

"BUSINESS_NO 컬럼에 저장된 10자리 코드는 모두 기업의 고유 KEY 역할을 한다" 

라는 가정하에 진행하였다.

 

 

 

---

 

 

2. 분석(2) : 서베이 테이블(TABLE0) 분석

2.1. COMPANY_ID 집계

본격적으로 TABLE0 그리고 TABLE1+TABLE2 에 존재하는 기업의 수를 비교한다.

• TABLE0 : 서베이 테이블
• TABLE1 : 기업 수출역량 테이블
• TABLE2 : 기업 내수역량 테이블

TABLE0은 A공사에서 기업 회원들에게 설문을 통한 서베이 결과를 저장한 테이블이다.

 

 

 

TABLE0 주요 컬럼

• SURVEY_ID
• COMPANY_ID
• QUESTION_ID
• ANSWER

 

SURVEY에 참여한 기업 수를 확인하자.

기업 수는 TABLE0에서 고유 KEY역할을 하는 COMPANY_ID의 DISTINCT COUNT를 통해 쉽게 확인할 수 있었다.

 

-- TABLE0 기업 수 : 44,046
SELECT COUNT(DISTINCT COMPANY_ID)
    FROM TABLE0

 

TABLE0에 존재하는 기업의 수는 44,046으로 분석 대상인 국내 기업 수(약 158,000)의 약 28% 정도이다.

 

 

 

---

 

 

3. 분석(3) : 수출/내수 기업 평가 테이블(TABLE1, TABLE2) 분석

3.1. 테이블별 BUSINESS_NO 집계

다음으로 TABLE1, TABLE2 각각에 존재하는 기업의 수를 알아본다.

 

TABLE1, TABLE2 에서 기업을 구분하는 KEY는 MASTER_CORP 테이블에서와 마찬가지로 BUSINESS_NO 컬럼을 사용한다.

 

-- TABLE1 기업 수 : 11,729
SELECT COUNT(DISTINCT BUSINESS_NO)
    FROM TABLE1

-- TABLE2 기업 수 : 18,205
SELECT COUNT(DISTINCT BUSINESS_NO)
    FROM TABLE2

 

TABLE1 : 11,729기업, TABLE2 : 18,205기업이 존재하는 것으로 확인되었다. 

따라서 평가점수가 존재하는 수출/내수 기업의 수는 11,729 + 18,205 = 29,934개 라고 생각할 수 있다. 

하지만 TABLE1과 TABLE2가 disjoint set인지 joint set인지에 따라 그 결과가 달라질 수 있다는 점을 생각해야한다.

동일한 기업이 수출기업과 내수기업에 동시에 존재하는 CASE가 존재할 수 있고(교집합 존재 유무), A사에서 수출기업과 내수기업을 어떤 방식으로 구분하지 확인할 수 없기 때문이다.

 

예를 들어, 동일 기업이 TABLE1과 TABLE2에 존재하면서 서로 다른 BUSINESS_NO를 부여받았을 가능성(TABLE별 BUSINESS_NO의 numbering이 다른 경우)이 존재할 수 있다. 컬럼명은 동일하지만 테이블별 numbering 기준이 달라서 중복 생성되는 경우도 있기 때문이다.

사실 본 분석에서는 위의 경우를 'BUSINESS_NO는 기업을 구분하는 global한 unique key이다. (기업 - BUSINESS_NO : one to one 관계)' 라는 사전정보를 통해 따로 검증할 필요는 없었다. 만약 위와 같은 사전 정보가 없다면, TABLE1과 TABLE2에 존재하는 BUSINESS_NO를 묶고, 이를 MASTER_CORP 테이블과 JOIN하여 UNIQUE한 value와 대조하는 등의 추가 과정이 필요했을 것이다.

 

 

검산의 의미로 TABLE1과 TABLE2의 BUSINESS_NO를 UNION하고

DISTINCT COUNT를 산출하여 위에서 구한 내수/수출 기업수와 비교해보자.

 

-- TABLE1 + TABLE2 기업 수 : 29,934
SELECT COUNT(DISTINCT BUSINESS_NO)
	FROM(
    		SELECT DISTINCT BUSINESS_NO
            	FROM TABLE1
            UNION
            SELECT DISTINCT BUSINESS_NO
                FROM TABLE2
            )

 

실제로 TABLE1과 TABLE2 각각을 합친 기업의 수(11,729 + 18,205 = 29,934개)와

TABLE1과 TABLE2 를 UNION한 테이블에서의 기업의 수(29,934개)가 같다.

즉, TABLE1과 TABLE2에 동시에 존재하는 기업은 없는 것으로 보인다.

 

 

 

"기업은 내수기업 또는 수출기업 중 하나이다."

 

 

---

 

 

4. 분석 결과

지금까지의 집계결과를 정리해보자.

 

전체 국내기업 수	약 158,000
1)서베이 참여 기업 수	44,046
2)수출/내수 평가 점수 존재 기업 수	29,934
내수평가 기업수	18,205
수출평가 기업수	11,729

 

비교결과

(서베이 참여 기업 수) > (내수/수출점수 평가 기업)

==> 서베이에 참여했더라도 내수/수출 평가점수가 반드시 산출되는 것은 아니다.

 

해당 정보만으로는 서베이 데이터로부터 내수/수출평가 데이터를 산출하지 않았음을 검증할 수 없다. 

 

추가적으로 내수/수출 평가점수가 존재하는 기업중 서베이 참여 이력이 없는 CASE가 존재하는 경우 위의 가설을 검증할 수 있을 것이다.

 

 

---

향후 분석 과제

TABLE0과 TABLE1+TABLE2에 존재하는 기업을 JOIN하여 CASE별 COUNT 비교

1) TABLE0 존재O, TABLE1+TABLE2 존재O COUNT
2) TABLE0 존재O, TABLE1+TABLE2 존재X COUNT
3) TABLE0 존재X, TABLE1+TABLE2 존재O COUNT
4) TABLE0 존재X, TABLE1+TABLE2 존재X COUNT

 

TABLE0으로부터 TABLE1, TABLE2가 도출되었다면,

적어도 3) TABLE0 존재X, TABLE1+TABLE2 존재O COUNT 에 속하는 기업이 존재하지 않아야 한다.

(TABLE1, TABLE2에 존재하는 고객이 TABLE0에는 반드시 존재해야한다.)

 

각 케이스에 대한 기업 COUNT를 산출하여 분석의 기본 가정에 위배되는지 여부를 확인하자.