DataRosie의 일과 삶

Join은 sql의 시작과 끝

실무를 하다보면 결국 join (inner join)과 left join 을 가장 많이 쓰게 되는데 실제 데이터와 쿼리를 보면서 어떤식으로 결과가 나오는지 확인해본다. 

데이터는 Join을 이해하기 쉽도록 포인트 적립과 사용에 대한 데이터를 예제로 사용해본다.

데이터 준비 

- write (DDL) 쿼리가 안되는 환경을 고려해서 다음과 같이 데이터를 넣는다. 

상황 : AA, BB 두명의 유저가 있고 포인트를 쌓거나 사용하고 있으며 각각 get_point, use_point에 로그를 남긴다. . 

point 테이블 : 간단하게 idx(index, Primary Key값, 고유번호를 의미하는 컬럼)만 넣은 테이블

get_point 테이블 : 포인트 적립 (p_idx, idx, uid, point) , p_idx로 point.idx와 엮을 수 있다.

use_point 테이블 : 포인트 사용 (p_idx, idx, uid, point) , p_idx로 point.idx와 엮을 수 있다.

*원래 idx , p_idx , uid, point 순서가 더 자연스러운데 중간에 수정하다보니.. 글을 전체 수정하기 어려워서 이대로 감.

with point as (
select     1  IDX union all
select     2  union all
select     3  union all
select     4  union all
select     5  union all
select     6  union all
select     7  union all
select     8  union all
select     9  union all
select     10  union all
select     11  union all
select     12  union all
select     13  union all
select     14  union all
select     15  union all
select     16 
)

,get_point as (
select    1 p_IDX, 1  IDX,    'AA'  UID,   1000 POINT union all
select    2 ,    2    ,     'AA'   ,      1500 union all
select    3 ,    3    ,     'BB'   ,      1000 union all
select    4 ,    4    ,     'AA'   ,      1000 union all
select    6,     5    ,     'BB'   ,      2000 union all
select    7,     6    ,     'AA'   ,      500 union all
select    10,    7    ,     'BB'   ,      5000 union all
select    11,    8    ,     'AA'   ,      10000 union all
select    15,    9    ,     'BB'   ,      3000 union all
select    16,   10    ,     'AA'   ,      5000 
)

,use_point as (
select   5 p_IDX,  1  IDX,    'AA'  UID,   -1500 POINT union all
select   8,      2    ,       'AA'   ,     -200 union all
select   9,      3    ,       'BB'   ,      -100 union all
select   12,     4    ,     'AA'   ,      -100 union all
select   13,     5    ,     'BB'   ,      -2800 union all
select   14,     6    ,     'AA'   ,      -500 
)

데이터 확인

• point 테이블

select * from point p

•  get_point 테이블

select * from get_point gp

• use_point 테이블

select * from use_point up

inner join 결과

이때, 만약 전체를 join 한다면?

select * from point p 
join get_point gp on gp.p_idx = p.idx
join use_point up on up.p_idx = p.idx

=> 아무것도 나오지 않는다.

호옥..시 point.idx별로 join 이 되어서 전체 적립/사용 테이블이 나오길 기대했나? 

아니다. 그걸 보기 위해서는 Left join 으로 처리해야한다. 

left join 결과

select * from point p
Left join get_point gp on p.idx=gp.p_idx 
left join use_point up on p.idx=up.p_idx

이렇게 했더니 원하는 것을 볼 수 있게 되었다. 

case when 사용해서 정리하기

위 쿼리의 결과로 나온 테이블은 뭔가 정리가 안 되어 있음. 원하는것에 아직 한발 부족함. 

그럴때는 case when을 사용함. 

먼저 위의 Left join 두개 사용한 쿼리를 With 문으로 서브쿼리로 만들어 주고, 해당 테이블을 다시 사용하면서 case 문을 이용하는 방식

,tbl as (
select p.idx, 
      gp.idx gp_idx, gp.p_idx gp_p_idx,  gp.uid gp_uid, gp.point gp_point, 
      up.idx up_idx, up.p_idx up_p_idx,  up.uid up_uid, up.point up_point
from point p 
left join get_point gp on p.idx=gp.p_idx 
left join use_point up on p.idx=up.p_idx 
)


select tbl.idx,
      case when gp_idx is not null then gp_idx else up_idx end gu_idx, 
      case when gp_idx is not null then gp_point else up_point end point,
      case when gp_idx is not null then gp_uid else up_uid end uid
 from tbl

그럼 요렇게 나온다. 

종종 누적집계 함수가 헷갈린다..
- 빅쿼리에도 쓸수 있음. mysql 버전으로 작성

- sum over
예제

- gender, day가 나오면서 score_points 를 누적 집계 하려면

# Write your MySQL query statement below
select gender, day, sum(score_points) over( partition by gender order by gender, day) as total 
from Scores group by 1,2 order by 1,2

- 누적집계할 컬럼을 sum() 안에 넣어준다 => sum(score_points)
- over() 안에 값을 입력
-크게는 gender 별로 누적집계를 보고 싶은 것이니 partition by gender (누적되어 나오는 마지막 값은 성별 누적값을 보고 싶다는 뜻.)
-누적 집계는 순서가 중요함. order by 다음에 gender, day 로 날짜별로 누적되어 나오게 설정.

결과

꿀팁
Sum over 쓸때 partition by 를 안해도 되고 (이때는 전체 테이블이 Order by 기준으로만 누적집계 되어 나옴)
order by 가 안들어가도 됨 (거의 쓸일 없을듯? 뭐.. 한달 급여 테이블이 있고 사원이 있고 지불된 총 한달 급여를 보고 싶으면 쓰겠지만 웬만해서는 Order by 가 필요할 것 같음.)