[강의 수강 13일차] Join 문 연습 문제 풀기

오늘은 Join에 대해서 연습해보고자 한다. 

실제 현업에서는 Join을 굉장히 많이 쓰는 데, 분석을 하고자 하는 데 계산과 전처리할 때 "Join"을 사용하면 유용하기 때문이다.

 

그리고 처리량을 줄이면서 가는 데에 있어 특히나 Join이 활용되고

서브쿼리나 임시 테이블들끼리의 Join을 하면 처리 속도와 내가 원하고자 하는 것들을 빠르게 추출할 수 있다.

 

보유한 포켓몬 수를 트레이러별로 확인하기 

# 1. 트레이너가 보유한 포켓몬들은 얼마나 있는 지 알 수 있는 쿼리를 작성해주세요
# 쿼리를 작성하는 목표, 확인할 지표 : 포켓몬들(이름 명시)이 얼마나 있는 지 알고 싶음 -> 포켓몬의 수 
# 쿼리 계산 방법 : trainer_pokemon + pokemon Join -> 그 후 Group by 집계
# 데이터 기간 : x
# 사용할 테이블 : trainer_pokemon, pokemon
# Join Key : trainer_pokemon, pokemon
# 데이터 특징
-- 참고) 보유했다는 정의는 status가 Active, Training인 경우 의미 / Released는 방출

-- 1) trainer_pokemon에서 status 가 Active, Trainin인 경우 필터링(WHERE)
	-- 1)을 먼저 하는 것이 좋을까? 혹은 Join을 하고 그 후에 Active, Training 을 필터링 하는 게 좋을까?
    -- Join을 할 테이블을 일단 줄이고(ROW 수를 줄인다) 그 후 Join을 한다
    -- 연산량 관점에서 먼저 줄이고 JOIN이 효율적
    -- WHERE 조건을 하지 않고 JOIN 한 후 WHERE 조건 건다면 처리량이 많아짐 
	-- 핵심 : Table을 그래도 사용해야 하는 가? 혹은 줄이고 쓰는 게 내 목적에 맞는 가가 우선이다.
-- 2) 필터링한 결과를 pokemon Talbe과 JOIN
-- 3) 2)의 결과에서 pokemon_name, COUNT(pokemon_id) AS pokemon_cnt 


SELECT
    -- 테이블 확인하는 과정
    -- tp.*,
    -- p.id,
    -- p.kor_name
    kor_name,
    COUNT(tp.id) AS pokemon_cnt 
FROM
    (SELECT
        id,
        trainer_id,
        pokemon_id,
        status
    FROM basic.trainer_pokemon
    WHERE
        status IN ("Active", "Training")
    )
AS tp
LEFT JOIN basic.pokemon AS p
ON tp.pokemon_id = p.id
-- SELECT FROM (JOIN) WHERE GROUP BY 
GROUP BY 
	kor_name
ORDER BY
	pokemon_cnt DESC

✔️ Join할 때 자주 나올 수 있는 에러 <Column name id is ambiguous at> : id가 모호하다. 더 구체적으로 말해달라는 뜻!

이 경우에는 JOIN에서 사용하는 테이블들에 중복된 컬럼의 이름이 있다면 어디에서 가져올 지 컬럼 명시 필수! 

 

✔️ 추가 TIP 

where 1=1 을 추가하고 그 뒤 and status = "Active" / and status = "Training" 과 같이 조건에 따라 값 확인할 때 유용!

쿼리를 작성할 때 값을 바꿔가며 실행해야 하고 빠른 주석 처리를 위해 앞에서 무조건 TRUE인 1=1 을 넣고,

AND 쓰고 빠른 주석 처리해가며 확인하는 방법! WHERE 로 결과에서 필터링이 되어야 하면 먼저 데이터를 줄이는 것이 좋다.

 

트레이너가 가진 포켓몬 중 'Grass' 타입 포켓몬 수 계산하기

 

