11.21(금)(sql 3일차)

JOIN 문제풀기 / WITH 배움!

문제 1

트레이너가 보유한 포켓몬들은 얼마나 있는지 알 수 있는 쿼리를 작성해주세요

참고

보유했다의 정의는 status가 Active, Training인 경우를 의미
Released는 방출했다는 것을 의미

답:

# 문제 : 1. 트레이너가 보유한 포켓몬들은 얼마나 있는지 알 수 있는 쿼리를 작성해주세요
-- 참고
-- - 보유했다의 정의는 status가 Active, Training인 경우를 의미
-- - Released는 방출했다는 것을 의미

# 쿼리를 작성하는 목표, 확인할 지표 : 보유한 / 포켓몬 수 / status active, training의 전체 수를 구하자!/ 조건문 / 
# 쿼리 계산 방법 : count, group by
# 데이터의 기간 : 
# 사용할 테이블 : trainer_pokemon, pokemon
# Join KEY : pokemon_id = name
# 데이터 특징 : 

SELECT
  tp.trainer_id,
  p.kor_name as pokemon_name,
  COUNT(status) as pokemon_cnt
FROM basic.trainer_pokemon as tp
LEFT JOIN basic.pokemon as p
ON tp.pokemon_id = p.id
GROUP BY 
  trainer_id, kor_name, status
HAVING
  tp.status IN ('Active', 'Training')
ORDER BY trainer_id ASC

# 피드백
# 1. GROUP BY에 들어가는 컬럼은, SELECT에서 집계함수(count,max,min 등)을 제외한 나머지가 들어간다!
# 2. HAVING을 쓰는 것도 좋지만, 데이터를 미리 줄인 상태에서 분석하는게 더 좋다! => WHERE로 바꾸기!
# 3. JOIN KEY는 합칠 테이블을 보고, 공통된 컬럼 값을 도출해 낸 후 적는거다! 헷갈릴 수가 없음!

# 피드백 후 코드

SELECT
  tp.trainer_id,
  p.kor_name as pokemon_name,
  COUNT(status) as pokemon_cnt
FROM basic.trainer_pokemon as tp
LEFT JOIN basic.pokemon as p
ON tp.pokemon_id = p.id
WHERE
	tp.status IN ('Active', 'Training')
GROUP BY 
  trainer_id, kor_name
ORDER BY trainer_id ASC

# 1번 문제의 version 2 풀이
# JOIN한 후에 WHERE
  # SQL 연산을 할 때, 데이터의 양이 중요함
  # 적은 양의 데이터 => 연산이 더 빠름
  # trainer_pokemon에 status로 먼저 데이터를 줄이고, 그 후에 pokemon과 JOIN!
  # trainer_pokemon에 Active, Training

WITH base AS (
  SELECT
    pokemon_id AS pokemon_id,
    trainer_id,
    status
  FROM basic.trainer_pokemon
  WHERE
    status IN ("Active", "Training")
)

SELECT
  tp.trainer_id AS trainer_id,
  tp.pokemon_id AS pokemon_id,
  p.kor_name AS pokemon_name,
  COUNT(tp.pokemon_id) AS cnt
FROM base AS tp
LEFT JOIN basic.pokemon AS p 
ON tp.pokemon_id = p.id
GROUP BY 
  trainer_id, 
  pokemon_id, 
  pokemon_name

문제 2

각 트레이너가 가진 포켓몬 중에서 'Grass' 타입의 포켓몬 수를 계산해주세요 (단, 편의를 위해 type1 기준으로 계산해주세요)

답:

# 문제 : 각 트레이너가 가진 포켓몬 중에서 'Grass' 타입의 포켓몬 수를 계산해주세요
#.     (단, 편의를 위해 type1 기준으로 계산해주세요)
# 쿼리를 작성하는 목표, 확인할 지표 : 트레이너가 가지고 있는 포켓몬 = status active, training / type1 = grass / count pokemon
# 쿼리 계산 방법 : 
# 데이터의 기간 : 
# 사용할 테이블 : trainer_pokemon, pokemon, trainer
# Join KEY : pokemon_id, trainer_id
# 데이터 특징 : 

SELECT
  t.name,
  count(p.id) as Grass_cnt
FROM basic.trainer_pokemon as tp
LEFT JOIN basic.pokemon as p
ON tp.pokemon_id = p.id
LEFT JOIN basic.trainer as t
ON tp.trainer_id = t.id
WHERE
  p.type1 = 'Grass' AND
  tp.status IN ('Active', 'Training')
GROUP BY 
  t.name

문제 3

트레이너의 고향(hometown)과 포켓몬을 포획한 위치(location)를 비교하여, 자신의 고향에서 포켓몬을 포획한 트레이너의 수를 계산해주세요.

참고

status 상관없이 구해주세요

답:

# 문제 : 3. 트레이너의 고향(hometown)과 포켓몬을 포획한 위치(location)를 비교하여, 자신의 고향에서 포켓몬을 포획한 트레이너의 수를 계산해주세요.
#.   참고
#.   - status 상관없이 구해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 조건 고향 = 위치 / COUNT 트레이너 수!
# 쿼리 계산 방법 : trainer hometown = trainer_pokemon location / count(trainer id)
# 데이터의 기간 : 
# 사용할 테이블 : trainer_pokemon, trainer
# Join KEY : trainer_id = id
# 데이터 특징 : 

SELECT
  COUNT(t.id) as home_cation
FROM `basic.trainer` AS t
LEFT JOIN basic.trainer_pokemon AS tp
ON t.id = tp.trainer_id
WHERE
  t.hometown = tp.location
  
# 검토해보니, 한 트레이너가 여러번 잡으면 중복된 id가 생길거고, left join 하고 where을 쓴다는게 inner join으로 하면 되지 않나? 라는 생각!
ㅡㅡㅡㅡㅡ검토 후ㅡㅡㅡㅡㅡㅡㅡㅡ  

# 문제 : 3. 트레이너의 고향(hometown)과 포켓몬을 포획한 위치(location)를 비교하여, 자신의 고향에서 포켓몬을 포획한 트레이너의 수를 계산해주세요.
#.   참고
#.   - status 상관없이 구해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 조건 고향 = 위치 / COUNT 트레이너 수!
# 쿼리 계산 방법 : trainer hometown = trainer_pokemon location / count(trainer id)
# 데이터의 기간 : 
# 사용할 테이블 : trainer_pokemon, trainer
# Join KEY : trainer_id = id
# 데이터 특징 : 

SELECT
  COUNT(DISTINCT t.id) as home_cation
FROM `basic.trainer` AS t
INNER JOIN basic.trainer_pokemon AS tp
ON t.id = tp.trainer_id
WHERE
  t.hometown = tp.location

문제 1

문제 2

문제 3

문제 4