신한카드 데이터 분석 3: 질문에 대한 답을 찾는 과정 with PyCharm

Toy Projects

신한카드 데이터 분석 3: 질문에 대한 답을 찾는 과정 with PyCharm

듀공🥕 2024. 4. 4. 02:45

이번 포스트에서는,

프로젝트 초반에 설정햇던 질문에 대한 답 찾아보기
조회된 결과에 대해 나름의 분석과 의미 찾기

프로젝트 초반에 설정해 두었던 질문은 다음과 같다.

성별 및 연령별 소비 분야에 대해 어떠한 차이가 있는가?
요일별 외식 소비에 차이가 있는가?
MZ세대의 과시소비(백화점, 골프, 호텔) 경향 확인해보기

질문에 대한 답을 찾기에 앞서, 중간 정리 단계가 필요하다고 생각하였다.

해당 데이터는 서울/경기에 주로 집중되어 있음. (수도권 과밀화로 인한 것으로 추정)
- 수도권 지역과 지방의 소비 패턴 비교, 국내외 소비 패턴 비교까지 한다면 범위가 방대해질 것으로 보았음.
- 수도권으로 한정하여 이용자들의 소비 패턴을 분석하고자 함.
설정한 질문들은 모두 개인 소비에 대한 것임.
- 개인/법인 분류 컬럼을 통해 개인의 소비 패턴을 분석하고자 함.

중간 정리를 함으로써 좀 더 구체화된 주제를 잡아서 답을 찾아보고자 한다.
이번에는 결과 시각화를 위해 PyCharm을 통해 분석을 시행할 예정이다.

1. 수도권 지역의 성별 및 연령별 소비 분야에 대해 어떠한 차이가 있는가?

성별 및 연령대별 소비 분야의 차이를 보기 위해서는 이용건수와 이용금액이 가장 많은 분야와 가장 적은 분야를 조회하는 단계가 필요하다고 생각하였다.

처음에는 성별과 연령대별로 그룹화하여 MAX()와 MIN()을 이용하여 값을 구하려고 했지만, 계속 에러가 나서 UNION을 사용하였다.

🚨 문제 발생

MAX() MIN() 사용을 통해 성별 및 연령별 최댓값과 최솟값 출력을 원했지만, GROUP BY의 문제였던 건지 출력되지 않고 계속 에러가 발생하였다.

-- 1) 수도권 지역의 성별 및 연령대별 소비 분야에 어떠한 차이가 있는가?
SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`,T.`내국인업종분류(SB_UPJONG_NM)`,
       MAX(SA.`카드이용건수(USECT_CORR)`), SA.`카드이용금액계(AMT_CORR)`
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'F' AND `연령대별(AGE_GB)` = '10대'
GROUP BY SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`
ORDER BY SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`;

❗️ 해결 과정

결국 (무식하게) UNION ALL을 통해 쿼리별로 연령을 나눠서 최댓값과 최솟값을 출력하는 방식으로 변경하였다.
WHERE 절에서 성별과 연령을 입력하고, ORDER BY 절에서 최댓값(DESC)과 최솟값(ASC)을 입력할 수 있도록 쿼리를 작성하였다.

-- 질문에 대한 답 찾기.
-- 1) 수도권 지역의 성별 및 연령대별 소비 분야에 어떠한 차이가 있는가? (union 사용)
-- 10대 이용자
(SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`,T.`내국인업종분류(SB_UPJONG_NM)`,
       ROUND(SUM(SA.`카드이용건수(USECT_CORR)`), 0) AS '카드이용건수 합계',
       ROUND(SUM(SA.`카드이용금액계(AMT_CORR)`), 0) AS '카드이용금액 합계'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
