# 3주차 목표
- 여러 테이블의 정보를 연결하는 Join을 이해한다.
- 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습한다.
- 아래 위로 결과를 연결하는 Union을 공부한다.
# JOIN이란?
👉두 테이블의 공통된 정보(=key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미. 엑셀의 vlookup과 같다!
자주 쓰는 join
- left join : 왼쪽 테이블을 기준으로 공통된 데이터를 연결.
따라서, left join은어디에/무엇을 붙일건지, 순서가 중요
- inner join : 각 테이블의 공통된 데이터.(=교집합)
# 실습
EX 1 : orders 테이블에 users 테이블 연결하려면,
SELECT * FROM orders o
INNER JOIN users u
ON o.user_id = u.user_id;※ 주문을 하기 위해서는 회원정보가 있어야 할테니, orders 테이블에 담긴 user_id는 모두 users 테이블에 존재함.
※ 위 쿼리가 실행되는 순서: from → join → select
(join은 항상 from과 붙어 있다.)
연결의 기준이 되고싶은 테이블을 from 절에,
기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치!
EX 2 : checkins 테이블에 courses 테이블 연결해서 통계치 내보기
'오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보려면,
SELECT c2.title, COUNT(*) as cnt_title
FROM checkins c
INNER JOIN courses c2
ON c.course_id = c2.course_id
GROUP BY c2.title ;결과 👉
EX 3 : point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기
유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑으려면,
SELECT * FROM point_users pu
INNER JOIN users u
ON pu.user_id = u.user_id
ORDER BY pu.point DESC ;결과 👉
EX 4 : orders 테이블에 users 테이블 연결해서 통계치 내보기
주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보려면,
SELECT u.name, COUNT(u.name) as name_cnt
FROM orders o
INNER JOIN users u
ON o.user_id = u.user_id
WHERE o.email LIKE '%naver.com'
GROUP BY u.name ;결과 👉
위 쿼리가 실행되는 순서: from → join → where → group by → select
1. from : orders 테이블 데이터 전체를 가져옴.
2. join : users와 orders 테이블 연결, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙임. (users 테이블에 u라는 별칭)
3. where : users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져옴.
4. group by : users 테이블의 name값이 같은 값들을 그룹화.
5. select : users 테이블의 name 필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력.
EX 5 : 결제 수단 별 유저 포인트의 평균값 구해보기
SELECT o.payment_method, ROUND(AVG(pu.point),2) as point_avg
FROM point_users pu
INNER JOIN orders o
ON pu.user_id = o.user_id
WHERE pu.point
GROUP BY o.payment_method ;결과 👉
평균값에서 나타내고자 하는 자리까지만 round(__,_)로 나타내준다. 위 값은 소수점 아래 2자리수까지 표기한 것이고, 정수만 표기하려면 round(avg(pu.point),0)로 작성하면 된다.
EX 6 : 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
SELECT u.name, COUNT(u.name) as name_cnt
FROM enrolleds e
INNER JOIN users u
ON e.user_id = u.user_id
WHERE is_registered = 0
GROUP BY u.name
ORDER BY name_cnt DESC ;결과 👉
_시작하지 않은_ => is_registered = 0
EX 7 : 과목 별로 시작하지 않은 유저들을 세어보기
SELECT c.title, count(*) as title_cnt
FROM courses c
INNER JOIN enrolleds e
ON c.course_id = e.course_id
WHERE is_registered = 0
GROUP BY c.title ;결과 👉
EX 8 : 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
SELECT title, week, COUNT(*) as week_cnt
FROM courses c1
INNER JOIN checkins c2
ON c1.course_id = c2.course_id
GROUP BY title, week
ORDER BY title, week;※ 위처럼 작성해도 정답은 나오지만 title과 week가 각각 어느 테이블에 속한 컬럼인지 표기하는 것이 쿼리가 길어졌을 때 헷갈리지 않는다. 따라서 보기 좋게 아래처럼 수정해주는 것이 좋다.
SELECT title, week, COUNT(*) as week_cnt
FROM courses c1
INNER JOIN checkins c2
ON c1.course_id = c2.course_id
GROUP BY c1.title, c2.week
ORDER BY c1.title, c2.week;꿀팁! → group by, order by에
콤마로 이어서 두 개 필드를 건다. EX8을 예시로 보면, title과 week로 각각 그룹화한 후 각각 정렬할 수 있다. (꼭 필드 하나만 적용하는 것이 아님!!)
# is NULL, is not NULL
: inner join이라면 공통분모가 나오지만 left join 같은 경우, 왼쪽 테이블을 기준으로 차집합의 형태로 값이 나오므로 NULL값이 존재할 수 있다.
ex) users 테이블에 point_users 테이블을 left join으로 연결했을 때, 유저 중 강의를 시작하지 않은 사람들은 포인트가 적립되지 않았을 것이므로 데이터가 표시되지 않을 것이다( [NULL]값)
select * from users u
left join point_users pu on u.user_id = pu.user_id
따라서, 시작하지 않은 유저를 검색하려면 is null 키워드를 이용할 수 있다.(반대의 경우 is not null 입력)
그리고 count는 NULL을 세지 않는다.
select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by nameEX 9 : 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 추출하려면,
SELECT COUNT(pu.point_user_id) as pnt_cnt,
COUNT(*) as tot_cnt,
ROUND(COUNT(pu.point_user_id)/COUNT(*),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'결과 👉
- count는
null을 세지 않으므로, 전체(=202)에서 포인트를 가진 고객의 숫자를 count하면 null을 제외한 값(=82)이 나온다. 따라서 82/202를 round로 소수점을 정리해주면(두 자리까지 구한다고 했을 때), 0.41이라는 비율이 나온다.- count와 round 등 여러 조건이 붙어 컬럼명이 길어지므로 적절한 alias를 이용해준다.
# Union
: 데이터 결과물을 합쳐서 한번에 보고 싶은 경우, 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)
order by month, title, week;결과 👉
union을 사용하면 내부 정렬이 먹지 않는다!
# 마무리 숙제
: enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.
SELECT e.enrolled_id, e.user_id, COUNT(*) as e_cnt
FROM enrolleds e
INNER JOIN enrolleds_detail ed
ON e.enrolled_id = ed.enrolled_id
WHERE ed.done = 1
GROUP BY e.enrolled_id, e.user_id
ORDER BY e_cnt DESC ;결과 👉
# 새로 배운 내용
- union all은 합집합의 개념이므로 order by를 통해 정렬한 것이 의미가 없어진다. 내부 정렬이 되지 않으므로 union으로 합쳐진 최종 결과물에 다시 order by를 적용하거나
subquery를 사용하도록 한다. count는 NULL을 세지 않는다.콤마(,)로 group by, order by를 두 개 이상 필드로 조건을 걸 수 있다.- 엔터티 관계도를 봤을 때 각 테이블의 최상단 칼럼이 주로 기본값이 될 수 있다. 해당 데이터 하나를 구분짓도록 해주는 고유번호라고 볼 수 있다.
# 회고
-
3주차는 이론보다 실습 위주로 작성을 했는데 확실히 쿼리를 직접 써보는 게 문법을 익히는데 빠르다. 에러가 자주 나는 구문 위주로 추가 작성해보기
(EX 8,9는 다시 풀어보는 문제였는데도 복잡하고 헷갈렸다. 최대한 정답 코드스니펫을 참고 하지 않도록 여러번 복습하고 다시 풀어보기) -
join을 배우고 나니
error : ~is ambiguous가 자주 뜬다. 문장 그대로 중의적이라는 뜻인데, 여러 테이블을 사용하고 각 테이블에서도 다른 테이블과 중복되는 필드가 있으니, 출력하고자 하는 데이터가 어떤 내용으로 사용하는지에 중점을 두어 연결해주어야 한다.
ex) 고객들의 정보를 가져와야 한다면, 고객 정보 중 가장 기본값이 되는user_id로 연결해준다. -
조인해야 하는 테이블이 무엇인지, 조인하는 필드가 무엇인지 잘 선별하도록 해야 한다. 어디에 어떤 데이터가 있는지 기억이 안나면 속성값을 일일이 클릭하기 번거로웠는데, 엔터티 관계도를 켜두니 쿼리 작성을 시작할 때 훨씬 직관적이라 수월했다.
