22/05/02 ~ 22/05/12
๋ง์ง๋ง ์ฃผ์ฐจ์ธ 4์ฃผ์ฐจ์ 3์ฃผ์ฐจ ๋ง๋ฌด๋ฆฌํ๋ ๊ฒ์ผ๋ฆ๋ฑ
์ด ๋ #~#
3์ฃผ์ฐจ๋ฅผ ์์ํด๋ณด์๋ค. 3์ฃผ์ฐจ์ ํต์ฌ์ ์ชผ์ธ(join) !
๐น ํฉ์น๊ณ ์ ํ๋ ๋ ํ ์ด๋ธ์ ์๋ ๊ณตํต๋ ํ๋๋ฅผ ๊ธฐ์ค์ผ๋ก ํ ์ด๋ธ์ ์ฐ๊ฒฐํ์ฌ ํ ํ ์ด๋ธ์ฒ๋ผ ๋ณผ ์ ์๊ฒ ๋ง๋๋ ๊ฒ !
๐น ์ด ๋ ์ด ๊ณตํต๋ ํ๋๋ฅผ 'key' ๐ ๋ผ๊ณ ํฉ๋๋ค !
Join์๋ ๋ ๊ฐ์ง ์ข
๋ฅ๊ฐ ์๋๋ฐ,
1๏ธโฃ Left Join
2๏ธโฃ Inner Join
'Left Join'์ ์ฒซ ๋ฒ์งธ ํ
์ด๋ธ์ ๋ถ์ด๋ ๊ฒ์ด๊ณ , (๋ฐ๋ผ์ ์์๊ฐ ์ค์ โญ๏ธ)
'Inner Join'์ ๊ต์งํฉ ๊ฐ๋
์ผ๋ก,
์ด ์ค์์ ๋๋ Inner Join์ ์ฃผ๋ก ์ฌ์ฉํ์๋ค ! ๐
์ ์๋๊ป์๋ Left Join๋ณด๋ค๋ Inner Join์ ๋ ๋ง์ด ์ฌ์ฉํ ๊ฒ์ด๋ผ๊ณ ํ์
จ๋ค. ๐จ๐ปโ๐ซ
๐ chapter3-2 ๋จ์ํ ์ฌ๋ฌ ํ
์ด๋ธ ์ฐ๊ฒฐํด๋ณด๊ธฐ
๋ด๊ฐ Inner Join์ ์ฒ์ ๋ฐฐ์ฐ๊ณ ๋ฌธ์ ๋ฅผ ํ๋ฉด์ ๊ฐ์ฅ ๋ง์ด ๋์ณค๋ ๋ถ๋ถ์ alias(๋ณ์นญ)์ด์๋ค !
๊ฐ๋ฒผ์ด ์ฝ๋๋ฅผ ํ๋ ๋ณด์.
select * from checkins c
inner join users u on c.user_id = u.user_id
1๏ธโฃ ์ง๋ 2์ฃผ์ฐจ๊น์ง ๊ณต๋ถํ๋ SQL์ ๊ธฐ๋ณธ์ ์ธ ๊ตฌ์กฐ๋ง ๋ณด๋ฉด ์ฒซ ์ค์ 'checkins' ํ
์ด๋ธ์ ๊ฐ์ ธ์จ๋ค๋ ์๋ฏธ ! checkins ๋ค์ ์๋ 'c'๊ฐ ์ด ํ
์ด๋ธ์ ๋ณ์นญ์ด๋ค.
2๏ธโฃ ๋ ๋ฒ์งธ ์ค์ ๊ฐ๋จํ๊ฒ ๋ํ๋ด๋ฉด
inner join ํ
์ด๋ธ๋ช
ํ
์ด๋ธ ๋ณ์นญ on ์ฐ๊ฒฐ ๊ธฐ์ค์ธ key๊ฐ ํํ
์ด๋ ๊ฒ ๋์ ์๋ค.
โญ๏ธJoin์์ ๊ฐ์ฅ ์ค์ํ๋ค๊ณ ์๊ฐํ๋ key๊ฐ ํํ์ ํ ๋ alias๊ฐ ํ์์ ์ด๋ฏ๋ก ๊ผญ ์ฑ๊ฒจ์ผ ํ๋ค โ๏ธ
๐๐ป ์์ฉ ๋ฌธ์ 1๏ธโฃ
select c1.course_id, count(*) as cnt from checkins c1
inner join courses c2 on c1.course_id = c2.course_id
group by c1.course_id
๐น ์ฟผ๋ฆฌ ์คํ ์์ : from โก๏ธ join โก๏ธ group by โก๏ธ select
๐จ๐ปโ๐ซ "๋ชจ๋ ๊ฐ๋ฐ์๋ค์ด ๋ค SQL์ ์ฒ์๋ถํฐ ์๋ฒฝํ๊ฒ ์์ฑํ๋ ๊ฒ์ ์๋๋ค, ์คํ ์์์ ๋ฐ๋ผ ์ฐจ๊ทผ ์ฐจ๊ทผ ํ๋ฉด ๋๋ค !"
๐น ๊ฐ๋จ ์์ฝ
1) select * from checkins c1์ผ๋ก ์์
2) courses c2 ํ
์ด๋ธ๊ณผ course_id์ ๋ํด join
3) course_id์ ๋ํด ๊ทธ๋ฃน ์ง๊ธฐ (๊ทธ๋ฅ key๊ฐ๋ง ์ ์ง ๋ง๊ณ ๊ผญ alias ๋ถ์ด๊ธฐ !)
4) ์ต์ข
result์ ๋ํ๋ผ ํ๋๋ค๋ง ์ถ๋ ค์ select ๋ค์ ๋ฃ๊ธฐ + as cnt๋ ๋ณ์นญ ์ค์ !
๐๐ป ์์ฉ ๋ฌธ์ 2๏ธโฃ
select u.name, count(*) as 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๋ฒ์ ํ์ด๋ดค์ผ๋ ๋์ถฉ ๊ฐ ์ค๊ฒ ์ง ? ๋ณต์ตํ ๋๋ ๋ฐ๋ก ์๊ธฐ๋ฅผ ^_^
๐น ๊ฐ๋จ ์์ฝ
1) select * from orders o ๋ก ์์
2) users u ํ
์ด๋ธ๊ณผ user_id์ ๋ํด join
3) o ํ
์ด๋ธ์ email ํ๋์์ 'naver.com'์ผ๋ก ๋๋๋ ์ฌ๋๋ง ์ถ๋ฆฌ๊ธฐ (where - ์กฐ๊ฑด)
4) u ํ
์ด๋ธ์ name ํ๋๋ก ๊ทธ๋ฃน ์ง๊ธฐ
5) ์ต์ข
result์ ๋ํ๋ผ ํ๋๋ค๋ง ์ถ๋ ค์ select ๋ค์ ๋ฃ๊ธฐ !
๐๐ป ์ฌํ ๋ฌธ์ 1๏ธโฃ : ๊ฒฐ์ ์๋จ ๋ณ ์ ์ ํฌ์ธํธ์ ํ๊ท ๊ฐ ๊ตฌํด๋ณด๊ธฐ (point_users ํ ์ด๋ธ์ orders ํ ์ด๋ธ ๋ถ์ด๊ธฐ)
select o.payment_method, round(avg(pu.point)) as avg_point from point_users pu
inner join orders o on pu.user_id = o.user_id
group by o.payment_method
๐น ๊ฐ๋จ ์์ฝ
1) select * from point_users pu ๋ก ์์ - point_users ํ
์ด๋ธ ๋ณด์ฌ์ฃผ๊ธฐ
2) orders o ํ
์ด๋ธ๊ณผ user_id์ ๋ํด join !
3) o ํ
์ด๋ธ์ payment_method ํ๋์ ๋ํด ๊ทธ๋ฃน ์ง๊ธฐ
4) ์ต์ข
result์ ๋ํ๋ผ ํ๋๋ค ์ ํ + avg๋ ํ๊ท (avarage)์ ์ฝ์, round(ํ๋, ์๋ฆฟ์) : ๋ฐ์ฌ๋ฆผ
๐๐ป ์ฌํ ๋ฌธ์ 2๏ธโฃ : ๊ฒฐ์ ํ๊ณ ์์ํ์ง ์์ ์ ์ ๋ค์ ์ฑ์จ ๋ณ๋ก ์ธ์ด๋ณด๊ธฐ (enrolleds ํ ์ด๋ธ์ users ํ ์ด๋ธ ๋ถ์ด๊ธฐ)
select name, count(*) as cnt_name 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_name desc
โ
์ฌ๊ธฐ์ ํ๋ ธ๋ ์ ์ง๊ณ ๋์ด๊ฐ๊ธฐ
1) count(name) -> count(*)
2) is_registered = 0 -> e.is_registered
(๋ณ์นญ ๊ผญ ๋ถ์ด๊ธฐโ๏ธ)
๐๐ป ์ฌํ ๋ฌธ์ 3๏ธโฃ : ๊ณผ๋ชฉ ๋ณ๋ก ์์ํ์ง ์์ ์ ์ ๋ค ์ธ์ด ๋ณด๊ธฐ (courses ํ ์ด๋ธ์ enrolleds ๋ถ์ด๊ธฐ)
select c.course_id, c.title, count(*) as cnt_notstart from courses c
inner join enrolleds e on c.course_id = e.course_id
where e.is_registered = 0
group by c.course_id
โ
์ฌ๊ธฐ์ ํ๋ ธ๋ ์ ์ง๊ณ ๋์ด๊ฐ๊ธฐ
1) sรฉlect course_id -> sรฉlect c.course_id (๋ณ์นญ ๊ผญ ๋ถ์ด๊ธฐโ๏ธ)
2) where ์กฐ๊ฑด ์ ๋จผ์ ์ฐ๊ณ group by๋ก ๋ฌถ์ด์ฃผ๊ธฐ
๐๐ป ์ฌํ ๋ฌธ์ 4๏ธโฃ : ์น ๊ฐ๋ฐ, ์ฑ ๊ฐ๋ฐ ์ข
ํฉ๋ฐ์ week ๋ณ ์ฒดํฌ์ธ ์๋ฅผ ์ธ์ด๋ณด๊ณ 8์ 1์ผ ์ดํ์ ๊ตฌ๋งคํ ๊ณ ๊ฐ๋ค๋ง ๋ฐ๋ผ๋ด์ด ๋ณด๊ธฐ ! (courses ํ
์ด๋ธ์ checkins ํ
์ด๋ธ ๋ถ์ด๊ณ orders ํ
์ด๋ธ ํ ๋ฒ ๋ ๋ถ์ด๊ธฐ) โญ๏ธ์ด๋ ค์โญ๏ธ
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
๐น ์ฒดํฌํด์ผ ํ ๋ถ๋ถ
1) ์ด๋ฏธ ์ด์ด๋ถ์ธ ๋ ํ
์ด๋ธ์ ๋ ํ๋์ ํ
์ด๋ธ์ ๋ถ์ผ ๋, ์ถ๊ฐ๋ก inner join์ ์ ์ฉํ๋ฉด ๋๋ค ! ์ฌ๊ธฐ์ key ๊ฐ๐์ ์ฒดํฌํ๋ ๊ฒ์ด ์ค์ !
2) where ์กฐ๊ฑด ์ ๋จผ์ ์ฐ๊ณ group by, order by ์ ์ฉํด์ฃผ๊ธฐ
3) ๋ ์ง๋ '' ์์ ์ ์ด์ฃผ๊ธฐ
๐ chapter3-7 ' Left Join' ์จ๋ณด๊ธฐ !
Q. 7์10์ผ ~ 7์19์ผ์ ๊ฐ์
ํ ๊ณ ๊ฐ ์ค,
ํฌ์ธํธ๋ฅผ ๊ฐ์ง ๊ณ ๊ฐ์ ์ซ์, ๊ทธ๋ฆฌ๊ณ ์ ์ฒด ์ซ์, ๊ทธ๋ฆฌ๊ณ ๋น์จ์ ๋ํ๋ด๊ธฐ
select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_users_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'
๐น ์ฒดํฌํด์ผ ํ ๋ถ๋ถ
1. count๋ NULL์ ์ธ์ง ์์ผ๋ฏ๋ก ์ ์ฒด ์ซ์๋ฅผ ์
๋ NULL์ด ์๋ ํ๋๋ฅผ ์ธ์ด์ฃผ๋ฉด ๋๋ค โญ๏ธ
2. between์ A๋ถํฐ B๊น์ง์ผ ๋, between A and B+1 ์ด๋ ๊ฒ ์ฌ์ฉํ๋ค.
๐ chapter3-8 Union ์ฌ์ฉํด๋ณด๊ธฐ !
๐๐ป Union์ select๋ฅผ ๋ ๋ฒ ํ์ง ์๊ณ ํ ๋ฒ์ ๋ณด๊ณ ์ถ์ ๋ ์ฌ์ฉํ๋ค ~
์์ ๊ฐ์ ๋ชจ์ต์ ๋ง๋ค๊ธฐ ์ํด ์๋ ์ฝ๋๋ฅผ ์์ฑํด๋ณด์๋ค.
(
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
)
๊ฐ์ด๋ฐ ์๋ union all์ด ํฌ์ธํธ !
ํ๋ ๋ ์ฒดํฌํด์ผ ํ ๊ฒ์ order by๋ฅผ ํ ์ํ๋ก ํฉ์น๋ ๊ฒ์ ์๋ฏธ๊ฐ ์๊ณ , ํฉ์น ํ์ ์ ์ฒด์์ order by๋ฅผ ํด์ค์ผ ํ๋ค !
๐ 3์ฃผ์ฐจ homework : enrolled_id๋ณ ์๊ฐ์๋ฃ(done=1)ํ ๊ฐ์ ๊ฐฏ์๋ฅผ ์ธ์ด๋ณด๊ณ , ์๋ฃํ ๊ฐ์ ์๊ฐ ๋ง์ ์์๋๋ก ์ ๋ ฌํด๋ณด๊ธฐ !
user_id๋ ๊ฐ์ด ์ถ๋ ฅ๋์ด์ผ ํ๋ค.
์ด๋ ๊ฒ ๋ฐฐ์ฐ๊ณ ๋๋ ์์ ๋ ๊ป์ด์ง ! (๊ณผ์ฐ ?)
๐ฆ ๋๋ ์
๊ณต๋ถํ๋ฉด์ ์ด๋ ค์ด ๋ถ๋ถ๋ค์ด ์กฐ๊ธ์ฉ ์๊ธฐ๋ค๋ณด๋ ์ด๊ฒ ๋ฐ๋ก SQL์ธ๊ฐ ? ์ถ์ผ๋ฉด์๋, ๋ง์ง๋ง์ผ๋ก ๋จ์ 4์ฃผ์ฐจ ๊ณต๋ถ๋ฅผ ์งํํ ์๊ฐ์ ๋๊ทผ๋๊ทผ๋๋ค โค๏ธโ๐ฅโค๏ธโ๐ฅโค๏ธโ๐ฅ
์ง๊ธ ๋ฐฐ์ฐ๋ ํ๊ต ๊ณผ๋ชฉ ์ค ์๋์ด๋
ธ๋ฅผ ์ด์ฉํ๋ '์ด๋๋ฒค์ฒ ๋์์ธ'์์ ์ฐ๋ฆฌ ์กฐ๋ ๊ตฌํ ์ ํ๊ณผ ์ดํ์ ๋ง๋๋๋ผ SQL์ด ํ์ํ์ ๋๊ฐ ์์๋๋ฐ, ๊ณต๋ถํ๊ณ ๋ณด๋ ์ญ์๋ ์ญ์ ...!
" ์๋ ๋งํผ ๋ณด์ธ๋ค " ๋จ์ ํ ์ฃผ์ฐจ๋ ํ์ดํ
๐ฅ