[DAY8] 멋쟁이사자처럼부트캠프 그로스마케팅

카테고리 없음

[DAY8] 멋쟁이사자처럼부트캠프 그로스마케팅_Today I Learned

hachaenii 2025. 2. 12. 19:41

오늘의 학습 내용
- 여러 테이블 다루기 - JOIN, UNION
- 고급 SQL - 집계 함수와 그룹화

여러 테이블 다루기

1. JOIN

[ KEY WORD ]
1) FOREIGN KEY
한 테이블의 특정 컬럼이 다른 테이블의 기본 키(PRIMARY KEY)를 참조하도록 설정하는 제약 조건
참조된 테이블에 존재하지 않는 값이 입력되지 않도록 제한
2) REFERENCES
FOREIGN KEY를 정의할 때 사용되며, 특정 테이블의 기본 키를 참조하도록 지정하는 역할
3) ON DELETE CASCADE
외래 키가 참조하는 부모 테이블의 데이터가 삭제될 때, 자식 테이블의 데이터도 자동으로 삭제
되도록 설정하는 옵션
4) DISTINCT
SELECT 문에서 중복된 데이터를 제거하고 고유한 값만 반환할 때 사용
# 저자 테이블과 책 테이블 생성 예제 
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE
);
# 저자 데이터 삽입
INSERT INTO authors VALUES (1, '이문열');
INSERT INTO authors VALUES (2, '한강');
# 책 데이터 삽입 (저자를 참조)
INSERT INTO books VALUES (101, '삼국지', 1);
INSERT INTO books VALUES (102, '채식주의자', 2);
# 특정 저자 삭제 (해당 저자의 책도 자동 삭제)
DELETE FROM authors WHERE author_id = 1;

1.1 JOIN의 유형

JOIN 유형	설명
INNER JOIN	두 테이블에서 일치하는 데이터만 반환(교집합)
LEFT JOIN	왼쪽 테이블의 모든 데이터 + 오른쪽 테이블에서 일치하는 데이터
RIGHT JOIN	오른쪽 테이블의 모든 데이터 + 왼쪽 테이블에서 일치하는 데이터
FULL OUTER	두 테이블의 모든 데이터 포함(합집합)

** LEFT JOIN vs RIGHT JOIN

왼쪽(A)과 오른쪽(B) 중 어떤 데이터를 모두 포함시킬지를 정한다고 생각하면 쉽다!

예를 들어 A를 모두 포함시키고 싶다면 LEFT JOIN을 사용하고 B는 A와 일치하는 값은 연결하고 없는건 NULL로 처리!

1.2 JOIN 활용 예제

예제에 활용되는 TABLE 1 : Student / TABLE 2: Class

[ 테이블 생성 코드 ]

-- Class 테이블 생성 (먼저 생성)
CREATE TABLE Class (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(50) NOT NULL,
    teacher VARCHAR(50) NOT NULL
);

-- Student 테이블 생성 (외래 키 포함)
CREATE TABLE Student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    grade VARCHAR(10) NOT NULL,
    class_id INT NULL,
    FOREIGN KEY (class_id) REFERENCES Class(class_id) ON DELETE SET NULL
);

-- Class 테이블 데이터 삽입
INSERT INTO Class (subject, teacher) VALUES
('수학', '이승기'),
('과학', '한지민'),
('영어', '김태희'),
('음악', '유재석');  -- 수업을 듣지 않는 경우를 위해 추가

-- Student 테이블 데이터 삽입
INSERT INTO Student (name, grade, class_id) VALUES
('김민수', '1학년', 1),
('이지은', '2학년', 2),
('박철수', '3학년', 1),
('최영희', '1학년', 3),
('정우성', '2학년', NULL);  -- 수업을 듣지 않는 학생

예제 1. 학생과 수업 정보를 결합하여 각 학생이 듣는 수업의 과목명과 담당 교사를 조회하세요.

SELECT s.student_id, s.name, s.grade, c.subject, c.teacher
FROM Student s
INNER JOIN Class c ON s.class_id = c.class_id;

[ 출력 결과 ]

두 테이블의 class_id가 일치하는 교집합 영역이 출력된 것을 확인(INNER JOIN)

예제 2. 모든 학생을 포함하여 수업 정보를 조회하고, 수업을 듣지 않는 학생도 포함되도록 하세요.

SELECT s.student_id, s.name, s.grade, c.subject, c.teacher
FROM Student s
LEFT JOIN Class c ON s.class_id = c.class_id;