-- 개인/법인 구분, 지역 한정, 조회하고자 하는 성별과 연령대 입력.
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'M' AND `연령대별(AGE_GB)` = '10대'
GROUP BY T.`내국인업종분류(SB_UPJONG_NM)`, SA.`카드이용건수(USECT_CORR)`, SA.`카드이용금액계(AMT_CORR)`
-- 이용건수 or 이용금액 입력. 최댓값은 DESC 최솟값은 ASC 입력.
ORDER BY SUM(SA.`카드이용건수(USECT_CORR)`) ASC
LIMIT 1)
UNION
-- 20대 이용자
(SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`,
       ROUND(SUM(SA.`카드이용건수(USECT_CORR)`), 0) AS '카드이용건수 합계',
       ROUND(SUM(SA.`카드이용금액계(AMT_CORR)`), 0) AS '카드이용금액 합계'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'M' AND `연령대별(AGE_GB)` = '20대'
GROUP BY T.`내국인업종분류(SB_UPJONG_NM)`, SA.`카드이용건수(USECT_CORR)`, SA.`카드이용금액계(AMT_CORR)`
ORDER BY SUM(SA.`카드이용건수(USECT_CORR)`) ASC
LIMIT 1)
UNION 
-- 30대 이용자
(SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`,
       ROUND(SUM(SA.`카드이용건수(USECT_CORR)`), 0) AS '카드이용건수 합계',
       ROUND(SUM(SA.`카드이용금액계(AMT_CORR)`), 0) AS '카드이용금액 합계'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'M' AND `연령대별(AGE_GB)` = '30대'
GROUP BY T.`내국인업종분류(SB_UPJONG_NM)`, SA.`카드이용건수(USECT_CORR)`, SA.`카드이용금액계(AMT_CORR)`
ORDER BY SUM(SA.`카드이용건수(USECT_CORR)`) ASC
LIMIT 1)
UNION ALL
-- 40대 이용자
(SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`,
       ROUND(SUM(SA.`카드이용건수(USECT_CORR)`), 0) AS '카드이용건수 합계',
       ROUND(SUM(SA.`카드이용금액계(AMT_CORR)`), 0) AS '카드이용금액 합계'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'M' AND `연령대별(AGE_GB)` = '40대'
GROUP BY T.`내국인업종분류(SB_UPJONG_NM)`, SA.`카드이용건수(USECT_CORR)`, SA.`카드이용금액계(AMT_CORR)`
ORDER BY SUM(SA.`카드이용건수(USECT_CORR)`) ASC
LIMIT 1)
UNION 
-- 50대 이용자
(SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`,
       ROUND(SUM(SA.`카드이용건수(USECT_CORR)`), 0) AS '카드이용건수 합계',
       ROUND(SUM(SA.`카드이용금액계(AMT_CORR)`), 0) AS '카드이용금액 합계'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'M' AND `연령대별(AGE_GB)` = '50대'
GROUP BY T.`내국인업종분류(SB_UPJONG_NM)`, SA.`카드이용건수(USECT_CORR)`, SA.`카드이용금액계(AMT_CORR)`
ORDER BY SUM(SA.`카드이용건수(USECT_CORR)`) ASC
LIMIT 1)
UNION 
-- 60대 이용자
(SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`,
       ROUND(SUM(SA.`카드이용건수(USECT_CORR)`), 0) AS '카드이용건수 합계',
       ROUND(SUM(SA.`카드이용금액계(AMT_CORR)`), 0) AS '카드이용금액 합계'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'M' AND `연령대별(AGE_GB)` = '60대'
GROUP BY T.`내국인업종분류(SB_UPJONG_NM)`, SA.`카드이용건수(USECT_CORR)`, SA.`카드이용금액계(AMT_CORR)`
ORDER BY SUM(SA.`카드이용건수(USECT_CORR)`) ASC
LIMIT 1)
UNION 
-- 70대 이상 이용자
(SELECT SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`, T.`내국인업종분류(SB_UPJONG_NM)`,
       ROUND(SUM(SA.`카드이용건수(USECT_CORR)`), 0) AS '카드이용건수 합계',
       ROUND(SUM(SA.`카드이용금액계(AMT_CORR)`), 0) AS '카드이용금액 합계'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS T ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE `개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
  AND `성별(SEX_CCD)` = 'M' AND `연령대별(AGE_GB)` = '70대 이상'
GROUP BY T.`내국인업종분류(SB_UPJONG_NM)`, SA.`카드이용건수(USECT_CORR)`, SA.`카드이용금액계(AMT_CORR)`
ORDER BY SUM(SA.`카드이용건수(USECT_CORR)`) ASC
LIMIT 1);

🗿 결과 테이블

1) 여성 이용자의 연령별 카드이용건수가 가장 높은 분야

2) 여성 이용자의 연령별 카드이용건수가 가장 낮은 분야

3) 남성 이용자의 연령별 카드이용건수가 가장 높은 분야

4) 남성 이용자의 연령별 카드이용건수가 가장 낮은 분야

🔍 해석하고 의미 찾기

처음에 결과 테이블을 출력할 때 20대 남성이 ‘정육점’ 소비가 가장 높고 ‘패스트푸드점’ 소비가 가장 낮은 것을 보고 예상과 너무 다른 결과가 나와서 이상함을 느꼈다.

이는 합계 계산 누락으로 인해 오류가 발생한 것이라 판단함.
합계의 최댓값과 최솟값을 출력해야 하는데 전체 매출건수 중 최댓값과 최솟값을 출력한 것이 오류였음.
SUM()을 추가하여 쿼리를 재작성하니 위와 같은 결과가 출력됨.

결과 테이블을 보았을 때 의외였던 것은,

1️⃣ 5060 여성 이용자의 카드 소비 분야가 ‘편의점’에서 가장 높은 수치를 보여준다는 점
👉🏻 10대 학생들이 부모님의 카드로 식비를 해결하는 경우를 생각해 볼 수 있음.
👉🏻 이를 감안하면 편의점에서 소비 정도가 높을 수도 있다고 생각함.

2️⃣ 30대 남성 이용자의 ‘실내골프/헬스’ 분야가 가장 낮은 이용건수를 기록한다는 점

2. 수도권 지역의 연도별 및 요일별 외식 소비에 어떠한 차이가 있는가?

먼저 외식분야를 ‘중분류(SB_M_UPJONG_NM)’ 컬럼에서 ‘한식’과 ‘일식/중식/양식’으로 특정하였다.
그리고 연도별 및 요일별로 외식 소비 경향을 파악할 수 있도록 쿼리를 작성하였다.

1) 연도별 외식분야 카드 이용건수 및 이용금액 합계

연도별 외식 소비에 대한 카드 이용건수와 이용금액의 합계이다.
개인적으로 이용건수와 이용금액의 합계 수치가 낮다고 생각하여 사이트에서 제공한 메타 데이터를 확인하였다.

카드이용건수 단위: 건(件)
카드이용금액 단위: 원
데이터의 이해를 돕기위해 별도로 가공하여 제공하는 정보로써 원본 데이터와 차이가 있는 샘플 데이터임.

세 가지 사항을 확인하였고, 이를 감안하고 그대로 진행하고자 한다.

-- 2) 수도권 지역의 연도별 및 요일별 외식 소비에 어떠한 차이가 있는가?
-- 연도별 외식분야 카드이용건수 및 카드이용금액 합계
SELECT YEAR(T.`일별(TS_YMD)`) AS '연도', -- T.`요일(DAW)`, B.`내국인업종분류(SB_UPJONG_NM)`,
       CONCAT(ROUND(SUM(T.`카드이용건수(USECT_CORR)`), 0), '건') AS '카드이용건수 합계',
       CONCAT(ROUND(SUM(T.`카드이용금액계(AMT_CORR)`), 0), '원') AS '카드이용금액 합계'
FROM salesByTime_in AS T
JOIN typeOfBusiness_in AS B ON T.`내국인업종코드(SB_UPJONG_CD)` = B.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesBySexAge_in AS SA ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE SA.`개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
      AND B.`중분류(SB_M_UPJONG_NM)` = ('한식' OR '일식/중식/양식')
GROUP BY YEAR(T.`일별(TS_YMD)`)
ORDER BY YEAR(T.`일별(TS_YMD)`);

2) 요일별 외식분야 카드 이용건수 및 이용금액 합계

