엑셀보다 쉬운, SQL 3주차

여러 정보를 한 눈에 보고 싶다면 (Join)

테이블1과 테이블2을 매칭 시켜줍니다. 그래서 Join이라는 기능을 사용하면, 한눈에 두 개의 테이블을 연결해서 볼 수 있습니다.

그럼 왜? 테이블을 구분 시켜놓는걸까?
-> 테이블은 한 목적들 끼리 모아두기 떄문이다.

예를 들면
수강정보는 수강정보들 끼리
결제정보는 결제정보들 끼리 ...

그러면 어떻게 두개의 테이블을 연결을 하는가?
-> 두 테이블의 공통된 필드가 있으면 가능하다

Join의 종류 (left join, inner join)

SQL에서의 Join은 두 집합 사이의 관계

Left Join

A테이블 기준으로 B테이블에 붙인다.

EX)
SELECT * from users u
left join point_users pu
on u.user_id = pu.user_id

Inner Join (교집합)

EX)
SELECT * from users u
inner join point_users pu
on u.user_id = pu.user_id

[실습] orders 테이블에 users 테이블 연결해보기

SELECT * from orders o
inner join users u
on o.user_id = u.user_id

[실습] checkins 테이블에 users 테이블 연결해보기

SELECT * from checkins c
inner join users u
on c.user_id = u.user_id

[실습] enrolleds 테이블에 courses 테이블 연결해보기

SELECT * from enrolleds e
inner join courses c
on e.course_id = c.course_id

SQL 쿼리가 실행되는 순서

select * from enrolleds e
inner join courses c
on e.course_id = c.course_id

위 쿼리가 실행되는 순서: from -> join -> select

1. from enrolleds e: enrolleds 테이블 데이터 전체를 가져옵니다.
2. inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙입니다.
3. select *: 붙여진 모든 데이터를 출력합니다.

checkins 테이블에 courses 테이블 연결해서 통계치 내보기

과목별 오늘의 다짐 갯수 세어보기

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
point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

point_users 테이블에 users 테이블 연결해서 통계치 내보기

유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑기

SELECT u.name ,u.email , pu.point FROM point_users pu
inner join users u on pu.user_id = u.user_id
order by pu.point desc

order 테이블에 users 테이블 연결해서 통계치 내보기

네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기

SELECT u.name, count(*) as cnt from orders o
inner join users u on o.user_id = u.user_id
where u.email like '%@naver.com'
group by u.name

위 쿼리가 실행되는 순서: from -> join -> where -> group by -> select

퀴즈

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

SELECT o.payment_method , round(avg(pu.point),2) as avg_point FROM orders o
inner join point_users pu on o.user_id = pu.user_id
group by o.payment_method

-> 분석결과 MONEY로 결제한 그룹들이 평균적으로 낮은 포인트를 얻었구나

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

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 cnt desc

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

SELECT c.course_id , c.title , count(*) as cnt from courses c
inner join enrolleds e on c.course_id = e.course_id
where e.is_registered = 0
group by c.course_id
order by cnt desc

웹개발, 앱개발 종합반의 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월이후에 가입한 사람의 목록 세어보기

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 00:00:00'
group by c.title , c2.week
order by c.title , c2.week

LEFT JOIN

포인트가 NULL 인 사람 세워보기

SELECT u.name , count(*) as cnt from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point is NULL
group by u.name
order by cnt desc

NULL을 사용할 떄 주로 Left Join 사용!

퀴즈: 7월 10일 ~ 7월 19일에 가입한 고객중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고싶을 때

SELECT count(pu.point_user_id) as point_user,
count(u.user_id) as total_user,
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 00:00:00' and '2020-07-19 23:59:59'

Union

select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우

7월, 8월달에 가입한 목록을 보고 싶을 떄

(
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
)
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
)

숙제

Enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기, user-id도 같이 출력되어야 한다.

select ed.enrolled_detail_id , e.user_id , count(*) as cnt from enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = 1
group by ed.enrolled_id , e.user_id
order by cnt desc