[ 출력 결과 ]

S 테이블 컬럼은 전체가 출력되고, C는 class_id가 일치하는 값만 출력된 것을 확인(LEFT JOIN)

예제 3. 모든 수업을 포함하여 학생과 연결하고, 해당 수업을 듣는 학생이 없으면 NULL 값을 표시하세요.

SELECT s.student_id, s.name, s.grade, c.subject, c.teacher
FROM Student s
RIGHT JOIN Class c ON s.class_id = c.class_id;

[ 출력 결과 ]

C 테이블 컬럼은 전체가 출력되고, S는 class_id가 일치하는 값만 출력된 것을 확인(RIGHT JOIN)

2. UNION

두 개 이상의 테이블에서 데이터를 가져와 동일한 컬럼 구조로 하나의 결과 집합으로 결합하는 SQL 연산자

2.1 UNION의 특징**

- 컬럼 개수와 데이터 타입이 동일해야 한다.

- 중복 데이터를 자동 제거한다.

- 중복 데이터를 포함하려면 UNION ALL을 사용한다.

2.1 UNION 활용 예제

-- 국가별 마케팅 성과 테이블
CREATE TABLE marketing_korea (
    region VARCHAR(50),
    campaign_name VARCHAR(100),
    revenue DECIMAL(10,2)
);

CREATE TABLE marketing_usa (
    region VARCHAR(50),
    campaign_name VARCHAR(100),
    revenue DECIMAL(10,2)
);
## 컬럼 개수, 타입이 동일해야 UNION 사용 가능

-- 1) 한국 마케팅 캠페인 성과 데이터 삽입
INSERT INTO marketing_korea VALUES
('Korea', '겨울 할인 이벤트', 500000),
('Korea', '신규 회원 웰컴 이벤트', 300000),
('Korea', '특가 프로모션', 700000),
('Korea', '봄맞이 할인', 400000),
('Korea', '회원 전용 세일', 550000);

-- 2) 미국 마케팅 캠페인 성과 데이터 삽입
INSERT INTO marketing_usa VALUES
('USA', 'Winter Sale', 600000),
('USA', 'New Member Promo', 350000),
('USA', 'Exclusive Deal', 800000),
('USA', 'Spring Discount', 450000),
('USA', 'VIP Members Only', 600000);

--- 여러국가의 마케팅 성과 통합
SELECT region, campaign_name, revenue FROM marketing_korea
UNION
SELECT region, campaign_name, revenue FROM marketing_usa
ORDER BY revenue DESC;
# 국가별 마케팅 캠페인 데이터를 합치고 매출순으로 정리

--- UNION ALL을 사용하여 중복 포함
SELECT region, campaign_name, revenue FROM marketing_korea
UNION ALL
SELECT region, campaign_name, revenue FROM marketing_usa;

2.3 UNION 과 JOIN 비교

	데이터 결합 방식	컬럼 개수	중복 데이터	활용
JOIN	공통 키를 기준으로 좌우로 결합	테이블 간의 공통 키를 사용하여 여러 개의 컬럼을 조합	중복 데이터가 있을 경우 그대로 유지됨	주로 고객 정보, 마케팅 캠페인, 거래 데이터 등을 통합하여 보다 정밀한 분석을 수행할 때
UNION	여러 테이블의 데이터를 위아래로 합침	결합하려는 테이블의 컬럼 개수와 데이터 타입이 동일해야 함	기본적으로 제거됨 (UNION ALL 사용 시 제거 안 됨)	여러 마케팅 채널의 성과 데이터를 하나로 합칠 때 유용

고급 SQL - 집계 함수와 그룹화

1. 주요 집계 함수

그로스 마케팅에서의 활용

1. JOIN 활용

1.1 마케팅 비용 대비 매출 분석

예제 테이블 생성

-- 1) 마케팅 비용 테이블 (광고 캠페인별 지출 데이터)
CREATE TABLE marketing_spend (
    campaign_id INT PRIMARY KEY,
    campaign_name VARCHAR(100),
    ad_spend DECIMAL(10,2) -- 마케팅 비용
);

-- 2) 구매 테이블 (구매 내역 데이터)
CREATE TABLE purchases (
    purchase_id INT PRIMARY KEY,
    campaign_id INT,
    amount DECIMAL(10,2), -- 구매 금액
    purchase_date DATE,
    FOREIGN KEY (campaign_id) REFERENCES marketing_spend(campaign_id)
);

