[TIL-260409] 멋쟁이사자처럼 부트캠프 그로스마케팅 4기 - 데이터분석 개론 day21

▼오늘 배운 사항들
1. sql
- join, 서브쿼리, 윈도우함수

 

Part 1. SQL - join

• join 여러함수 연결해서 분석
• 서브쿼리
• 윈도우함수 순위를 매기거나 
  ▶ 오늘 수업 이후 광고 들어온 사람 중에 몇 명이 구독했나 알수있음

[Join]

• join을 배울때는 여러개 테이블 연결
• 사용자 행동 이벤트 테이블
  foreign key : user id를 레퍼런스를 받겠다 = users테이블의 userid를 레퍼런스 연결하겠다
  ->내가 하고싶던거!!
• join이 필요한 이유 : 테이블을 연결해야 어떤 경로로 들어온 사용자가 어떤 활동이 많은가 분석이 가능해짐
  ▶ 한 테이블 안에 다 넣으면 비효율적이니 각각의 테이블에 나눠서 쌓고 연결만
• 엮이고 나서는 데이터 삭제가 어려움 주의
• join종류는 벤다이어그램 집합을 생각하면 쉬움

foreign key -- references를 통한 ERD 변화를 확인하세요!

 

 

 

[join 문법]

• join 문장에서 두 테이블을 어떤 컬럼으로 연결할 것인지 조건으로 쓰는 것

SELECT 테이블A.컬럼, 테이블B.컬럼
FROM 테이블A
JOIN 테이블B ON 테이블A.공통컬럼 = 테이블B.공통컬럼;

 

[실습] 사용자 정보와 이벤트 합치기 inner join

- 유저아이디부터 이벤트 타입까지 한번에 볼 수 있어졌습니다~

테이블을 합칩니다.

 

join 문법을 이해하기 위한 발버둥

 

1. users: 출석부 (모든 학생 이름이 있음)
2. user_events: 급식 먹은 명단 (급식 먹은 학생만 이름이 적힘)
3. INNER JOIN: 급식 먹은 학생 명단과 출석부를 대조해 오늘 급식 먹은 학생만 뽑기.
4. LEFT JOIN: 출석부 학생 옆에 급식 기록을 다 붙여보기. (안 먹은 애들은 옆 칸이 비어 있음)
5. WHERE IS NULL: 그중에서 옆 칸이 비어있는 학생만 골라내기 → "오늘 급식 안 먹은 학생" 발견!

[실습 3 - 추천 코드 발행한 사용자 정보 확인]

- 정우진님이 보낸 추천인코드 중 1명은 24-4-20에 사용했고 1명은 사용하지 않았습니다

 

[실습 5. 가입경로별 업그레이드 수 집계

• distinct 
• group by
• event type

 

- group by user id하고 카운트 열 추가하니까 유저아디당 이벤트 몇개일어났는지 확인가능

- 이 데이터를 가지고 pandas에서 막대그래프를 그린다던지 등 활용

유저아이디당 이벤트 몇 개 일어났는지 카운트할 수 있음

 

---

Part 2. 서브쿼리

• 평균 예산보다 높은 캠페인을 알고 싶다 // 데이터가 바뀌면 where절 매일 기입해야되니 번거롭 -함수설정과 유사한듯
• 동점 오류 가능성을 없애기 위해 서브쿼리 이용

서브쿼리 값이 일치할 땐 = 사용

 

• 서브쿼리의 값이 목록일 시 in 사용해서 추출
•  '어떤 이벤트를 한 사람' 조회 → 조회한 사람의 '정보' 조회

업그레이드한 사람 조회하기

 

 

[실습 10번. 한 번도 업그레이드 안한 무료 유저 찾기 : 리마케팅 타겟 리스트]

• users 테이블에서 이름, 가입채널, 플랜, 가입일, 이메일을 뽑아냄
• 그 들 중 플랜이 무료이고 유저아이디가 업그레이드를 한번도 하지 않았던 사람(서브쿼리) 으로 조건 걸어주면
• 리마케팅 타겟을 할 사람들이 두둥

 

 

• 양수가 높을수록 전환수가 높은 캠페인이겠지
• 서브쿼리로 테이블을 만들 수 있고, 만든 서브쿼리를 join으로 붙일 수 있음

 

※  event type 테이블까지 보여주고싶으면, left join하고 서브쿼리 사용

SELECT u.user_id, u.username, e.event_type
FROM users u
LEFT JOIN user_events e ON u.user_id = e.user_id
WHERE u.plan = 'free'
AND u.user_id NOT IN (
SELECT DISTINCT user_id
FROM user_events
WHERE event_type = 'upgrade'
);

---

Part 3. 윈도우 함수

• 원래 행 유지하면서 순위, 누적합계 같은 추가 계산 값 붙이는 함수 ---???😵‍💫
• 3월 누적합계 8,280,000원이군!
• 어제 매출과 비교해서 오늘 얼마나 늘었는지 보기 / 어제 매출과 오늘과 어제 매출 증감액

 

갈수록 최악인데...오늘 역대급 노답이다🥹 

sqld 취득이 가능한건가 쿼리..쿼쿼 쿼리...쿼리쿼리.....