[SQL 입문반 33기] 3주차 복습(SQL ver)-2

데이터리안 SQL 데이터 분석 캠프 입문반 3주차 복습

이번 글에서는 피봇 테이블, 데이터 연결에 대해서 복습해보겠습니다.

 

0. 데이터 설명

이전에 사용했던 타이타닉 데이터와 데이콘의 이커머스 데이터를 같이 활용하겠습니다.

 

onlinesales 데이터

변수명	정의
customer_id	고객 고유 ID
order_id	거래 고유 ID
date	거래가 이루어진 날짜
product_id	제품 고유 ID
category	제품이 포함된 카테고리
quantity	주문한 품목 수
avg_cost	수량 1개당 가격 (단위: 달러) 동일 상품이어도 세부 옵션에 따라 가격이 다를 수 있음
shipping_fee	배송비용 (단위: 달러)
coupon_status	할인쿠폰 적용 상태

 

customer 데이터

변수명	정의
customer_id	고객 고유 ID
sex	고객 성별
local	고객지역
period	가입기간 (단위: 월)

 

discount 데이터

변수명	정의
month	월(Month) 정보
category	제품이 포함된 카테고리
coupon_code	쿠폰코드
discount_rate	해당 쿠폰에 대한 할인율

 

 

데이터 출처: https://dacon.io/competitions/official/236222/data

이커머스 고객 세분화 분석 아이디어 경진대회 - DACON

 

데이터 업로드 과정은 아래 글을 참고해주세요.

https://whda.tistory.com/13

[MySQL]스키마 생성, CSV파일 업로드, utf8인코딩 오류 해결

 

1. 피봇 테이블

피봇 테이블에대해서 ChatGPT에 검색하면 아래와 같이 나옵니다.

피봇 테이블(Pivot Table)은 데이터를 요약하고 분석하는 데 사용되는 강력한 도구입니다. 원래의 데이터를 기준으로 행과 열을 재구성하여, 특정 기준에 따라 데이터를 그룹화하거나 집계할 수 있도록 도와줍니다. 주로 대량의 데이터를 빠르게 요약하고 분석할 때 유용하며, Microsoft Excel, Google Sheets, SQL 및 Python 등 다양한 환경에서 사용됩니다.

피봇 테이블의 주요 기능:
1. 데이터 요약: 원본 데이터를 행, 열, 값으로 그룹화하여 간단하게 요약할 수 있습니다.
- 예를 들어, 각 상품의 판매량, 지역별 매출 등을 쉽게 볼 수 있습니다.
2. 집계 함수 사용: 피봇 테이블은 합계, 평균, 카운트, 최대값, 최소값 등 다양한 집계 함수로 데이터를 요약할 수 있습니다.
3. 데이터 분류: 특정 기준으로 데이터를 분류하거나 그룹화하여 원하는 방식으로 재구성할 수 있습니다.
4. 데이터 필터링: 데이터를 특정 조건에 따라 필터링하여 필요한 정보만을 확인할 수 있습니다.

피봇 테이블의 구성 요소:
1. 행(Row): 데이터를 기준으로 그룹화할 수 있는 행. 예를 들어, 지역별 판매 데이터를 행으로 사용할 수 있습니다.
2. 열(Column): 데이터를 기준으로 구분할 수 있는 열. 예를 들어, 제품 카테고리나 연도별 데이터를 열로 사용할 수 있습니다.
3. 값(Values): 피봇 테이블에서 요약할 실제 데이터. 합계, 평균, 카운트 등의 값을 표시할 수 있습니다.
- 예를 들어, 판매 수량이나 매출액이 해당됩니다.
4. 필터(Filter): 원하는 조건에 따라 데이터 일부만 표시하거나 제외할 수 있습니다.

피봇 테이블 예시 (Python pandas에서의 활용)

import pandas as pd

# 데이터프레임 생성
data = {
    'Region': ['North', 'South', 'East', 'West', 'North', 'South', 'East', 'West'],
    'Product': ['A', 'A', 'B', 'B', 'A', 'B', 'A', 'B'],
    'Sales': [100, 150, 200, 130, 120, 170, 180, 190]
}
df = pd.DataFrame(data)

# 피봇 테이블 생성
pivot_table = pd.pivot_table(df, 
                             values='Sales',     # 집계할 값
                             index='Region',     # 행 기준 (지역별)
                             columns='Product',  # 열 기준 (제품별)
                             aggfunc='sum',      # 집계 함수 (합계)
                             fill_value=0)       # NaN 값 처리
print(pivot_table)

위 코드에서는 pandas의 pivot_table() 함수를 사용하여 각 지역(Region)별로 제품(Product)의 판매량(Sales)을 합산한 피봇 테이블을 생성합니다.

SQL에서 피봇 테이블
SQL에서도 피봇 테이블을 생성할 수 있습니다.
- 예를 들어, GROUP BY와 CASE WHEN 등을 사용해 특정 조건별로 데이터를 요약할 수 있습니다.

SELECT Region,
       SUM(CASE WHEN Product = 'A' THEN Sales ELSE 0 END) AS Sales_A,
       SUM(CASE WHEN Product = 'B' THEN Sales ELSE 0 END) AS Sales_B
FROM sales_data
GROUP BY Region;

