[TIL] SQL 4회차 세션 & SQL 과제 풀이

UNION과 JOIN
UNION

• 서로 다른 테이블을 수직으로 결합하는 것.

UNION 기본 구조
SELECT 컬럼1, 컬럼2
FROM TABLE A
UNION (ALL)
SELECT 컬럼1, 컬럼2
FROM TABLE B

UNION: 결합한 결과에서 중복되는 행은 하나만 표시
UNION ALL: 결합한 결과에서 중복되는 행을 모두 표시

JOIN

• 다른 테이블 중 조건이 맞는 컬럼을 수평으로 결합하는 것.

JOIN 기본 구조

JOIN: TABLE A INNER JOIN TABLE B
ON A.공통컬럼=B.공통컬럼
두 테이블에서 일치하는 값을 가진 행을 반환

LEFT JOIN: TABLE A LEFT JOIN TABLE B
ON A.공통컬럼=B.공통컬럼
왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환합니다. 일치하는 항목이 없으면 오른쪽 테이블의 열에 대해 NULL 값이 출력됩니다.

RIGHT JOIN: TABLE A RIGHT JOIN TABLE B
ON A.공통컬럼=B.공통컬럼
오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행을 반환합니다. 일치하는 항목이 없으면 왼쪽 테이블의 열에 대해 NULL 값이 출력됩니다.

FULL OUTER JOIN: LEFT JOIN + UNION + RIGHT JOIN
LEFT, RIGHT 의 모든 데이터를 출력합니다. 각각의 빈 값은 NULL 로 반환됩니다.

! JOIN을 활용 할 때 서브쿼리 2개 활용하는 것 연습하기 !

SQL 문제 1.

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

풀이 내용.
1. USER 테이블 기준 PAYMENT 테이블 조인 = JOIN 방식 상관없이 USER 테이블을 왼편에 위치 (기준 테이블)
2. CASE WHEN 구문을 사용해 결제 한 유저, 하지 않은 게임 계정 구분 = 결제를 하거나,하지않은 유저 모두 표시해야하기에 LEFT JOIN으로 USER 테이블의 모든 컬럼이 나올 수 있도록 ! 결제를 하지 않은 유저는 NULL로 표시
3. gb를 기준으로 게임계정수 추출 -> ~를 기준으로 = GROUP BY 절 사용

정답
SELECT IF(pay_amount IS NULL,'결제안함','결제함') AS gb,
COUNT(DISTINCT mu.game_account_id) AS usercnt
FROM marketer_sql_users mu
LEFT JOIN marketer_sql_payment mp
ON mu.game_account_id = mp.game_account_id
GROUP BY gb

되짚어보기
1. 조건 잘 읽기. IF절이 아니라 CASE WHEN절 사용했어야 함.

• IF절 활용 방안 계속 암기하기 ! IF(조건,참일 때 값, 거짓일 때 값)

SQL 문제 2.

조건1) users 테이블에서 서버번호가 2 이상인 데이터와 payment 테이블에서 결제방식이 CARD 모두를 만족하는 경우를 알맞은 방식으로 join 해 주세요. payment 테이블의 매출 금액이 중복되는 것을 방지하기 위해 모든 값을 고유하게 추출해야 합니다.

조건2) 조인한 결과를 바탕으로 users 테이블의 game_account_id 를 기준으로 game_actor_id수를 중복값없이 세고 컬럼 이름을 actor_cnt로 지정해주세요. 또한 pay_amount 값을 더해주시고, 컬럼 이름을 sumamount로 지정해주세요.

조건3) having 을 사용하지 않고, 인라인 뷰 subquery 사용으로 actor_cnt수가 2 이상인 경우만 추출해주세요. 그리고 sumamount를 기준으로 내림차순 정렬해주세요.
결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

풀이 내용.
1. USER 테이블의 서버 번호가 2 이상인 데이터와 결제 방식이 CARD를 모두 만족 = 모두 만족하는 조건이면 INNER JOIN으로 NULL값이 없도록 !
2. 모든 값을 고유하게 추출 = DISTINCT로 중복 방지
3. 조인 결과를 바탕으로 ~를 기준으로 중복값 없이 = GROUP BY + DISTINCT
4. ~값을 더하다 = SUM(~)
5. 인라인 뷰 쿼리 = FROM절 뒤에서 쓰이는 테이블 역할
6. actor_cnt 2 이상, 내림차순 정렬 = actor_cnt >= 2 , ORDER BY ~ DESC

정답
SELECT a.game_account_id,
a.actor_cnt,
a.sumamount
FROM
(
SELECT mu.game_account_id,
COUNT(DISTINCT game_actor_id) AS actor_cnt,
SUM(pay_amount) AS sumamount
FROM marketer_sql_users mu
INNER JOIN marketer_sql_payment mp
ON mu.game_account_id = mp.game_account_id
WHERE serverno >= 2
AND pay_type ='CARD'
GROUP BY game_account_id
ORDER BY sumamount DESC
) AS a
WHERE actor_cnt>=2

SQL 문제 3.

조건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일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

▲
재복습 필요!