팀스터디

기초분석

발표대본 초본 작성

• 도입
  • 이커머스 데이터 선택
  • 그 중에서도 VIP 유저 선정
    → 행동분석 진행 / 이탈방지를 위한 가상의 해결방안 수립
  • 이커머스란?
  • VIP(=큰손) 선정 기준
    • 구매횟수+구매금액 종합적으로 고려
      1~5점으로 나눠 점수 부여 후 합산점수가(>=5)인 사람
• 경향성 분석
  • 월별 장바구니 제거 횟수
    12월 증가
    11월에 이벤트가 있었음을 추론 가능
  • 최근 두 달에 비해 구매 횟수가 줄어든 고객o
    다만, VIP 내 기준이므로 전체적으로 충성도가 높음을 확인할 수 있음
  • 선호 브랜드
    • 브랜드가 없는 제품들이 대부분
      중저가 브랜드를 취급하는 쇼핑몰로 추론 가능
      가성비 좋은 제품을 원하는 고객층으로 구성되어 있음
    • 상위 10개 브랜드 중 저가 브랜드 다수
• 해결방안
  • 리텐션마케팅 진행
    • 재구매 유도, 쿠폰 발송 등
  • 충성도 프로그램 운영
• 기대효과
  • 기존 고객 유치
  • 쇼핑몰 성장
  • 신규 고객 유입

개인스터디

예제로 익히는 SQL 4,5회차

UNION

수직결합구조
두 개 이상도 결합 가능

기본구조

# 컬럼 순서가 같고, 그 형식이 같아야 함
 
select 컬럼1, 컬럼2, 컬럼3.. 
from 테이블명1
union (all) #수직결합 명시
select 컬럼1, 컬럼2, 컬럼3..
from 테이블명2 

구분	UNION	UNION ALL
공통점	두 테이블을 수직결합	두 테이블을 수직결합
차이점	중복된 행을 하나로 표기(중복제거하고 표기)	UNION ALL 은 모두 표현(중복을 제거하지 않고 표기)

주의점✅

• 열의 갯수와 순서가 모든 쿼리에서 동일해야 합니다.
• 두 테이블의 컬럼 이름이 일치해야 합니다.
• 위 두 가지의 조건을 만족할 경우, UNION 과 UNION ALL 은 2개 이상의 테이블도 결합할 수 있습니다.

JOIN

수평결합구조
두 개 이상도 결합 가능
이름이 달라도 됨✅

기본구조

# JOIN 기본 구문
# 공통컬럼이 1개인 경우

select 컬럼1, 컬럼2.
from 테이블 a
join 
select 컬럼1, 컬럼2..
from 테이블 b
on a.공통컬럼=b.공통컬럼

---------------------------------------------------------------------------
# JOIN 기본 구문
# 공통컬럼이 2개 이상인 경우

select 컬럼1, 컬럼2..
from 테이블 as a
join 
select 컬럼1, 컬럼2..
from 테이블 as b
on a.공통컬럼=b.공통컬럼 and a.공통컬럼2=b.공통컬럼2 

단계

1️⃣ 공통컬럼 찾기

테이블A: 나는 이름컬럼과 나이컬럼이 있어.
테이블B: 나는 성별컬럼과 결제금액컬럼이 있어.
➡️ JOIN 불가 공통컬럼 없음

테이블A: 나는 이름컬럼과 나이컬럼이 있어.
테이블B: 나는 이름컬럼과 나이컬럼과 국가컬럼이 있어.
➡️ 공통컬럼 이름, 나이
조건 1개 : 이름이 같은 경우를 확인하고 싶은 경우
조건 2개 : 이름과 나이가 같은 경우를 확인하고 싶은 경우

2️⃣ 공통컬럼 관계찾기(PK와 FK찾기)

• PK
  • 기본키 / NULL일 수 없음 / 유일한 값
  • 모든 데이터를 식별하는 기준이 되는 컬럼
  • 테이블 당 하나의 기본키만 가질 수 있음
• FK
  • 외래키 / 다른 테이블의 PK와 연결되어 테이블간 관계를 나타내주는 컬럼
  • 기준이 되는 컬럼(PK)를 확인하기 위한 연결컬럼
    

3️⃣ 적절한 조인 방식 찾기

INNER JOIN

두 테이블에서 일치하는 값을 가진 행 출력(교집합)

# INNER JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블명1 a
inner join 테이블명2   
on a.공통컬럼=b.공통컬럼

복잡한 예✅
구조적으로 작성하기
문제가 있을 때 안쪽에서부터 바깥쪽으로 검증(정합성 체크)

LEFT JOIN

COUNT와 같이 많이 사용하게 됨

• ’LEFT 를 기준으로 위쪽에 작성하는 테이블’
  ==’LEFT에 위치해 있는 테이블’
  ==’LEFT JOIN 에서 기준이 되는 테이블’

기준이 되는 테이블은, 조인 조건을 만족해도/만족하지 못해도 모두 출력

• ‘LEFT 를 기준으로 아래쪽에 작성하는 테이블’
  ==’RIGHT 에 위치해 있는 테이블’
  ==’LEFT JOIN 에서 조건에 따라 출력되는 테이블’
  RIGHT에 위치한 테이블은, 조인 조건을 만족하는 경우 출력, 만족하지 못할경우 NULL 값으로 출력

# LEFT JOIN 작성법(기초편)
select 컬럼1, 컬럼2... 
from 테이블1 as 테이블명1
left join basic.theglory2 as 테이블명2   
on a.공통컬럼=b.공통컬럼

