5회차 과제

1. 프로그래머스 코딩테스트 '오랜 기간 보호한 동물(1)'

SELECT
  ai.name
  , ai.datetime
FROM
  animal_ins ai LEFT JOIN animal_outs ao USING(animal_id)
WHERE
  ao.datetime IS NULL
ORDER BY
  ai.datetime
LIMIT 3
;

해설:

select name, datetime 
from(   select animal_id, datetime, name 
        from animal_ins
    )as a 
left outer join # inner join 을 쓰게되면, 들어오고 and 입양된 동물이 출력되므로 사용할 수 없음 
    (   select animal_id 
        from animal_outs 
    )as b 
on a.animal_id=b.animal_id 
where b.animal_id is null #out 에 animal_id 가 있지 않은 경우. 즉, 입양을 가지 못한 동물 필터링 
order by datetime #datetime 이 작은 것부터 
limit 3 # 최대 3개까지 출력 

• inner join 이 아닌, left join을 사용하였는지가 중요한 key point 였습니다.
• 해당 문제는 반드시 left join 을 사용해야 합니다. 동물보호소에 들어온 동물의 테이블이 있고, 입양된 동물들의 테이블이 있습니다. 따라서 동물보호소에 들어왔지만 입양되지 못한 동물 = 들어온 동물 - 입양된 동물이 됩니다.
이를 구현하기 위해서는, left join 을 사용하여 두 테이블을 결합해 준 뒤, out table 에 animal_id is null 조건을 통해 아직 남아있는 동물 친구들의 리스트를 확인해주어야 합니다.
핵심은 where b.animal_id is null

2. 프로그래머스 코딩테스트 '조건에 맞는 사용자와 총 거래금액 조회하기'

SELECT
  ugb.writer_id
  , ugu.nickname
  , SUM(ugb.price) as total_sales
FROM
  used_goods_board ugb JOIN used_goods_user ugu ON ugb.writer_id = ugu.user_id
WHERE
  ugb.status = 'DONE'
GROUP BY
  ugb.writer_id
HAVING
  `total_sales` >= 700000
ORDER BY
  `total_sales`
;

해설

select b.user_id, nickname as NICKNAME, sum(price)as TOTAL_SALES
from(   select WRITER_ID, price 
        from used_goods_board
        where STATUS='DONE'#반드시 대소문자 구분해서 사용. 반드시 입력된 컬럼값 그대로 호출해야함 
    )as a
inner join # 중고거래 게시판에 있고 거래상태가 완료된 건. 두 테이블을 교집합
    (   select user_id, nickname
        from used_goods_user
    )as b 
on a.WRITER_ID=b.user_id 
group by b.user_id, nickname
having TOTAL_SALES>=700000
order by TOTAL_SALES asc 

• 중고거래 게시판에 거래상태가 완료된 건의 닉네임을 가져오기 위해서는 inner join 이 적합합니다. left join 으로도 구현 할 수 있지만, 쿼리가 길어질 수 있습니다. inner join의 간단한 예에서 다뤄보았듯, join 시 where 절에 조건이 없다면 간단한 방식으로 join 함수를 사용할 수 있습니다.
• 중고거래가 완료된 건에 한하여, 유저별 총 결제액을 구할 수 있습니다. 여기서 유저는 기준이되고, 총 결제액은 sum 이라는 집계함수를 사용하여 구할 수 있습니다.
• 집계된 데이터를 가지고 두가지 형태로 답을 도출할 수 있습니다. having 절을 사용하여 group by 의 결과값을 필터링할 수 있습니다. 또한, having 절을 사용하지 않을 경우, 인라인 뷰 서브쿼리를 이용하여(마치 하나의 테이블처럼 기능) 원하는 데이터를 추출할 수 있습니다.
LEFT JOIN을 사용하고 싶다면 WHERE절로 추가 조건 WHERE user_id IS NOT NULL 걸어주면 됨

3. 프로그래머스 코딩테스트 '보호소에서 중성화된 동물'

SELECT
  ai.animal_id
  , ai.animal_type
  , ai.name
FROM 
  animal_ins ai 
  LEFT JOIN animal_outs ao 
  USING(animal_id)
WHERE 
  (ai.sex_upon_intake LIKE 'Intact%') 
  AND (ao.sex_upon_outcome NOT LIKE 'Intact%')
ORDER BY
  ai.animal_id
;

해설:

select a.animal_id, animal_type, name
from(   select animal_id , animal_type, name
        from animal_ins 
        where SEX_UPON_INTAKE like('%Intact%')
    )as a
