오늘 배울 것
Subquery란? 쿼리 안의 쿼리라는 의미입니다.
- 즉, Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 파워풀한 기능입니다.
- Subquery에 대한 이해도가 생기면, With구문을 이용해서 더 간단하게 만들어볼게요!
- 원하는 데이터를 더 쉽게: Subquery
1) Subquery 사용방법 익혀보기
2) Where 에 들어가는 Subquery
kakaopay로 결제한 유저들의 정보 보기
select u.user_id , u.name , u.email from users u
inner join orders o on u.user_id = o.user_id
where o.payment_method = 'kakaopay'
select * from users u
where u.user_id in (.....)
in(.....)안의 내용만 내용전체를 추출할 수 있다.
예시)select * from users u
where u.user_id in ('3b3eac9f','afb35ce0','81bde520')
그 안에
select o.user_id from orders o
where payment_method = 'kakaopay'를 넣어서 추출하면 된다.
select * from users u
where u.user_id in (
select o.user_id from orders o
where payment_method = 'kakaopay'
)
저 줄만 실행하고 싶을땐 드래그 후 컨트롤 엔터 하면 된다.
subquery는 select절, from절, where절 어디든 들어갈 수 있다.
2) Select 에 들어가는 Subquery
select avg(likes) from checkins c
where user_id = '4b8a10e6'
select c.checkin_id ,
c.user_id ,
c.likes,
(
select avg(likes) from checkins
where user_id = c.user_id
) as avg_likes_user
from checkins c
subquery가 select 안에서 하나하나 다 실행이 된다.
3) From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
우선 유저 별 좋아요 평균을 먼저 구해볼까요?
select user_id, round(avg(likes),1) as avg_likes from checkins c
group by user_id
select user_id , point from point_users pu
from 뒤에 이미 있는 절처럼 사용해보자
select pu.user_id , pu.point, a.avg_likes from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_likes from checkins c
group by user_id
) a on pu.user_id = a.user_id
연습 1 Where 절에 들어가는 Subquery 연습해보기
1-1) 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
먼저 전체유저의 포인트의 평균을 구한다
select avg(point) from point_users pu
그리고 그 내용을 subquery로 넣는다.
select * from point_users pu
where point > (
select avg(point) from point_users pu
)
1-2) 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
(1) join으로 풀어보기
select avg(point) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name = '이**'
그대로 넣으면 된다.
select * from point_users pu
where point > (
select avg(point) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name = '이**'
)
(2) subquery로 풀어보기
select u.user_id from users u
where u.name = '이**'
이 자료를 포인트 평균을 구하려는 위치에 넣으면된다.
select avg(point) from point_users pu
where pu.user_id in(
select u.user_id from users u
where u.name = '이**'
)
이때 where in()을 조심하자
이 값을 비교하려는 포인트에 넣으면 된다.
select * from point_users pu
where point > (
select avg(point) from point_users pu
where pu.user_id in(
select u.user_id from users u
where u.name = '이**'
)
)
subquery 안에 subquery를 넣을 수 있다.
연습 2 Select 절에 들어가는 Subquery 연습해보기
2-1) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
먼저 유저 아이디 별 likes의 평균을 구해보자
select round(avg(likes),1) from checkins
그 후 값을 구하면 된다.
select c.checkin_id ,
c.course_id ,
c.user_id ,
c.likes ,
(
select round(avg(likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
여기서 먼저 구했던 likes의 평균을 잘 짜야된다.
2-2) checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기(나한텐 조금 어려웠다)
2-1)에서 만들어진 자료를 토대로 밑에 inner를 만들고 그 값에서 title을 가져오면 된다.
select c.checkin_id ,
c2.title ,
c.user_id ,
c.likes ,
(
select round(avg(likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
inner join courses c2 on c2.course_id = c.course_id
연습 3 From 절에 들어가는 Subquery 연습해보기
1) [준비1] course_id별 유저의 체크인 개수를 구해보기!
select course_id , count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by course_id
distinct()로 겹치지 않는 유저 아이디를 count하면 된다.
2) [준비2] course_id별 인원을 구해보기!
select course_id , count(*) from orders o
group by course_id
3) [진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기
select a.course_id, a.cnt_checkins, b.cnt_total
from (
select course_id , count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by course_id
) a
inner join (
select course_id , count(*) as cnt_total from orders o
group by course_id
) b
on a.course_id = b.course_id
4) [한 걸음 더] 퍼센트를 나타내기
select a.course_id, a.cnt_checkins, b.cnt_total , (a.cnt_checkins/b.cnt_total) as ratio
from (
select course_id , count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by course_id
) a
inner join (
select course_id , count(*) as cnt_total from orders o
group by course_id
) b
on a.course_id = b.course_id
5) [반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!
select c.title, a.cnt_checkins, b.cnt_total , (a.cnt_checkins/b.cnt_total) as ratio
from (
select course_id , count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by course_id
) a
inner join (
select course_id , count(*) as cnt_total from orders o
group by course_id
) b
on a.course_id = b.course_id
inner join courses c
on a.course_id = c.course_id
- with절
with table1 as (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
), table2 as (
select course_id, count(*) as cnt_total from orders
group by course_id
)
select c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
subquery를 alias처럼 쓸수있다.
- 실전에서 유용한 SQL 문법 (문자열, Case)
1) 문자열 쪼개보기(SUBSTRING_INDEX())
select user_id, email, SUBSTRING_INDEX(email,'@',1) from users u
숫자 1의 의미 : email의 @를 기준으로 앞에걸 보여줘
숫자 -1의 의미 : email의 @를 기준으로 뒤에걸 보여줘
2) 문자열 일부만 출력하기(substring())
select order_no , created_at , SUBSTRING(created_at, 1, 10) as date from orders o
string()은 엑셀의 mid()랑 사용법이 같다
select SUBSTRING(created_at, 1, 10) as date, count(*) from orders o
group by date
이런식으로 날짜별 종합해볼 수도 있다.
3) CASE (경우에 따라 원하는 값을 새 필드에 출력해보기)
select pu.user_id, pu.point,
(case when pu.point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as msg
from point_users pu
4) case + subquery 로 통계를 낼 수 있다.
(1)
select pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
(2) 구간별 몇명인지 확인할 수 있다.
select a.lv, count(*) from (
select pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
) a
group by a.lv
(3) with절로 만들어서 사용할 수 있다.
with table1 as (
select pu.user_id, pu.point,
(case when pu.point > 10000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
)
select a.lv, count(*) as cnt from table1 a
group by a.lv
초급퀴즈
퀴즈 1 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
select pu.user_id,
pu.point,
(case when pu.point > (select avg(point) from point_users pu2 ) then '잘 하고 있어요'
else '열심히 합시다!' end) as msg
from point_users puselect avg(point) from point_users pu2
퀴즈 2 이메일 도메인별 유저의 수 세어보기
select domain, count() as cnt_domain
from (
select substring_index(email,'@',-1) as domain from users u2
) a
group by domain
select SUBSTRING_INDEX(email,'@',-1) as domain, count() as cnt_domain
from users u
group by SUBSTRING_INDEX(email,'@',-1)
이거로도 같은 값이 나온다
퀴즈 3 '화이팅'이 포함된 오늘의 다짐만 출력해보기
select * from checkins c
where comment like '%화이팅%'
중급 퀴즈
퀴즈 1 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
select ed1.enrolled_id, count() as done_cnt, a.total_cnt from enrolleds_detail ed1
inner join (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) a on ed1.enrolled_id = a.enrolled_id
where ed1.done =1
group by ed1.enrolled_id
select a.enrolled_id, a.done_cnt, b.total_cnt from (
select ed1.enrolled_id, count() as done_cnt from enrolleds_detail ed1
where ed1.done =1
group by ed1.enrolled_id
) a
inner JOIN (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) b
on a.enrolled_id = b.enrolled_id
이렇게 해도 같다
퀴즈 2 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기
select ed1.enrolled_id, count() as done_cnt, a.total_cnt, round((count()/a.total_cnt),2) as ratio
from enrolleds_detail ed1
inner join (
select ed2.enrolled_id, count(*) as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) a on ed1.enrolled_id = a.enrolled_id
where ed1.done =1
group by ed1.enrolled_id
select a.enrolled_id, a.done_cnt, b.total_cnt, round((a.done_cnt/b.total_cnt),2) as ratio from (
select ed1.enrolled_id, count() as done_cnt from enrolleds_detail ed1
where ed1.done =1
group by ed1.enrolled_id
) a
inner JOIN (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) b
on a.enrolled_id = b.enrolled_id
이렇게 해도 같다
추가) with절 사용
with table1 as (
select ed1.enrolled_id, count() as done_cnt from enrolleds_detail ed1
where ed1.done =1
group by ed1.enrolled_id
), table2 as (
select ed2.enrolled_id, count() as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
)select a.enrolled_id,
a.done_cnt,
b.total_cnt, round((a.done_cnt/b.total_cnt),2) as ratio
from table1 a
inner JOIN table2 b on a.enrolled_id = b.enrolled_id
with절이 위에 있어야 한다.
추가) 그러나, 더 간단하게 만들 수 있지 않을까!
select enrolled_id,
sum(done) as done_cnt,
count() as total_cnt,
round(sum(done)/count(),2) as ratio
from enrolleds_detail ed
group by enrolled_id
