엑셀보다 쉬운, SQL - 3일차

Inner Join, Left Join, Union

 

Join 을 사용하기 위해서는 기준이 되는 필드가 있어야 함.

두개의 테이블을 참조하기 위해 두 테이블에 공통으로 들어가는 key값이 반드시 필요.

 

Left Ioin , Inner Join을 많이 씀. Outer Join은 현업에서 미사용

 

Inner Join : 교집합으로 보면 됨.

// Inner Join 쿼리문

select * from users u

inner join point_users p

on u.user_id = p.user_id

/* inner join은 교집합이라고 생각하면 됨. */

 

select * from point_users pu

 

 

select * from orders o

inner join users u

on o.user_id = u.user_id

 

 

select * from checkins c

inner join users u2

on c.user_id = u2.user_id

 

 

select * from enrolleds e

inner join courses c2

on e.course_id = c2.course_id

 

 

// 과목별 오늘의 다짐 갯수 조회

select c1.course_id, c2.title, count(*) as cnt from checkins c1

inner join courses c2

on c1.course_id = c2.course_id

group by c1.course_id

 

 

// 많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기

select * from point_users pu

inner join users u2

on pu.user_id = u2.user_id

order by pu.point desc

 

 

// 네이버 이메일 사용자의 성씨별 주문건수 세기

select u1.name, count(*) as cnt from orders o

inner join users u1

on o.user_id = u1.user_id

where o.email like '%naver%'

group by u1.name

 

//결제 수단별 유저 포인트의 평균값 구하기

select o.payment_method, round(avg(pu.point),2) as avg from point_users pu

inner join orders o

on pu.user_id = o.user_id

group by o.payment_method

 

 

//결제하고 시작하지 않은 유저들 성시별로 세어보기

select u.name, count(*) as cnt from enrolleds e

inner join users u

on e.user_id = u.user_id

where e.is_registered =0

group by u.name

order by count(*) desc

 

// 과목별로 시작하지 않은 유저들 세어보기

select c.course_id, c.title, count(*) as cnt from courses c

inner join enrolleds e

on e.course_id = c.course_id

where e.is_registered =0

group by c.course_id

 

// 앱개발, 웹개발 종합반의 week별 체크인 수 세어보기

select c.title, c2.week, count(*) as cnt from courses c

inner join checkins c2

on c.course_id = c2.course_id

group by c.title, c2.week

order by c.title, c2.week

 

// 위에 코드에서 8월 1일 이후에 구매한 고객들만 조회.

select c.title, c2.week, count(*) as cnt from courses c

inner join checkins c2

on c.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 c.title, c2.week

order by c.title, c2.week

 

 

 

 

Left Join 

select * from users u

left join point_users p

on u.user_id = p.user_id

/* left join은 왼쪽인 users테이블에 id가 있는 데이터 기준으로 조회.

그러므로 point테이블에 id가 없는 회원도 출력됨. */

 

 

select u.name, count(*) from users u

left join point_users pu2

on u.user_id = pu2.user_id

where pu2.point_user_id is not null

group by u.name

 

//7월10-7울 19일에 가립한 고객중, 포인트를 가진 고객의 숫자, 전체 숫자, 그리고 비율

select count(pu.point) as pnt_user_cnt,

count(*) as tot_user_cnt,

round( count(point) / count(*) , 2) as ratio

from users u

left join point_users pu2

on u.user_id = pu2.user_id

where u.created_at BETWEEN '2020-07-10' and '2020-07-20'

 

 

 

Union All

//Union함수

(

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 ALL

(

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

)

 

Homeworks

// enrolled_id별 수강완료한 강의 갯수를 세어보고, 완료한 강의수가 많은 순서로 정렬

select e.enrolled_id, e.user_id, count(*) as max_count from enrolleds e

inner join enrolleds_detail ed2

on e.enrolled_id = ed2.enrolled_id

where ed2.done = 1

group by e.enrolled_id

ORDER by count(*) desc