-- 1) 방문자 데이터 삽입 (유입 채널별 방문자 ID 포함)
INSERT INTO user_tracking VALUES
(1, 101, 'Google Ads', '2024-02-01'),
(2, 102, 'Google Ads', '2024-02-01'),
(3, 103, 'Facebook Ads', '2024-02-02'),
(4, 104, 'Facebook Ads', '2024-02-02'),
(5, 105, 'Instagram Ads', '2024-02-03'),
(6, 106, 'Instagram Ads', '2024-02-03'),
(7, 107, 'Instagram Ads', '2024-02-03'),
(8, 108, 'YouTube Ads', '2024-02-04'),
(9, 109, 'YouTube Ads', '2024-02-04'),
(10, 110, 'YouTube Ads', '2024-02-04'),
(11, 111, 'YouTube Ads', '2024-02-04');

-- 2) 구매 데이터 삽입 (일부 방문자가 실제로 구매한 데이터)
INSERT INTO purchases VALUES
(201, 101, 50000, '2024-02-02'), -- Google Ads 방문자 1명 구매
(202, 103, 75000, '2024-02-03'), -- Facebook Ads 방문자 1명 구매
(203, 105, 120000, '2024-02-04'), -- Instagram Ads 방문자 1명 구매
(204, 110, 90000, '2024-02-05'); -- YouTube Ads 방문자 1명 구매

marketing_spend 테이블(마케팅 지출 데이터)과 purchases 테이블을 JOIN하여 캠페인별 투자 대비 수익 계산하기

SELECT u.channel, COUNT(DISTINCT u.user_id) AS visitors, COUNT(DISTINCT p.customer_id) AS buyers,
       (COUNT(DISTINCT p.customer_id) * 100.0 / COUNT(DISTINCT u.user_id)) AS conversion_rate
FROM user_tracking u
LEFT JOIN purchases p ON u.user_id = p.customer_id
GROUP BY u.channel;

2. UNION 활용

2.1 이메일과 SNS 캠페인 반응 데이터 통합

예제 테이블 생성

-- 1) 이메일 캠페인 반응 테이블
CREATE TABLE email_campaign (
    campaign_id INT PRIMARY KEY,
    customer_id INT,
    campaign_name VARCHAR(100),
    clicks INT
);

-- 2) SNS 캠페인 반응 테이블
CREATE TABLE sns_campaign (
    campaign_id INT PRIMARY KEY,
    customer_id INT,
    campaign_name VARCHAR(100),
    clicks INT
);

-- 1) 이메일 캠페인 데이터 삽입
INSERT INTO email_campaign VALUES
(101, 1, '겨울 할인 이벤트', 8),
(102, 2, '신규 회원 웰컴 이벤트', 5),
(103, 3, '특가 프로모션', 12),
(104, 4, '봄맞이 할인', 3),
(105, 5, 'VIP 고객 혜택', 10);

-- 2) SNS 캠페인 데이터 삽입
INSERT INTO sns_campaign VALUES
(201, 1, '인스타그램 광고', 20),
(202, 2, '페이스북 광고', 15),
(203, 3, '틱톡 바이럴', 25),
(204, 4, '유튜브 리뷰', 10),
(205, 5, '트위터 프로모션', 18);

이메일과 SNS 캠페인의 성과 데이터를 하나의 테이블로 통합하여 비교 분석하기

SELECT customer_id, campaign_name, clicks, 'Email' AS channel
FROM email_campaign
UNION
SELECT customer_id, campaign_name, clicks, 'SNS' AS channel
FROM sns_campaign;

" 오늘의 회고 "

오늘은 JOIN을 주로 배웠다. 테이블을 생성하고 데이터를 삽입하는 과정은 이제 제법 익숙해진것 같은데,,

오늘의 문제는 JOIN,,,, 힘들었다.. 수업 8교시가 다 끝날때가 되어서야 그나마 적응한 기분이었다...ㅜㅜ

쓰여진 코드를 해석하는 것 말고 혼자 JOIN을 활용한 코드를 짜라면 짤 수 있을까 싶었다..

그리고 SQL .. 자꾸 PANDAS 의 장점을 떠오르게 한다.. ㅋㅋㅋ

수업이 거듭할수록 점점 내용이 심화되어가는 기분인데 더 반복하고 더 연습해야겠다,..! 아자아자..\('_')/

모두들 화이팅이에요..