피봇 테이블의 장점:
1. 데이터를 쉽게 요약: 대량의 데이터를 원하는 방식으로 요약할 수 있어 데이터 분석이 훨씬 쉬워집니다.
2. 빠른 데이터 탐색: 복잡한 데이터를 행과 열로 쉽게 나누어 볼 수 있어 패턴을 빠르게 파악할 수 있습니다.
3. 사용자 친화적: 비즈니스 사용자도 직관적으로 사용할 수 있는 도구입니다.
- Excel 같은 도구에서는 드래그 앤 드롭만으로 피봇 테이블을 만들 수 있습니다.

피봇 테이블의 사용 사례:
- 판매 데이터를 지역, 제품, 연도별로 집계
- 직원 데이터를 부서, 성별, 직급별로 요약
- 고객 데이터를 구매 이력, 지역, 성별로 분석

피봇 테이블은 EDA(탐색적 데이터 분석) 과정에서도 데이터를 다양한 관점에서 빠르게 요약하고 이해할 수 있는 유용한 도구입니다.

 

1단계: 분기별로 order_id 확인하세요.

SELECT order_id
	, CASE WHEN date BETWEEN '2019-01-01' AND '2019-03-31' THEN order_id END AS Q1
	, CASE WHEN date BETWEEN '2019-04-01' AND '2019-06-30' THEN order_id END AS Q2
	, CASE WHEN date BETWEEN '2019-07-01' AND '2019-09-30' THEN order_id END AS Q3
	, CASE WHEN date BETWEEN '2019-10-01' AND '2019-12-31' THEN order_id END AS Q4 	
FROM onlinesales;

각 분기에 해당 되는 데이터만 각 컬럼에 데이터가 표시되는 걸 확인할 수 있습니다.

 

2단계: 전체 주문수와 분기별 주문수 확인하세요.

SELECT COUNT(DISTINCT order_id) AS cnt_orders
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-01-01' AND '2019-03-31' THEN order_id END) AS Q1_cnts
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-04-01' AND '2019-06-30' THEN order_id END) AS Q2_cnts
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-07-01' AND '2019-09-30' THEN order_id END) AS Q3_cnts
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-10-01' AND '2019-12-31' THEN order_id END) AS Q4_cnts
FROM onlinesales;

 

3단계: 쿠폰 상태별 총 주문수와 분기별 주문수 확인하세요.

SELECT coupon_status
	, COUNT(DISTINCT order_id) AS Total
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-01-01' AND '2019-03-31' THEN order_id END) AS Q1_cnts
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-04-01' AND '2019-06-30' THEN order_id END) AS Q2_cnts
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-07-01' AND '2019-09-30' THEN order_id END) AS Q3_cnts
	, COUNT(DISTINCT CASE WHEN date BETWEEN '2019-10-01' AND '2019-12-31' THEN order_id END) AS Q4_cnts
FROM onlinesales
GROUP BY coupon_status;

 

2. 데이터 연결하기

• 위아래로 연결하기(UNION, UNION ALL)

USE titanic;

SELECT *
FROM titanic_train

UNION ALL

SELECT *
FROM titanic_train;

UNION: 합집합으로 중복이 있다면 제외합니다.

UNION ALL: 합집합인데 중복이 있어도 그대로 나타냅니다.

 

• INNER JOIN

 

출처: https://sql-joins.leopard.in.ua/

위 사이트에서 화면을 클릭하면서 여러 JOIN에 대해 직관적으로 확인해볼 수 있습니다.

INNER JOIN을 시각화하면 위와 같습니다. 

테이블 A와 B 사이에 공통된 행들만 나타납니다.

 

테이블 A

id	name
1	철수
2	훈
3	맹구

 

테이블 B

id	age
2	5
3	5
4	5

 

INNER JOIN 한다면 아래와 같이 됩니다.

id	name	id	age
2	훈	2	5
3	맹구	3	5

 

 

 

Q. customer 테이블과 onlinesales 테이블을 INNER JOIN 하세요.

USE e_commerce;

SELECT *
FROM customer AS c
	INNER JOIN onlinesales AS o ON c.customer_id = o.customer_id;

 

Q. 성별이 여성인 데이터만 추출하세요.

SELECT *
FROM customer AS c
	INNER JOIN onlinesales AS o ON c.customer_id = o.customer_id
WHERE sex = 'female';

 

Q. 성별이 여성인 고객의 고객id, 성별, 지역, 제품id, 제품분류, 쿠폰 상태, 할인율을 추출하세요.

SELECT c.customer_id
	, c.sex
	, c.local
    , o.product_id
    , o.category
    , o.coupon_status
    , d.discount_rate
FROM customer AS c
	INNER JOIN onlinesales AS o ON o.customer_id = c.customer_id
    INNER JOIN discount AS d ON d.category = o.category
WHERE sex = 'female'

 

이상으로 3주차 내용을 전부 다 복습해봤습니다.

이번 주차에서는 EDA 과정에 대해서 학습해봤고 앞으로 해볼 프로젝트에서 지금 배웠던 내용들을 활용할 수 있도록 하겠습니다.

 

전체 소스코드가 궁금하다면 아래 깃허브 주소를 참고해주세요.

https://github.com/HSYhrae/TIL/tree/master/MySQL/Datarian

TIL/MySQL/Datarian at master · HSYhrae/TIL

 

 

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 입문반' 을 수강하며 작성한 내용입니다.