복잡한 예✅
구조적으로 작성하기
문제가 있을 때 안쪽에서부터 바깥쪽으로 검증(정합성 체크)

RIGHT JOIN

현업에서 잘 사용하지 않음
기준을 LEFT랑 반대로 둔다고 이해하면 될듯

FULL OUTER JOIN

합집합
현업에서 잘 사용하지 않음
모든 데이터를 보고싶을때 사용 MYSQL 지원❌
FULL OUTER JOIN = LEFT JOIN + RIGHT JOIN

UNION ALL과 동일하다고 생각하면 됨

UNION VS JOIN

필요에 따라 UNION과 JOIN 함께 사용 가능

숙제

문제 1 - JOIN 활용

• 조건1) 알맞은 join 방식을 사용하여 users 테이블을 기준으로, payment 테이블을 조인해주세요.
• 조건2) case when 구문을 사용하여 결제를 한 유저와 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요.
• 조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.
  • 힌트) 기준이 되는 테이블의 데이터는 그대로 두어야겠죠?

SELECT CASE WHEN p.pay_type IS NOT NULL THEN '결제함'
	        ELSE '결제안함'
	        END AS gb,
	   COUNT(DISTINCT u.game_account_id) AS usercnt
FROM users u 
LEFT JOIN payment p 
ON u.game_account_id = p.game_account_id 
GROUP BY gb;

문제 2 - JOIN 응용1

• 조건1) users 테이블에서 서버번호가 2 이상인 데이터와 payment 테이블에서 결제방식이 CARD 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요.
• 조건2) 조인한 결과를 바탕으로 users 테이블의 game_account_id 를 기준으로 game_actor_id수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 pay_amount 값을 더해주시고, 컬럼 이름을 sumamount로 지정해주세요.
• 조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요. 그리고 sumamount를 기준으로 내림차순 정렬해주세요.

SELECT game_account_id,
	   actor_cnt,
	   sumamount 
FROM
	(
	 SELECT u.game_account_id,
		    COUNT(DISTINCT u.game_actor_id) AS actor_cnt,
	   		SUM(p.pay_amount) AS sumamount
	 FROM users u 
	 LEFT JOIN payment p 
     ON u.game_account_id = p.game_account_id 
	 WHERE u.serverno >= 2 AND p.pay_type = 'CARD'
	 GROUP BY u.game_account_id
	 )sub
WHERE actor_cnt >= 2
ORDER BY sumamount DESC;

문제3 - JOIN 응용2

• 조건1) user 테이블에서 game_account_id, first_login_date, serverno 를 추출한 결과와
• 조건2) payment 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join 을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.
• 조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.
• 조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을avgdiffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.
• 조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요.
• 힌트) 소수점을 반올림해주는 round 함수를 활용해주세요!

SELECT serverno, round(avg(diffdate))
FROM
	(
	SELECT u.game_account_id, 
	  		u.first_login_date, 
	   		u.serverno,
	   		DATEDIFF(p.date2, u.first_login_date) AS diffdate
	FROM users u 
	INNER JOIN 
	 (
	  SELECT p.game_account_id,
	  	     MAX(p.approved_at) AS date2
	  FROM payment p
	  GROUP BY p.game_account_id
	  )p 
	ON u.game_account_id = p.game_account_id
	WHERE p.date2 > u.first_login_date)sub
WHERE diffdate >= 10
GROUP BY serverno
ORDER BY serverno DESC

데일리퀘스트

SQL - NULL 처리하기
SQL - 경기도에 위치한 식품창고 목록 출력하기
SQL - 강원도에 위치한 생산공장 목록 출력하기
SQL - DATETIME에서 DATE로 형 변환

---

일기

• SQL 코드카타 15-18✅ 숙제제출✅
• 기초분석스터디 발표대본초본✅
• 데이터리터러시 1-1강✅

오늘은 그래도 개인시간이 조금 생겨서 SQL 숙제를 미리 해뒀다
3/4까지긴 한데 개인강의도 추가로 지급됐고, 다음 주에는 파이썬 개인과제라 정신이 없을 것을 미리 대비🥲

조원분들이랑도 그렇지만 타 조원분들이랑도 스몰톡하는 시간이 늘었다 다른 조 상황은 어떤지 많이 물어봤었는데 다들 비슷비슷한 것 같아서 위안이 되기도 했다 그리고 얘기하면서 물어보고 배우고 하는 게 많아서 참 좋다😗💞💞

기초분석은 상당히 .. 힘든 과제였다 처음부터 분석 방향이나 쿼리를 잘 짜야겠다는 생각이 많이 들었음 내가 짜둔 쿼리가 쓰이지 않거나 뭔가 무의미한 결과가 도출돼서 그래프 폐기하게 되는 게 너무 슬펐음🥴

그리고 내가 원하는 것에 비해 내 쿼리 실력이 따라주지 않는다는 것을 절실히 느끼게 되었음 ㅎ 그래서 아직 제대로 배우지 못한 WITH 절이 너무너무 궁금하고요

숙제 하면서도 느꼈지만 이미 정해진 답에서 쿼리를 짜는 건 쉬운 것 같은데, 정답이 없는 데이터 속에서 무언가를 찾아내는 건 어려워서 연습이 많이 필요할 것 같다고 생각했다

데이터 리터러시 강의가 이 시기에 지급된 건 정말 레전드 타이밍
제게 정말 필요한 거에요 예예.. 상대적으로 SQLD 준비할 시간이 없어서 조금만 더 고민해보고 취소할듯🤣 치킨 사먹었다 치겠습니다