신한카드 데이터 분석 2.2: 전처리 과정을 통해 데이터 파악하기 with DataGrip

 

이번 포스트에서는,

• 국내 이용자들에 이어서 국외 이용자들의 집단별 카드이용금액과 카드이용건수 조회
• 국내외 이용자들의 매출액 테이블과 업종별 분류 코드 테이블 JOIN
• 더 구체적인 그룹화를 통한 데이터 파악

제대로 하고 있는 건지 아닌 건지...

---

1. 국외 이용자들의 카드이용건수와 카드이용금액 조회하기

1) 국가별 카드 이용금액과 이용건수 합계

• 미국의 카드 이용금액과 이용건수가 압도적으로 높은 것을 확인.
• 일본의 경우 이용금액이 낮은 편이지만 이용건수는 다른 국가들보다 높은 편임을 알 수 있음.

# (국외) 국가별 카드이용금액과 카드이용건수
SELECT `국가별(COUNTRY_ENG_NM)`,
       SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액',
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수'
FROM salesByCountry_out
GROUP BY `국가별(COUNTRY_ENG_NM)`
ORDER BY SUM(`카드이용금액계(AMT_CORR)`) DESC, SUM(`카드이용건수(USECT_CORR)`) DESC;

 

2) 연도별 카드이용금액과 카드이용건수

• 2020년부터 카드 이용액과 이용건수가 하락함. (코로나19의 영향인 것으로 추정)

# (국외) 연도별 카드이용금액과 카드이용건수
SELECT YEAR(`일별(TS_YMD)`) AS '연도',
       SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액',
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수'
FROM salesByCountry_out
GROUP BY YEAR(`일별(TS_YMD)`)
ORDER BY YEAR(`일별(TS_YMD)`);

---

2.  JOIN을 통해 더 세부적으로 그룹화하여 데이터 파악하기

1) 서울 지역구별 카드 이용분야, 카드 이용금액 및 이용건수 합계

• 대부분의 지역구에서 ‘기타요식’, ‘편의점’, ‘커피전문점’ 등 요식 분야에서 매출이 가장 높게 나옴.
• 다른 지역구와 달리 성북구는 백화점에서의 매출이 가장 높았음.

# 서울 지역구별 카드 이용분야, 카드 이용금액 및 이용건수 합계
SELECT A.`고객주소시군구(SGG)`,
       T.`내국인업종분류(SB_UPJONG_NM)`,
       SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액',
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수'
FROM salesByAddress_in AS A
JOIN typeOfBusiness_in AS T ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE A.`고객주소광역시(SIDO)` = '서울'
GROUP BY A.`고객주소시군구(SGG)`, T.`내국인업종분류(SB_UPJONG_NM)`
ORDER BY A.`고객주소시군구(SGG)`, SUM(`카드이용금액계(AMT_CORR)`) DESC, SUM(`카드이용건수(USECT_CORR)`) DESC;

 

2) 성별, 연령대별 카드 이용분야, 카드 이용금액 및 이용건수 합계

• 이용금액을 기준으로 보았을 때, 10대 여성 → 농수산물, 20대 여성 → 약국에서 매출이 가장 높은 것으로 출력됨.
• 그러나 이용금액은 제품의 분야마다 가격이 다르니, ‘금액이 이용자의 선호도를 완전히 반영하지 않는다’고 생각.

이용건수를 기준으로 본다면,

• 전체 연령 대부분의 이용자들이 한식, 편의점, 기타요식 등 요식 분야에서 매출이 가장 높았음.
• 특히, 30대 남성은 제과점, 40대 여성은 미용실에서 매출이 높았음.

