[TIL]_2025.03.04 본캠프 16일차 (2): 예제로 익히는 SQL 강의 6회차

1. JOIN 함수 복습

• JOIN 순서
  공통 컬럼 찾기 > 공통 컬럼 관계 찾기 > 알맞은 JOIN 함수선택하기

<Spotify의 ERD 예시>

• 테이블 종류
  fact table : 중심테이블로 측정값을 담고 있음
  테이블에 대한 외래키 포함
  dimension table : 팩트에 대한 설명 정보 포함
  모든 디멘젼테이블은 하나의 기본키 포함

• 테이블 적재(저장) 주기
  fact table : 대부분 시간에 따라서 기록 (기업마다 상이)
  Dimension Table : 필요에 의해 업데이트 (이벤트/업데이트 있을 경우)

• 테이블 적재 방식
  Truncate : 이전 데이터는 삭제됨. 갱신 정보가 필요한 테이블에 주로 사용
  Insert : 이전 데이터 유지, 데이터가 추가됨
  시간에 따른 데이터 흐름 파악 필요할 때 사용
  용량 이슈가 있음

• JOIN
  두 개 이상의 테이블을 수평결합하는 작업
  각 테이블은 규칙을 통해 나누어져 있기 때문에 필요에 따라 효율적으로 연결하는게 JOIN

• 3,5회차 문제 풀이
  3회차 문제 5번
  DISTINCT는 필수가 아님. DISTINCT를 안써도 서브쿼리에서 gamel_account_id가 중복값 제거되기 때문에

5회차 문제 1번 join 활용

# 정답 쿼리
select case when b.game_account_id is null then '결제안함' else '결제함' end as gb
, count(distinct a.game_account_id)as usercnt 
from(	select game_account_id
		from basic.users 
	)as a 
left outer join 
	(	select game_account_id 
		from basic.payment
	)as b
on a.game_account_id=b.game_account_id
group by case when b.game_account_id is null then '결제안함' else '결제함' end
;

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

결제를 하고 안하고를 계정으로 기준을 두는게 좋음. 기준 컬럼이 game_account_id이기 때문에.
정답쿼리에서 join에 서브쿼리를 사용하지 않아도 괜찮음.
정답과 내가 작성한 답의 차이는 정답에서는 본쿼리에서 case when을 사용함.

5회차 문제 2. join 응용1

# 정답 쿼리
select *
from(	select a.game_account_id, count(distinct game_actor_id) as actor_cnt, sum(pay_amount)as sumamount 
		from(	select game_account_id, game_actor_id 
				from basic.users 
				where serverno>=2
			)as a 
		inner join 
			(	select distinct game_account_id, pay_amount, approved_at
				from basic.payment
				where pay_type='CARD'
			)as b 
		on a.game_account_id=b.game_account_id 
		group by a.game_account_id
	)as a 
where actor_cnt>=2
order by sumamount desc

5회차 문제 3. join 응용2

# 정답 쿼리 
SELECT 
	serverno,
	round(avg(diffdate), 0)AS avgdiffdate
FROM
	(
		SELECT
			a.game_account_id,
			datediff(date_format(date2,('%Y-%m-%d')) , first_login_date) AS diffdate,
			serverno
		FROM
			(
				SELECT
					game_account_id,
					first_login_date,
					serverno
				FROM
					basic.users
			)AS a
		INNER JOIN 
				   (
				SELECT
					game_account_id,
					max(approved_at)AS date2
				FROM
					basic.payment
				GROUP BY
					game_account_id
			)AS c 
			ON
			a.game_account_id = c.game_account_id
		WHERE
			date2>first_login_date
	)AS d
WHERE
	diffdate >= 10
GROUP BY
	serverno
ORDER BY
	serverno DESC

• 틀린 쿼리 복습

SELECT serverno, round(avg(diffdate), 0) AS avgdiffdate
FROM
	(
		SELECT
			u.game_account_id, datediff(date_format(date2, '%Y-%m-%d'), first_login_date) AS diffdate, serverno
		FROM
			(
				SELECT
					game_account_id,
					first_login_date,
					serverno
				FROM
					users
			) AS u
		INNER JOIN
(
				SELECT
					game_account_id,
					max(approved_at) AS date2
				FROM
					payment
				GROUP BY
					game_account_id
			) AS p
		ON
			u.game_account_id = p.game_account_id
		WHERE
			p.date2 > u.first_login_date
	) AS c
WHERE diffdate >= 10
GROUP BY serverno
ORDER BY serverno desc;

subquery 안에 SELECT,FROM을 넣을 수 있다는 게 확실히 인지되지 않아서 접근을 잘못했었다.
subquery로 한 조건씩 작성하다보면 되는 것!
구조가 복잡하지만 차근차근 생각해보면 좋을 것 같다.