# 쿼리를 작성하는 목표, 확인할 지표: 트레이너가 보유한 포켓몬 중에서 Grass 타입의 포켓몬 수를 알고 싶다!
# 쿼리 계산 방법 : 트레이너가 보유한 포켓몬 조건 설정 => Grass 타입으로 WHERE 조건 걸어서 COUNT 
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon, pokemon
	-- 어느 것을 왼쪽에 둘까? = 우리가 풀려고 하는 문제에서 어떤 테이블이 기준이 되어야 할까?
    -- trainer_pokemon 테이블 : 트레이너가 잡은 히스토리가 저장된 테이블, 트레이너가 보유했던(보유한) 포켓몬 얼마나 있는 지 알려줌
    -- pokemon : 포켓몬의 메타 정보, 상품은 고정되어 있고 그 상품을 주문하면서 주문이 생김 
    	-- pokemon 테이블은 모든 포켓몬 정보 저장됨
    -- pokemon을 왼쪽에 두면 => pokemon 중 보유되지 않았던 포켓몬들은 trainer_pokemon에 없을 것 null 값 표기
    -- trainer_pokemon을 왼쪽에 두면 => 트레이너가 보유했던 포켓몬들을 기반으로 포켓몬 데이터만 추가, null 값 생성 x 
# Join Key : trainer_pokemon.pokemon_id = pokemon.id 
# 데이터 특징 : 1번 문제와 동일

SELECT
	-- tp.*
    p.type1,
    COUNT(tp.id) AS pokemon_Cnt 
FROM (
	SELECT
    	id,
        trainer_id,
        pokemon_id,
        status
    FROM basic.trainer_pokemon
    WHERE
    	status IN ("Active", "Training")
) AS tp
LEFT JOIN basic.pokemon AS p
ON tp.pokemon_id = p.id
WHERE 
	type 1 = "Grass" 
GROUP BY
	type 1
ORDER BY
	2 DESC # 2 대신 pokemon_cnt도 가능

✔️ Join 할 때 왼쪽에 두어야 할 테이블 기준

기준이 되는 테이블은 내가 구하고자 하는 데이터가 어디에 제일 잘 저장되어있는 가 파악이 핵심!

즉, 데이터 추출할 때 어떤 데이터를 중점으로 보고자 하는 가에 따라 달라진다.  

Join을 할 수 있는 key 같아 보이는 것이 많은 테이블을 왼쪽으로 두는 경우가 높다 (예외 있음) 

 

트레이너 고향과 포켓몬 포획한 위치 비교해 자신의 고향에서 포켓몬 포획한 트레이너 수 계산하기 

# 쿼리를 작성하는 목표, 확인할 지표: 트레이너 고향과 포켓몬 포획 위치 같은 트레이너 수 
# 쿼리 계산 방법 : trainer(hometown), trainer_pokemon(location) JOIN => hometown = location 트레이너 수 COUNT 
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon, trainer 
# Join Key : trainer_pokemon.pokemon_id = trainer.id 
-- 어디를 왼쪽에? trainer_pokemon 
-- trainer엔 특정 트레이너의 정보가 1개 들어있음 
-- > Join을 하다보면 Right에서 Left의 기준에 여러개가 있을 때 데이터 많아지는 것처럼 보임 > 이는 trainer_pokemon에 각 트레이너별 포켓몬 여러 마리 가지고 있어서 결과 합쳐진 것
-- LEFT에 메타 정보를 두면 헷갈릴 수 있음  
# 데이터 특징 : status와 상관없이 구하기 

SELECT
	COUNT(DISTINCT tp.trainer_id) AS trainer_uniq #트레이너 수 = 28명
	--COUNT(tp.trainer_id) AS trainer_cnt, # 트레이너와 포켓몬이 같은 건이 43개 
FROM basic.trainer AS t
LEFT JOIN basic.trainer_pokemon AS tp 
ON t.id = tp.trainer_id 
WHERE
	tp.location IS NOT NULL -- 없는 경우는 제외하기 
	AND t.hometown = tp.location 
