[개발 일지] 엑셀 보다 쉬운, SQL _ 3주차

[ 학습 목표 ]

여러 테이블의 정보를 연결하는 Join을 이해한다.

연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습한다.

아래 위로 결과를 연결하는 Union을 공부한다.

 

 

<문법>

Join

:두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것

Join에는 Inner join  과  Left join이 존재한다.

 

 

(1) Left join 

A와 B는 각각 테이블을 의미한다.

A에 B를 붙이는 것 

 

어디에 무엇을 붙일지가 가장 중요하다.

 

 

 

 

 

(2) Inner join 

Inner join은 두 테이블의 교집합이다. 

 

 

Union

필드는 같은데, 이어보고 싶을 때 붙여주는 기능 

 

 

 

예를 들어, 7월과 8월의 필드가 있다.

문득, 이 두 필드를 합쳐보고 싶어졌다.

 

select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1

inner join checkins c2 on c1.course_id = c2.course_id

inner join orders o on c2.user_id = o.user_id

where o.created_at >= '2020-08-01'

group by c1.title, c2.week

order by c1.title, c2.week

 

select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1

inner join checkins c2 on c1.course_id = c2.course_id

inner join orders o on c2.user_id = o.user_id

where o.created_at >= '2020-08-01'

group by c1.title, c2.week

order by c1.title, c2.week

이 때 Union을 사용하면 

 

(

select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1

inner join checkins c2 on c1.course_id = c2.course_id

inner join orders o on c2.user_id = o.user_id

where o.created_at >= '2020-08-01'

group by c1.title, c2.week

order by c1.title, c2.week

)

UNION ALL

(

select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1

inner join checkins c2 on c1.course_id = c2.course_id

inner join orders o on c2.user_id = o.user_id

where o.created_at >= '2020-08-01'

group by c1.title, c2.week

order by c1.title, c2.week

)

두 필드가 합쳐져서 결과값을 추출하게 된다!

 

짠!

 

---

[퀴즈]  7월10일 ~ 7월19일에 가입한 고객 중,

포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶다!

 

 SELECT * from users u

 left join point_users pu on u.user_id = pu.user_id

 // 이어서 시작!

 

 

SELECT * from users u

left join point_users pu on u.user_id = pu.user_id

WHERE u.created_at BETWEEN "2020-07-10" AND "2020-07-20"

먼저, 7월10일~ 7월 19일에 가입한 고객 먼저 찾아주고, 

 

 

SELECT COUNT(pu.point_user_id) as pnt_user_cnt,

               COUNT(u.user_id) as tot_user_cnt

FROM users u

left join point_users pu on u.user_id = pu.user_id

WHERE u.created_at BETWEEN "2020-07-10" AND "2020-07-20"

포인트를 가진 고객의 숫자, 전체 숫자까지 구했다. (Alias 포함)

( 1부터 전체까지 user_id가 다 들어있으므로 user_id로 해도 됨 ) 

COUNT는 NULL 값을 세지 않는다!

 

 

SELECT COUNT(pu.point_user_id) as pnt_user_cnt,

count(u.user_id) as tot_user_cnt,

ROUND(COUNT(pu.point_user_id)/count(u.user_id),2) as ratio

FROM users u

left join point_users pu on u.user_id = pu.user_id

WHERE u.created_at BETWEEN "2020-07-10" AND "2020-07-20"

 

마지막으로 비율(Ratio)을 구했다 ( Round 써서 둘째 자리 수까지 반올림 ) 

 

결과는 이렇게 나온다! 

 

 

---

[숙제] enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고,

완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

 

 

enrolled 테이블과 enrolled_detail 테이블을 살펴본 후 

Inner join 으로 enrolled 테이블에 enrolled_detail 테이블을 붙여줬다.

수강완료 한 강의 갯수를 Where절을 이용해 찾고,

 Group by로 enrolled_id 를 묶어서

Count(*)로 세주고 Alias는 cnt로 지정했다.

마지막으로 Order by로 내림차순 정리 해주면 끝!

User id도 같이 출력되어야 된다고 했으니, Select 다음에 써주었다!

 

 

아래는 완성된 코드!

SELECT e.enrolled_id, user_id, COUNT(*) as cnt\

   FROM enrolleds_detail ed

inner join enrolleds e on ed.enrolled_id = e.enrolled_id

WHERE done = 1

GROUP BY e.enrolled_id

ORDER BY cnt DESC