-- 요일별 외식분야 카드이용건수 및 카드이용금액 합계.
SELECT T.`요일(DAW)`,
       CONCAT(ROUND(SUM(T.`카드이용건수(USECT_CORR)`), 0), '건') AS '카드이용건수 합계',
       CONCAT(ROUND(SUM(T.`카드이용금액계(AMT_CORR)`), 0), '원') AS '카드이용금액 합계'
FROM salesByTime_in AS T
JOIN typeOfBusiness_in AS B ON T.`내국인업종코드(SB_UPJONG_CD)` = B.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesBySexAge_in AS SA ON SA.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON A.`내국인업종코드(SB_UPJONG_CD)` = T.`내국인업종코드(SB_UPJONG_CD)`
WHERE SA.`개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
      AND B.`중분류(SB_M_UPJONG_NM)` = ('한식' OR '일식/중식/양식')
GROUP BY T.`요일(DAW)`
-- 월~일 순서로 정렬.
ORDER BY CASE
    WHEN T.`요일(DAW)`='월요일' THEN 1
    WHEN T.`요일(DAW)`='화요일' THEN 2
    WHEN T.`요일(DAW)`='수요일' THEN 3
    WHEN T.`요일(DAW)`='목요일' THEN 4
    WHEN T.`요일(DAW)`='금요일' THEN 5
    WHEN T.`요일(DAW)`='토요일' THEN 6
    WHEN T.`요일(DAW)`='일요일' THEN 7
    END ASC;

🔍 해석하고 의미 찾기

연도별 외식 소비 경향: 코로나로 인해2019년과 2020년의 카드 이용건수가 낮을 것이라고 생각하였지만,

1️⃣ 2020년에 소비 건수가 가장 높음
2️⃣ 2019년은 다른 년도에 비해 소비 건수가 높은 편임
👉🏻 이는 외식 비율보다는 배달 이용률의 증가가 영향을 미쳤을 것으로 예상함.

요일별 소비 경향: 금요일과 토요일이 가장 높을 것이라 예상하였지만, 수요일이 토요일만큼 높은 이용 건수를 기록하였다.

3. 수도권 지역의 MZ세대의 과시소비(백화점, 골프, 호텔) 경향 확인해보기

먼저 MZ세대를 2030 세대로 특정하기로 하였다.
그리고 ‘내국인업종분류(SB_UPJONG_NM)’ 컬럼에서 분류할 수 있는 과시소비 항목은 다음과 같다.
해당 항목에서 특정 세대의 소비가 다른 세대보다 높은지, 연도별로 증가하는지 알아보고자 한다.

백화점	실외골프/스키	면세점	시계/귀금속
호텔/콘도	실내골프/헬스	의복/의류	패션/잡화

연도별 및 과시소비 업종별로 카드 이용건수가 가장 높은 성별과 연령대는 무엇인지 알아보고자 하였다.

-- 3)수도권 지역의 MZ세대의 과시소비(백화점,골프,호텔)경향 확인해보기.
-- 과시소비 항목:백화점,호텔/콘도,실외골프/스키,실내골프/헬스,면세점,의복/의류,시계/귀금속,패션/잡화
SELECT YEAR(SA.`일별(TS_YMD)`) AS '연도별', SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`,
       MAX(SA.`카드이용건수(USECT_CORR)`) AS '카드이용건수', B.`내국인업종분류(SB_UPJONG_NM)`
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS B ON SA.`내국인업종코드(SB_UPJONG_CD)` = B.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON SA.`내국인업종코드(SB_UPJONG_CD)` = A.`내국인업종코드(SB_UPJONG_CD)`
WHERE B.`내국인업종분류(SB_UPJONG_NM)` = '백화점' OR
      B.`내국인업종분류(SB_UPJONG_NM)` = '면세점' OR
      B.`내국인업종분류(SB_UPJONG_NM)` = '호텔/콘도' OR
      B.`내국인업종분류(SB_UPJONG_NM)` = '의복/의류' OR
      B.`내국인업종분류(SB_UPJONG_NM)` = '실외골프/스키' OR
      B.`내국인업종분류(SB_UPJONG_NM)` = '실내골프/헬스' OR
      B.`내국인업종분류(SB_UPJONG_NM)` = '시계/귀금속' OR
      B.`내국인업종분류(SB_UPJONG_NM)` = '패션/잡화'
      AND SA.`개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
GROUP BY YEAR(SA.`일별(TS_YMD)`), SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`,
			B.`내국인업종분류(SB_UPJONG_NM)`