inner join 
    (   select animal_id 
        from animal_outs 
        where (SEX_UPON_OUTCOME like ('%Spayed%')) or (SEX_UPON_OUTCOME like ('%Neutered%'))
    )as b
on a.animal_id =b.animal_id

• 조건에 맞는 데이터를 추출하고 조인이 필요한 문제였습니다. 앞선 강의에서 inner join 의 두가지 작성법을 살펴보았습니다. 해당문제에서는 각 테이블에서 중성화 여부가 조건으로 들어가므로 join 함수 사용시, subquery 형태로 감싸주어 구문을 작성해주어야 합니다.
• like 구문은 ‘~와 같은’ 이라는 의미로 생각해주세요. ‘%CAT%’ 을 예로 들면, 앞과 뒤에 %를 붙여줌으로써, 앞뒤에 어떠한 문자가 있어도 상관없이 CAT 이라는 문자열이 포함된 값들을 출력한다는 의미입니다.

4. JOIN 활용

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

조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.

힌트: 기준이 되는 테이블의 데이터는 그대로 두어야겠죠?

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

해설:

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
;

• left join 과 case when 의 활용이 필요한 문제였습니다. left join 은 left 에 위치한 테이블, 즉 기준이 되는 테이블의 값을 모두 출력되지만, right 에 위치한 테이블은 left 에 없을 경우 null 로 출력됩니다. 이를 활용하여, null 로 출력되는 경우를 ‘결제안함’ 으로, null 값이 아닌 경우 ‘결제함’ 이라는 값을 넣어 줄 수 있습니다. 이와같이 case when 과 left join 은 현업에서 주로 사용되는 방식이예요.
• 예를 들어 이벤트를 참여한 유저와 참여하지 않은 유저의 잔존율 비교, 결제금액 비교 등을 할 수 있겠죠 ?

5. JOIN 응용 1

조건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를 기준으로 내림차순 정렬해주세요.
결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

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

→ 실제 유저가 카드로 결제한 금액은 payment 테이블에서 구해진 값인데 왜 결과값을 (한 유저가 소유한 캐릭터 개수*해당 유저가 카드로 결제한 금액)으로 부풀린 값이 나오게 계산하라고 하셨을까?
: 답변 받았는데 그 부풀려지는 숫자를 설명하려고 일부러 문제 설정을 하신 거라고 함

-- 조건 생각하지 않고 최대한 간단하게 쿼리 써 보기
SELECT 
  u.game_account_id
  , count(DISTINCT u.game_actor_id) as actor_cnt
  , sum(DISTINCT p.pay_amount) as sumamount
FROM 
  basic.users u
  INNER JOIN basic.payment p
  ON u.game_account_id = p.game_account_id 
  AND u.serverno >= 2
  AND p.pay_type = 'card'
GROUP BY
  u.game_account_id 
ORDER BY 
  SUM(p.pay_amount) DESC
;

문제 더 깊게 이해하기: b4uttm5s-ygjn-ree0-z1db-8cmv8lkihs3d 유저 데이터를 확인

SELECT 
  u.game_account_id
  , u.game_actor_id as actor_cnt
  , p.pay_amount as sumamount
FROM 
  basic.users u
  INNER JOIN basic.payment p
  ON u.game_account_id = p.game_account_id 
  AND u.serverno >= 2
  AND p.pay_type = 'card'
WHERE
  u.game_account_id = 'b4uttm5s-ygjn-ree0-z1db-8cmv8lkihs3d'
;

위를 보면 알 수 있다시피 actor_cnt와 sumamount 모두 중복이 있으므로 이를 제거해야 함 → actor_cnt에서만 중복값을 제거하면 제시된 결과값과 같은 값이 출력됨!

해설:

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

• subquery 형태의 inner join 활용이 필요한 문제였습니다. join 함수를 기준으로 left, right 테이블의 where 조건에 모두 조건이 존재합니다. 따라서, join 함수를 쓰실 때 subquery 의 형태로 작성해 주어야 합니다. left join 역시 사용될 수 있으나 쿼리가 길어질 수 있습니다.
• 매출 금액이 중복되는 것을 방지하기 위해, 아래와 같은 구문이 사용되었습니다.
distinct game_account_id, pay_amount, approved_at ← 이는 distinct 뒤의 세 컬럼을 하나의 덩어리로 인식하고, 이 하나의 덩어리를 고유하게 추출해줍니다. 실제로 데이터의 중복 적재가 일어나는 경우를 방지하기 위해 많이 사용됩니다. (고객은 1번 결제했는데 똑같은 데이터가 2번 기록되면, 매출이 2배로 잘못 찍힘을 방지)
• 계정별로 캐릭터수와 결제금액을 추출하기 위해 count 와 sum 집계함수가 사용되었습니다. 또한 기준이 되는 a.game_account_id 가 group by 뒤에 작성되었습니다.
• 앞서 계산한 결과를 인라인 뷰 서브쿼리로 감싸주었습니다. 그 다음, where 조건을 통해 캐릭터 수가 2개 이상인 경우를 필터링 진행했습니다.

