✏️ 혼자 연습하려고 기록하는 블로깅.. ^^
🐼 몰랐던 부분 & 헷갈린 부분만 정리
-
JOIN -> 실에서 엄~~~청 많이 쓰임!
-> 두 개의 테이블을 연결해서 볼 수 있음!
-> 기준이 되는 키가 필요함 ! 그걸 매개로 두개를 붙임. -
JOIN: Left join, Inner join 🌱
-
Left join
A를 기준으로(왼쪽을 기준으로) B를 붙인다!
-
users랑 point_users 랑 레프트 조인할거야. (각각 별칭 붙여주고)
-
on : 무엇을 기준으로 이어주지? u의 user_id와 p의 user_id랑 이어쥼!
-
NULL값도 출력
-
Inner join
A와 B의 교집합!
- inner join은 포인트가 있는 것만 나옴.(교집합)
📌 누구한테 누굴 붙이느냐가 정말 중요함.
inner join은 교집합이니까 바뀌어도 상관없음
🌱 실습1
select * from checkins c
inner join users u
on c.user_id = u.user_id🌱 실습2
select * from enrolleds e
inner join courses c
on e.course_id = c.course_id🌱 실습3
select u.name, count(u.name) as count_name 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
🌱 퀴즈1
select o.payment_method, round(avg(pu.point)) from point_users pu
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method🌱 퀴즈2
select u.name, count(en.is_registered) as cnt from enrolleds en
inner join users u
on en.user_id = u.user_id
where en.is_registered = 0
group by
order by cnt DESC 🌱 퀴즈3
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🌱 퀴즈4
select c1.title, c2.week, count(*) as 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🌱 퀴즈5
select 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
# inner join 한번 하고 on 해주고, inner join 두번 해주고 on 해주고!-
같은 결과에 도달 할 수 있는 방법이 여러가지!
-
left join 연습해보기!
-> 한쪽에는 있는데 한쪽에는 없는 걸 통계내고 싶을 때 !
🌱 퀴즈6
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'
# 모범답안
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를 구할 때, 별명으로 계쏙 넣으니까 안됐다. count()/count()로 해야하나보다.📌 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
order by c1.title, c2.week
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 로!
->
-> union all에서 order by가 안먹기 때문에 빼줘도 상관없음!
🌱 숙제
select en.enrolled_id, en.user_id, count(done) from enrolleds en
inner join enrolleds_detail ed
on en.enrolled_id = ed.enrolled_id
group by ed.enrolled_id
order by done desc
# 나는 done=1인걸 조건을 못걸어줌.. 계속 에러남 ㅠㅠ(where done=1 이 아닌걸까..?)🌱 샘 설명(정답)
select en.enrolled_id, en.user_id, count(*) as cnt from enrolleds en
inner join enrolleds_detail ed
on en.enrolled_id = ed.enrolled_id
where ed.done=1
group by ed.enrolled_id, en.user_id
order by cnt desc
#1. where ed.done을 왜 생각 못했찌.. 계속 done으로만 넣어서 에러...ㅠㅠ
#2. group by 할 떄, and로 연결하지 말고 (,)콤마로 해줄 것!
#3. count(*)하고 별명 as로 붙여줄 것!
#4. order by 할 때 done말고 cnt 별명으로 가능!🌈 3주차 완료 ! inner join, left join 재밌땅!! 4주차 고고!
개발 기록