-- WHERE current_health IS UNLL 
-- trainer 중에 포켓몬 잡아보지 못한 trainer 가 있으면 null 조건 걸어줘야 함
-- 현재 테이블에는 trainer 모두 포켓몬 잡아봐서 신경 쓰지 않아도 되나

 

✔️ WHERE 조건에 NULL 값은 제외하고 계산을 해야 할 경우에는 포함시켜서 추출해준다. 

 

Master 등급인 트레이너들, 어떤 타입의 포켓몬 가장 많이 보유했는 지 구하기 

# 쿼리를 작성하는 목표, 확인할 지표: Master 등급 트레이너들이 가장 많이 보유하고 있는 타입  
# 쿼리 계산 방법 : trainer + pokemon + trainer_pokemon => Master 조건 설정(Where) => type1 GROUP BY + COUNT 
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon, trainer, pokemon 
# Join Key : trainer_pokemon.trainer_id = trainer.id, pokemon.id=trainer_pokemon.pokemon_id
	-- 2번 나오는 trainer_pokemon을 LEFT 조인
# 데이터 특징 : x


SELECT
	type1,
    COUNT(tp.id) AS pokemon_cnt 
FROM (
	SELECT
    	id,
        trainer_id,
        pokemon_id,
        stauts
     FROM basic.trainer_pokemon
     WHERE 
     	status IN ("Acive", "Training")
) 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
	t.achievement_level = "Master" 
GROUP BY
	type1
ORDER BY
	2 DESC

 

✔️ 수의 경우, 오름차순 & 내림차순을 하게 되면 인지 차원에서 데이터를 직관적으로 이해할 수 있으니 추출할 때 신경 쓰면 좋을 듯! 

✔️ LEFT JOIN은 연속해서 2번 사용할 수 있다. (N번 사용 가능) 

 

Incheon 출신 트레이너들은 1세대, 2세대 포켓몬의 보유 수 구하기 

# 쿼리를 작성하는 목표, 확인할 지표: Incheon 출신 트레이너들이 보유하고 있는 포켓몬 중 세대 구분 하고 싶다. 
# 쿼리 계산 방법 : trainer + trainer_pokemon + pokemon => Incheon 조건 설정(Where) => generation GROUP BY + COUNT 
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon, trainer, pokemon 
# Join Key : trainer_pokemon.trainer_id = trainer.id, pokemon.id=trainer_pokemon.pokemon_id
	-- 2번 나오는 trainer_pokemon을 LEFT 조인
# 데이터 특징 : 보유의 정의 

SELECT 
	generation,
    COUNT(tp.id) AS pokemon_cnt 
FROM(
	SELECT
    	id,
        trainer_id,
        pokemon_id,
        stauts
     FROM basic.trainer_pokemon
     WHERE 
     	status IN ("Acive", "Training")
) AS tp
LEFT JOIN baisc.trainer AS t
ON tp.trainer_id = t.id
LEFT JOIN pokemon AS p
ON p.id=tp.pokemon_id
WHERE 
	t.hometown = "Incheon"
    -- 만약 여러 세대가 있는 경우, generation IN (1,2)라는 조건 걸어주기! 
GROUP BY
	generation

 

✏️ 이번 강의의 포인트는 테이블에 어떤 데이터들이 있는 지 알고 > 내가 원하는 정보들을 합쳐서 볼 수 있는 것이 포인트!

즉, 데이터가 어디 있는 지를 알고 / 어떤 id로 연결 지을 수 있는 지를 파악하는 게 핵심이다. 

 

✏️이번 강의와 학습을 통해 느낀점과 배운점 
✅ 조인 쿼리를 작성하고 실행하기 전이 '결과를 예측해보는 과정'이 중요한 이유는 작성한 쿼리가 원하는 겨로가와 일치하는 지 확인하고 문제를 찾기 위함이다. ➡️ 모든 예측하면서 시나리오를 그리는 과정을 거치면 좋다.