6. JOIN 응용 2

조건1) user 테이블에서 game_account_id, first_login_date, serverno 를 추출한 결과와

SELECT
  game_account_id
  , first_login_date 
  , serverno 
FROM 
  basic.users 

조건2) payment 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join 을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.

SELECT 
  *
FROM (
  SELECT
    game_account_id
    , first_login_date 
    , serverno 
  FROM 
    basic.users 
  ) AS u
  JOIN (
    SELECT
      game_account_id
      , MAX(approved_at) AS date2
    FROM
      basic.payment
    GROUP BY
      game_account_id
  ) AS p
  USING(game_account_id)
WHERE 
  u.first_login_date < p.date2
;

조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.

SELECT 
  u.game_account_id
  , u.serverno
  , DATEDIFF(p.date2, u.first_login_date) AS diffdate 
FROM (
  SELECT
    game_account_id
    , CONVERT(first_login_date, date) AS first_login_date 
    , serverno 
  FROM 
    basic.users 
  ) AS u
  JOIN (
    SELECT
      game_account_id
      , MAX(CONVERT(approved_at, date)) AS date2
    FROM
      basic.payment
    GROUP BY
      game_account_id
  ) AS p
  USING(game_account_id)
WHERE 
  u.first_login_date < p.date2

조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을avgdiffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.

조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요. 결과값은 아래와 같아야 합니다. 전체결과 중 일부입니다.

힌트) 소수점을 반올림해주는 round 함수를 활용해주세요!

SELECT 
  serverno
  , ROUND(AVG(diffdate), 0) AS avgdiffdate
FROM (
	SELECT 
	  u.game_account_id
	  , u.serverno
	  , DATEDIFF(p.date2, u.first_login_date) AS diffdate 
	FROM (
	  SELECT
	    game_account_id
	    , CONVERT(first_login_date, date) AS first_login_date 
	    , serverno 
	  FROM 
	    basic.users 
	  ) AS u
	  JOIN (
	    SELECT
	      game_account_id
	      , MAX(CONVERT(approved_at, date)) AS date2
	    FROM
	      basic.payment
	    GROUP BY
	      game_account_id
	  ) AS p
	  USING(game_account_id)
	WHERE 
	  u.first_login_date < p.date2
    ) AS id
WHERE 
  diffdate >= 10
GROUP BY 
  serverno
ORDER BY 
  serverno DESC
;

• - operator 사용하면 답이 달라지는 이유: 1, 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

• 집계함수, 다중 서브쿼리, 조건절, date연산이 필요한 문제였습니다.
• 다중 서브쿼리에서, 가장 안쪽의 서브쿼리부터 연산되는 것을 인지해야 합니다. 쿼리의 가장 안쪽으로부터 바깥쪽으로 작성하며, 최종 결과값을 도출할 수 있어야 합니다.
• 우선 매출 테이블에서 계정이라는 기준을 두고 MAX 라는 집계함수를 사용해 주었습니다. 이에 group by 절을 사용하여 select 절에서 계정별 마지막 결제일자를 도출할 수 있습니다.
• 이를 기반으로 inner join 을 수행하고, 그 다음 where 절에서 date2>first_login_date 를 만족하는 경우만 가져오게 됩니다. SQL 은 JOIN 다음 WHERE 을 수행합니다.
•해당 쿼리에서는 두 날짜 컬럼의 표현 방식이 달라, 이를 맞춰주는 DATE_FORMAT 함수가 사용되었습니다. 결제일자-접속일자 계산을 위해, 날짜의 차이를 계산해주는 DATEDIFF 함수가 사용되었습니다.
• 전체 연산의 결과를 인라인 뷰 서브쿼리로 감싸주었고, 이름을 d로 지정해 주었습니다. 이 다음, where 절에서 diffdate가 10 이상인 경우만 필터링 해 주었습니다. 해당 구문은 인라인 뷰 서브쿼리가 아닌 having 으로도 구현 가능합니다.