# 성별, 연령대별 카드 이용분야, 카드 이용건수 및 이용금액 합계
SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`,
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수', SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
GROUP BY SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`
ORDER BY SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`;

 

3) 연도별 카드 이용분야, 카드 이용건수 및 이용금액 합계

• 2018년에는 전년도보다 이용분야가 확대됨.
• 2019년의 약국 이용건수가 급격하게 증가함.
• 2020년에는 편의점에서, 2021년에는 제과점에서 이용건수가 가장 높음.

# 요일별, 연도별 카드 이용분야, 카드 이용건수 및 이용금액 합계
SELECT YEAR(T.`일별(TS_YMD)`) AS '연도', B.`내국인업종분류(SB_UPJONG_NM)`,
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수', SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액'
FROM salesByTime_in AS T
JOIN typeOfBusiness_in AS B ON T.`내국인업종코드(SB_UPJONG_CD)` = B.`내국인업종코드(SB_UPJONG_CD)`
GROUP BY YEAR(T.`일별(TS_YMD)`), B.`내국인업종분류(SB_UPJONG_NM)`
ORDER BY YEAR(T.`일별(TS_YMD)`), SUM(`카드이용건수(USECT_CORR)`) DESC;

 

4) 국가별 카드 이용분야, 카드 이용건수 및 이용금액 합계

• 미국과 일본이 다른 나라들에 비해 전체적으로 이용건수가 높음.
• 그 중 미국은 스포츠 시설, 일본은 기타요식에서 이용건수가 높음.

# 국가별 카드 이용분야, 카드 이용건수 및 이용금액 합계
SELECT C.`국가별(COUNTRY_ENG_NM)`, B.`외국인관광업종분류(SF_UPJONG_NM)`,
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수', SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액'
FROM salesByCountry_out AS C
JOIN typeOfBusiness_out AS B ON C.`외국인관광업종코드(SF_UPJONG_CD)` = B.`외국인관광업종코드(SF_UPJONG_CD)`
GROUP BY C.`국가별(COUNTRY_ENG_NM)`, B.`외국인관광업종분류(SF_UPJONG_NM)`
ORDER BY C.`국가별(COUNTRY_ENG_NM)`, SUM(`카드이용건수(USECT_CORR)`) DESC;

 

5) 국가별, 연도별 카드 이용분야, 카드 이용건수 및 이용금액 합계

• 2017년에 미국과 일본이 편의점에서 압도적인 이용건수를 보여줌.
• 2017년 미국의 한식 이용건수는 다른 업종에 비해 낮지만 이용금액은 가장 높은 수치를 기록함.
• 연도별 국가별 한식, 편의점, 기타요식이 대부분 상위 매출을 기록함.

# 국가별, 연도별 카드 이용분야, 카드 이용건수 및 이용금액 합계
SELECT YEAR(T.`일별(TS_YMD)`) AS '연도', C.`국가별(COUNTRY_ENG_NM)`, `외국인관광업종분류(SF_UPJONG_NM)`,
       SUM(T.`카드이용건수(USECT_CORR)`) AS '카드이용건수', SUM(T.`카드이용금액계(AMT_CORR)`) AS '카드이용액'
FROM salesByTime_out AS T
JOIN typeOfBusiness_out AS B
	ON T.`외국인관광업종코드(SF_UPJONG_CD)` = B.`외국인관광업종코드(SF_UPJONG_CD)`
JOIN salesByCountry_out AS C
	ON B.`외국인관광업종코드(SF_UPJONG_CD)` = C.`외국인관광업종코드(SF_UPJONG_CD)`
GROUP BY YEAR(T.`일별(TS_YMD)`), C.`국가별(COUNTRY_ENG_NM)`, B.`외국인관광업종분류(SF_UPJONG_NM)`
ORDER BY YEAR(T.`일별(TS_YMD)`), C.`국가별(COUNTRY_ENG_NM)`, SUM(T.`카드이용건수(USECT_CORR)`) DESC;

 

 

🔗 관련 링크

서울시 빅데이터 캠퍼스 - https://bigdata.seoul.go.kr/main.do

원 데이터 확인(이전 포스트) - https://justinaofjune.tistory.com/59