예제로 익히는 SQL 5회차 정답

1. JOIN 활용

SELECT CASE WHEN p.pay_amount IS 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 1

첨에 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 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
;

 

결제한 유저와 결제하지 않은 유저를 조회해야 하므로 INNER JOIN 사용

INNER JOIN을 하게 되면 결제한 유저만 조회됨

2. JOIN 응용1

SELECT a.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 (SELECT *
		FROM users
		WHERE serverno>=2
		) u
	LEFT JOIN
		(SELECT *
		FROM payment
		WHERE pay_type='CARD'
		) p
	ON u.game_account_id=p.game_account_id
	GROUP BY u.game_account_id
	) a
WHERE a.actor_cnt>=2
ORDER BY sumamount DESC

# 정답 쿼리
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

 

조건1에서 '모두를 만족하는 경우' 라고 했으니까 INNER JOIN 써야 함...!

으이그

 

3. JOIN 응용2

SELECT serverno,
	CONVERT(AVG(diffdate), SIGNED) AS avgdiffdate
FROM(
	SELECT u.game_account_id,
		DATEDIFF(DATE_FORMAT(p.date2, '%Y-%m-%d'), u.first_login_date) AS diffdate,
		u.serverno
	FROM 
		(SELECT game_account_id,
				first_login_date,
				serverno
		FROM users
		) u
	INNER JOIN
		(SELECT game_account_id,
			MAX(approved_at) AS date2
		FROM payment
		GROUP BY game_account_id
		) p
	ON u.game_account_id=p.game_account_id
	WHERE u.first_login_date<p.date2
	) a
WHERE diffdate>=10
GROUP BY a.serverno
ORDER BY a.serverno DESC

# 정답 쿼리 
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