ORDER BY YEAR(SA.`일별(TS_YMD)`), SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`;

결과 출력 후, 예상과 달리 소비 업종이 다양하지 않아서 업종별 이용건수가 있는지 조회해 보았다.

-- 과시소비 항목에 대한 이용건수 존재 여부 알아보기.
SELECT B.`내국인업종분류(SB_UPJONG_NM)`, SA.`성별(SEX_CCD)`, SA.`연령대별(AGE_GB)`,
       SA.`카드이용건수(USECT_CORR)` AS '카드이용건수'
FROM salesBySexAge_in AS SA
JOIN typeOfBusiness_in AS B ON SA.`내국인업종코드(SB_UPJONG_CD)` = B.`내국인업종코드(SB_UPJONG_CD)`
JOIN salesByAddress_in AS A ON SA.`내국인업종코드(SB_UPJONG_CD)` = A.`내국인업종코드(SB_UPJONG_CD)`
WHERE B.`내국인업종분류(SB_UPJONG_NM)` = '백화점'
      AND SA.`개인법인구분(PSN_CPR)` = '개인' AND A.`고객주소광역시(SIDO)` = ('서울' OR '경기')
ORDER BY B.`내국인업종분류(SB_UPJONG_NM)`;

과시소비 항목 중 백화점, 면세점, 실외골프/스키, 시계/귀금속에 대해서 이용건수 자체가 조회되지 않았음.
- 이는 특정 세대의 과시소비 경향을 확인하기에 표본의 수가 적어서 어려움이 있는 것으로 판단함.

그러므로 다음 포스트에서는 1번과 2번 결과에 대해서 시각화를 진행하고자 한다.

🔗 관련 링크

서울시 빅데이터 캠퍼스 - https://bigdata.seoul.go.kr/main.do
원 데이터 확인(이전 포스트) - https://justinaofjune.tistory.com/59

728x90