오늘은 드디어 join에 대한 개념 정리를 했다. 라이브 세션 때 들어서 대충 알고는 있었는데 뭔가 직접 풀지 못해서 머릿속에서 정리가 되지 않았다. 개념들이 머릿속에 둥둥 떠다니는 느낌이랄까 그래서 오늘 join관련된 강의를 다 수강하고 직접 실습해보는 시간을 가졌다.
일단 내가 생각했던 left join은 진짜 찐으로 A의 값만 가져오는 줄 알았는데 그런 개념이 아니었다. 다음 그림을 보자.
위 그림과 같이 왼쪽에 있는 정보만 추출하는 건 줄 알았는데, 아니었음. 쉽게 이야기 하자면 left join이란- 왼쪽 테이블의 모든 레코드+왼쪽 테이블과 일치되는 값만 반환되는 것(공통컬럼을 중심으로)이다. 그러니까. A의 값은 다 가져오고, B의 값 중에서 A와 B 둘 다 가지고 있는 공통컬럼에서 A에 해당하는 값만 가져온다는 뜻이다.. 유노웟아민..? 공통컬럼이 있으니 join을 할 수있는건데, 여기서 A의 테이블에 있는 공통컬럼의 값에 따라서 B의 값을 가져온다는 소리임. 예를 들어 공통컬럼으로 user_id라는게 있다고 치자. 근데 그 user_id가 A에서는 1,3,4,5가 있고, B에서는 1,2,3,4가 있다고 하자. 그렇다면 여기서 A를 기준으로(왼쪽으로 놓고) left join으로 한다면 뽑히는 user_id에는1,3,4,5가 될 것이다. 이것을 기준으로 B에서의 2는 뽑히지 않을 것임. 오른쪽은 다 반환되고 왼쪽은 일치되는 값만 반환되는 것! 이게 left join이다. 그래서 위와 같은 그림으로 표현한 것. 그냥 그림 그대로 A값만 가져오는것. 이 아닌 join을 했을 때의 경우에 대입해야 한다는 것.
+그리고 left join이랑 left outer join을 사용했을 때 똑같은 값이 나온다. 그냥 똑같은 거라고 생각하면 됨.
밑의 문제는 강의 마지막에 나온 것이다.
문제: 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
풀이.
SELECT restaurant_name, price_group,
case when avg_age<30 then 'age_group1'
when avg_age>=30 and avg_age<40 then 'age_group2'
when avg_age>=41 and avg_age<50 then 'age_group3'
when avg_age>=50 then 'age_group4'
end as age_group
from
(
SELECT restaurant_name, avg_age,
case when avg_price<=5000 then 'price_group1'
when avg_price<=10000 then 'price_group2'
when avg_price<=30000 then 'price_group3'
when avg_price>30000 then 'price_group4'
end as price_group
from
(
select f.restaurant_name, avg(f.price) avg_price, avg(c.age) avg_age
from food_orders f inner join customers c
on f.customer_id=c.customer_id
group by f.restaurant_name
) a
) b
내가 작성한 쿼리. 이것도 작동이 잘 되었다.
밑에는 정답 쿼리. case when 구문을 바로 밑에 또 써도 되는줄 처음 알게 되었다.
select restaurant_name,
case when price <=5000 then 'price_group1'
when price >5000 and price <=10000 then 'price_group2'
when price >10000 and price <=30000 then 'price_group3'
when price >30000 then 'price_group4' end price_group,
case when age <30 then 'age_group1'
when age between 30 and 39 then 'age_group2'
when age between 40 and 49 then 'age_group3'
else 'age_group4' end age_group
from
(
select a.restaurant_name,
avg(price) price,
avg(age) age
from food_orders a inner join customers b on a.customer_id=b.customer_id
group by 1
) t
order by 1
오호. 근데 내가 작성한 쿼리문도 맞았음. 다음에는 case when 구문을 이어서 작성해봐야겠다.
그리고 여기서 알게된 점: case when 구문 작성할 때는 then 뒤에 오는 새로운 이름에는 작은 따옴표를 붙여줘야 한다. 이것도 별칭과 같은 개념이라고 생각했는데 아닌가보다.
다음문제:
조건1) 알맞은 join 방식을 사용하여 users 테이블을 기준으로, payment 테이블을 조인해주세요.
조건2) case when 구문을 사용하여 결제를 한 유저와 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요.
조건3) gb를 기준으로 게임계정수를 추출해주세요. 컬럼 이름은 usercnt로 지정해주시고, 결과값은 아래와 같아야 합니다.
일단 내가 처음 작성한 쿼리문.
select case when p.pay_amount is not null then '결제함'
when p.pay_amount is null then '결제안함'
end as gb, count(p.game_account_id) usercnt
from users u left join payment p
on u.game_account_id =p.game_account_id
group by gb
근데 이렇게 작성하니까
결과값이 이렇게 나왔다.
일단 값도 틀리고 결제안함은 아예 값이 0이 나와버렸다.(왜그런거지,,?)
아무리 해도 잘 모르겠다.
#정답쿼리
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
;
그래서 정답 쿼리를 보았는데, 그 해설에 보니 '결제를 한 유저와 결제를 하지 않은 게임계정'에서 나는 그것을 pay_amount라고 생각했다. 하지만, 해설에서는 그냥 비어있는 값을 이용하여 game_account_id로 작성을 했다.
->나는 여기서 이해가 되지 않았음. 왜 아이디로 결제를 한 유저와 결제를 하지 않은 유저를 구분하는거지? 싶었다. 당연히 결제라고 하니까 나는 pay_amount를 사용한건데 또 아니었나보다 -> 그래서 튜터님께 찾아가서 여쭤봄.: pay_amount가 ''로 찍히는 경우가 있다고 함. 테이블을 뜯어보면 해당 컬럼 사용하지 않는 것이 맞다고 하심. ->그래도 이해가 되지 않음. 내가 궁금한 것은 왜 결제한 것과 안한것의 기준이 left join했을 때의 game_account_id의 null 값인지가 궁금했음. 그래서 다시 여쭤봄.-> '결제했다면 payment 테이블에 ID가 남기 때문입니다. (+공통컬럼)' 라고 대답해주심.
일단 틀렸던 내 쿼리문을 디버깅 해보자면 먼저는 똑같은 값이 있을 수 있기 때문에(예를 들어 한 사람이 결제를 여러번 했을 수도 있으니) distinct 함수를 써줘야 함.
select case when p.pay_amount is not null then '결제함'
when p.pay_amount is null then '결제안함'
end as gb, count(distinct p.game_account_id) usercnt
from users u left join payment p
on u.game_account_id =p.game_account_id
group by gb
이렇게 되면 결제함의 값은 알맞게 나온 것을 볼 수 있다. 그러면 여기서 결제안함 의 값은 도대체 왜 0이 나오는 걸까?
->정답은 count(distinct p.game_account_id) usercnt 에 있었다. 지금 users테이블과 payment테이블을 left join으로 결합해주었다. 그렇다면 온전히 남아있는 데이터는 users테이블이라는 소리다. 그렇다면! payment테이블이 아닌 users테이블에서 데이터를 가져와야 하는 것이다.
그렇게 바꾸게 되면 올바른 결과값이 나온다.
select case when p.pay_amount is not null then '결제함'
when p.pay_amount is null then '결제안함'
end as gb, count(distinct u.game_account_id) usercnt
from users u left join payment p
on u.game_account_id =p.game_account_id
group by gb
이게 고친 값.
실행했을 때 나온 값.
그리고 나서 payment를 사용하면 안된다고 했으니 p.pay_amount를 p.game_account_id로 바꾼다.
select case when p.game_account_id is not null then '결제함'
when p.game_account_id is null then '결제안함'
end as gb, count(distinct u.game_account_id) usercnt
from users u left join payment p
on u.game_account_id =p.game_account_id
group by gb
그러면 동일한 값이 나오고 쿼리도 깔끔하게 완성된다.
=오늘 이 문제를 풀면서 느낀점은 나는 게임을 아예 하지 않는 사람인데, 이 문제가 다 게임관련 문제라서 문제를 풀 때에도 어느정도 그 분야의 도메인 지식을 필요로 한다는 것을 깨달았다. 결제를 하려면 어쨌든 계정이 있는 것이니까. 이 값은 조금 더 확실하다고 해야할까,,,튜터님께서 말씀하신 것처럼 결제했다면 payment 테이블에 ID가 남기 때문이다.
쉽지않았지만 그래도 오늘은 join에 대해서 스스로 개념정리와(left join,inner join)문제를 이해하면서 풀었다는것이 큰 수확이다. 늦은시간까지 정말 고생했다 나자신! 오늘 잘 자고 내일도 화이팅이다!!
내일은 나머지 문제 다 풀기!, 코드카타 문제도 풀어보기
