[SQL 3회차 숙제] join 문제

 

# 문제 설명

 

테이블은 다음과 같다

users 테이블 - 회원 유저 테이블

payment 테이블 - 회원 결제 테이블

 

< 문제 1 >

 

case when 구문과 join 함수를 사용하여, users 테이블을 기준으로, 결제를 한 유저와 결제를 하지 않은 유저를 추출해주세요.결과값은 아래와 같아야 합니다.

 

 

 

< 핵심 개념 >

 

1. count 와 sum의 차이점

count와 sum을 헷갈리지 말자!!

 

# count

테이블의 행의 수

- count(*)

null값까지 포함해서 개수를 센다

 

- count(컬럼) 

null값을 제외하고 개수를 센다

 

 

# sum

테이블의 열 합계

sum(컬럼), avg(컬럼), min(컬럼), max(컬럼) 형식으로 써야 한다

어떤 컬럼에 대해서 계산을 수행할 것인가를 지정해야 한다

 

※ count(컬럼), sum(컬럼)에서의 컬럼은 group by 컬럼이 아니다

그룹별로 집계할 컬럼을 지정할 때는 반드시 group by 부분에다가 사용해야 한다

 

 

2. 조인문에서

from

left join을 할때

 

서브쿼리로 원하는 컬럼만 가져온다면 더 효율적인 쿼리문이 될 수 있다

 

[ 전체 ]

from users as u
left join payment as p
on u.game_account_id = p.game_account_id

 

[ 필요한 것만 ]

from(	select game_account_id
		from basic.users 
	)as a 
left join 
	(	select game_account_id 
		from basic.payment
	)as b
on a.game_account_id=b.game_account_id

 

 

3. 습관적으로 group by를 쓰는 것이 아니라 group by를 왜 써야하는지 생각해보기

 

group by는 같은 값을 하나의 그룹으로 묶어서 

집계 함수를 사용하기 위해 쓰는 것이다

 

 

4. GROUP BY를 사용할 때 일반적으로 SELECT절에 집계함수를 제외한 모든 컬럼을 GROUP BY에 명시해주어야 합니다

 

ex) 바른 예시
SELECT  id, insta_id, COUNT(price)
FROM a
GROUP BY id, insta_id

 

ex) 틀린 예시 --> 이렇게 마음대로 쓰시면 원하는 값과 다른 값이 나옵니다.
SELECT  id, insta_id, COUNT(price)
FROM a
GROUP BY id

 

ex) 틀린 예시 --> 이렇게 마음대로 쓰시면 원하는 값과 다른 값이 나옵니다.
SELECT  id, COUNT(price), insta_id
FROM a
GROUP BY id

 

 

5. users 테이블 내에서 회원 수를 집계할때 중복된 데이터 값이 있을 수 있으므로

distinct를 사용하였다

 

 

# 정답 코드

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 join 
	(	select game_account_id 
		from basic.payment
	)as b
on a.game_account_id=b.game_account_id
group by 1

 

 

< 문제 2 >

 

- 서버번호가 2 이상인 데이터와 결제방식이 CARD 인 경우를 join해 주시고
- game_account_id 를 기준으로 game_actor_id 갯수를 중복값없이 세어주시고, actorcnt 으로 컬럼명을 명시해주세요.
- pay_amount 값을 더해주시고, sumamount 으로 컬럼명을 명시해주세요.
- having 을 사용하지 않고, subquery 사용으로 game_actor_id 갯수가 2 이상인 경우만 추출해주세요.

 

결과값은 아래와 같아야 합니다

 

 

 

# 핵심 개념

 

1. from ( 서브쿼리 ) as 별칭은

a,b,c .. 이렇게 간단하게 써줄 주 있다

 

굳이 모든 컬럼에

a.컬럼, b.컬럼 이런식으로 쓸 필요는 없고

중복되는 컬럼에 한해서만 써줘도 충분하다

 

 

2. 중요한 서브쿼리문은

 

1) where 컬럼 in (서브쿼리)

 

2) from ( 서브쿼리 ) as 별칭

 

이 있는 것을 기억하자

 

 

# 정답 코드

select *
from
(
	select a.game_account_id,
		   count(distinct game_actor_id) as actorcnt,
		   sum(pay_amount) as sumamount
	from ( 	select game_account_id, game_actor_id
			from users
			where serverno >= 2 
		 ) as a
	inner join 
		 ( 	select game_account_id, pay_amount
		 	from payment 
		 	where pay_type = 'CARD'
		 ) as b
	on a.game_account_id = b.game_account_id
	group by a.game_account_id
) a
where actorcnt >= 2

 

 

< 문제 3 >

 

- user 테이블에서 game_account_id, date, serverno 를 추출한 데이터와 매출 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 join 을 진행해주세요.
- 그 다음, datediff 함수를 사용해 결제일자-접속일자를 구해주세요. 그리고 컬럼이름을 *diffdate로 설정해주세요.* 두 날짜의 형식은 같아야 합니다.
- 마지막으로, 인라인 뷰 subquery 를 이용하여  서버별 평균 diffdate를 구해주세요. 
다만, 평균 datediff 컬럼은 정수 형태로 출력되어야 합니다.  또한, 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. (전체결과 중 일부입니다.)

 

 

 

# 핵심 개념

 

1. ` ` 컬럼명이 date와 같을때 백틱 잘 붙이기

 

2. 문제 요구사항 잘 따르기

 

 

# 정답 코드

 

select serverno,
	   round(avg(diffdate)) as avgdiffdate
from
(
	select a.game_account_id,
		   datediff(date_format(date2, '%Y-%m-%d'), `date`) as diffdate,
		   serverno
	from  (  select game_account_id, `date`, serverno
			 from users 
		  )  as a
	inner join
		  (  select game_account_id, max(approved_at) as date2
		     from payment 
		     group by game_account_id
		  )  as b
	on a.game_account_id = b.game_account_id
)  as a
where diffdate >= 10
group by serverno
order by serverno desc