TIL(24.07.24.)

codataffee·2024년 7월 24일

TIL

목록 보기

107/135

#INTRO

토마토마토마토마토마토

캠프 시작 100일이 넘었다.....

#코드카타

KATA #96
SQL
- CTE 생성 (WITH 절)
  - GRADING 테이블 생성
    각 직원의 EMP_NO 와 성적에 따른 등급(GRADE) 부여
  - GRADE : 윈도우 함수 AVG(SCORE) OVER (PARTITION BY EMP_NO) 를 사용하여
    각 직원의 평균 점수 계산
- CTE & MAIN TABLE (+ CASE WHEN)
  - BONUS : 직원 등급에 따른 보너스 금액
  - 등급 조건에 따라 보너스 비율 적용 :
    'S' : 기본 급여의 20%
    'A' : 기본 급여의 15%
    'B' : 기본 급여의 10%
    'C' : 보너스 없음 (0%)

# WITH 절 ; GRADING 임시 테이블 생성
WITH GRADING AS (
    # HR_GRADE 테이블에서 EMP_NO를 중복없이 가져오고, 성적에 따른 등급을 부여
    SELECT DISTINCT EMP_NO
         , CASE 
                # 직원의 평균 점수가 96 이상인 경우 'S' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 96
                    THEN 'S'
                # 직원의 평균 점수가 90 이상인 경우 'A' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 90
                    THEN 'A'
                # 직원의 평균 점수가 80 이상인 경우 'B' 등급 부여
                WHEN AVG(SCORE) OVER (PARTITION BY EMP_NO) >= 80
                    THEN 'B'
                # 위 조건에 해당하지 않는 경우 'C' 등급 부여
                ELSE 'C' END GRADE
    FROM HR_GRADE
)

# 메인 쿼리
SELECT H.EMP_NO
     , H.EMP_NAME
     , G.GRADE
     , CASE
            # 등급이 'S'인 경우 보너스는 기본 급여의 20%
            WHEN G.GRADE = 'S' THEN H.SAL * 0.2
            # 등급이 'A'인 경우 보너스는 기본 급여의 15%
            WHEN G.GRADE = 'A' THEN H.SAL * 0.15
            # 등급이 'B'인 경우 보너스는 기본 급여의 10%
            WHEN G.GRADE = 'B' THEN H.SAL * 0.1
            # 등급이 'C'인 경우 보너스는 없음 (0%)
            WHEN G.GRADE = 'C' THEN H.SAL * 0
            # 위 조건에 해당하지 않는 경우 NULL
            ELSE NULL END BONUS
FROM HR_EMPLOYEES H
     # GRADING 임시 테이블과 HR_EMPLOYEES 테이블을 EMP_NO를 기준으로 이너 조인
     INNER JOIN GRADING G ON H.EMP_NO = G.EMP_NO
# EMP_NO 기준 오름차순 정렬
ORDER BY 1

#최종 프로젝트 진행

EDA 후 인사이트 정리
- 팀원들과 역할을 나눠 EDA 진행
  1. 거래 데이터 기반 고객 특성 파악 위주의 분석
  2. 거래 데이터 기반 제품 특성 파악 위주의 분석

EDA - 고객

데이터 전처리
- customer_id 컬럼 내 데이터 값 레이블 인코딩
  (고유 값 개수 변화 없으므로 인코딩 데이터로 분석 진행 > 용량 변화 4.87GB → 3.16GB)
중복 값 발견 (약 300만 개 행)
- 중복 값은 동일 품목의 여러 구매에 해당하는 정상적인 거래 데이터로 확인.
  (제거하지 않음.)
데이터 확인
- 수치형 데이터 분포 확인 (유의미한 인사이트 발견 X)
- 수치형 데이터들의 상관 분석
  Active- FN : 0.99 (상관 관계가 매우 높음 )
  index_group_no - department_no : 0.6
  garment_group_no - section_no : 0.48
  (상관 관계가 높은 컬럼은 제거하여 분석 효율 높이기)
- 고객 연령별 구매 패턴 확인
  (20 ~ 30 대 구매 빈도 높음, 이후 연령대에서 급감)
- 월별 구매 분포
  (여름 시즌 (6~8월)에 상대적으로 높은 구매가 일어남)

여기까지가 내가 진행한 EDA
아래부터는 팀원들의 EDA 결과 정리

고객 특성 기반 매출 분포 확인

- sales_channel_id (구매 경로) 
- FN (뉴스 구독 여부)
- club_member_status (유저 상태)
- Active (유저 커뮤니케이션 가능 여부)

1-1. club_member_status = ACTIVE ▶ 활성 유저
→ 온라인 매출 비중이 높음
→ <뉴스 구독 X + 커뮤니케이션 X> OR <뉴스 구독 O + 커뮤니케이션 O> 형태의 소비자
→ 구독/커뮤니케이션 허용 유저의 경우, 대부분 정기 구독 진행

1-2. club_member_status ≠ ACTIVE ▶ 비활성유저
→ 대부분 온라인(2) 경로를 통해 매출 발생
→ <뉴스 구독 X + 커뮤니케이션 X> 소비자가 대부분
→ 구독/커뮤니케이션 허용 유저의 경우, 대부분 정기 구독 진행

연령 기반 매출 분포

2-1. 나이대 별 매출
→ 20대에서 가장 높음
→ 10대 + 60대 이상에서 낮음

2-2. 나이대별 회원 특성
club_members_status = Active
→ 20대 회원은 <뉴스 구독 X + 커뮤니케이션 X> 상태가 더 많음
club_members_status ≠ Active
→ 대부분의 나이대에서 club_member_status = PRE-CREATED 고객으로부터 매출 발생
→ 20대 회원에 비해 30대 회원의 매출 비중이 높음 ▶ <뉴스 구독 X + 커뮤니케이션 X>

EDA - 제품

컬럼 간 상관 관계 분석

가장 잘 팔리는 컬러 BLACK

매출 비중이 가장 높은 Index_group Ladieswear

가격대가 가장 높은 section_name Special Collections

상위 top 10 제품으로 보는 특징

7060160 : ”divided 섹션의 데님팬츠라인”

divided 섹션 :
H&M 브랜드 내 "Divided" 컬렉션은 일반적으로 젊은 세대를 대상으로
트렌디하고 젊은이를 겨냥한 패션 아이템

product_group 시계열 분석

EDA 1차 회의

FN - Active 상관 관계 높음 > 컬럼 줄이기
▶ FN 선택 (구독을 했으면 수신동의 거부할 수는 없으므로, FN 여부 = 충분조건 가정)
Table 조인 진행 시 articles / customers 테이블들의 고유 정보가
transactions 테이블에서 중복되어 데이터 편향 또는 증폭의 문제 고려 필요
제품 정보 컬럼 줄이기 (컬럼 선택 및 축약 후 분석)