앞의 3주차까지의 내용을 식은죽 먹기
4주차 subquery가 가장 어렵다.
01. 오늘 배울 것
1) Subquery: 원하는 데이터를 더 쉽게 얻어보기
Subquery란? 쿼리 안의 쿼리라는 의미입니다.
하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해져요!
Subquery에 대한 이해도가 생기면, With구문을 이용해서 더 간단하게 만들어볼게요!
2) 실전에서 유용한 SQL 문법들
생각보다 실무에서의 데이터는 지저분하고 복잡합니다.
주어진 데이터를 원하는 유의미한 정보로 만들기 위해서는 이것저것 해야할 일이 많습니다.
문자열을 한 번에 정리한다든지,
조건에 따라 데이터를 구분한다든지요.(case when 조건문)
실무 속 날것의 데이터에서도 원하는 데이터를 뽑아낼 수 있는 유용한 기능을 배워봅니다!
02. 원하는 데이터를 더 쉽게: Subquery
4) Subquery 사용방법 익혀보기
탭해서 subquery의 줄을 잘 맞춰주자.
기존의 innerjoin을 이용한 방법
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'subquery를 이용한 방법
select user_id, name, email from users u
where user_id in (
select user_id from orders o
where payment_method = 'kakaopay'
)03. Subquery 본격 사용해보기
5) 자주 쓰이는 Subquery 유형 알아보기
6) Where 에 들어가는 Subquery
위의 첫번째 예시는 where에 들어간 것
7) Select 에 들어가는 Subquery
select c1.checkin_id,
c1.user_id,
(
select round(avg(likes),2) from checkins c2
where c2.user_id in (c1.user_id)
) as likes_avg
from checkins c1where 절을 이렇게 할 수도 있다.
where c2.user_id = c1.user_id
8) From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
join 이용하는 거네~, 그냥 다른 테이블을 join해주듯이 내가 만든 subquery 테이블을 이어준다.
select pu.user_id,
pu.point,
a.likes_avg
from point_users pu
inner join (
select c.user_id ,
round(avg(c.likes),1) as likes_avg
from checkins c
group by c.user_id
) a on a.user_id = pu.user_id
order by pu.point desc04. Subquery 연습해보기 (where, select)
9) Where 절에 들어가는 Subquery 연습해보기
[연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
힌트! → point_users 테이블을 이용해서 avg를 구하고, 다시 point_users와 조인하세요!
👉 포인트가 평균보다 많은 사람들의 데이터를 추출해보자! *참고: 평균 포인트는 5380점
select * from point_users pu
where point > (
select avg(point) from point_users pu
)
[오늘의 팁!] 위와 같이, 같은 테이블을 Subquery로 사용할 수도 있어요.
[연습] 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
힌트! → 위 구문의 서브쿼리 내에서 users와 inner join을 해보세요!
👉 이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터를 추출해보자!
*참고: 이씨 성을 가진 유저들의 평균 포인트는 7454점
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 name = '이**'
)select * from point_users pu
where point > (
select avg(point) from point_users pu1
where pu1.user_id in (
select u.user_id from users u
where u.name = '이**'
)
)
10) Select 절에 들어가는 Subquery 연습해보기
[연습] checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
select c1.checkin_id,
c1. course_id,
c1.user_id,
c1.likes,
(
select round(avg(likes),1) from checkins c2
where c2.course_id = c1.course_id
) as course_avg
from checkins c1
[연습] checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
select c1.checkin_id,
(
select c.title from courses c
where c.course_id = c1.course_id
) as title,
c1.user_id,
c1.likes,
(
select round(avg(likes),1) from checkins c2
where c2.course_id = c1.course_id
) as course_avg
from checkins c1select c1.checkin_id,
c.title,
c1.user_id,
c1.likes,
(
select round(avg(likes),1) from checkins c2
where c2.course_id = c1.course_id
) as course_avg
from checkins c1
inner join courses c on c.course_id = c1.course_id
c.* 이렇게 select*에 넣어주면 inner join한 테이블을 다 확인 할 수 있다.
05. Subquery 연습해보기 (from, inner join)
From 절에 들어가는 Subquery 연습해보기
[준비1] course_id별 유저의 체크인 개수를 구해보기!
select c.course_id, count(distinct(c.user_id)) as cnt_checkins from checkins c
group by c.course_id
[준비2] course_id별 인원을 구해보기!
orders 테이블을 course_id로 group by 하면 되겠죠! 너무 쉽다!
select course_id ,count(*) as cnt_total from orders o
group by o.course_id
[진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기
내가 쓴 답
select c.course_id ,
count(distinct(c.user_id)) as cnt_checkins,
a.cnt_total
from checkins c
inner join (
select o.course_id, count(*) as cnt_total from orders o
group by o.course_id
) a on a.course_id = c.course_id
group by c.course_id강의노트 답
select a.course_id,
a.cnt_checkins,
b.cnt_total
from (
select course_id,
count(distinct(user_id)) as cnt_checkins
from checkins
group by course_id
) a
inner join (
select course_id,
count(*) as cnt_total
from orders
group by course_id
) b on b.course_id= a.course_id
[한 걸음 더] 퍼센트를 나타내기
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
group by course_id
) a
inner join (
select course_id,
count(*) as cnt_total
from orders
group by course_id
) b on b.course_id= a.course_id
[반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!
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
group by course_id
) a
inner join (
select course_id,
count(*) as cnt_total
from orders
group by course_id
) b on b.course_id= a.course_id
inner join courses c on c.course_id = a.course_id
06. with절 연습하기
12) with 절로 더 깔끔하게 쿼리문을 정리하기
from 안에 있는 subquery는 with절을 이용하면 깔끔하게 정리할 수 있다
with table1 as () ,
table2 as ()
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 b.course_id= a.course_id
inner join courses c on c.course_id = a.course_id전체선택한 후에 실행하야지 아니면 오류난다.
07. 실전에서 유용한 SQL 문법 (문자열)
13) 문자열 데이터 다뤄보기
실제 업무에서는, 문자열 데이터를 원하는 형태로 한번 정리해야 하는 경우가 많습니다.
문자열 쪼개보기
이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!
SUBSTRING_INDEX(필드명, '기준', 1or-1)
select user_id,email, SUBSTRING_INDEX(email,'@',1) from users u
select user_id,email, SUBSTRING_INDEX(email,'@',-1) from users u
문자열 일부만 출력하기
SUBSTRING(필드명, 시작자리숫자, 몇개보여줄건지)
orders 테이블에서 날짜까지 출력하게 해보기
select order_no, created_at, SUBSTRING(created_at,1,10) from orders
일별로 몇 개씩 주문이 일어났는지 살펴보기
select SUBSTRING(created_at,1,10) as date, count(*) from orders
group by date
08. 실전에서 유용한 SQL 문법 (Case)
14) CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기
10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!',
평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?
select pu.user_id, pu.point,
(case when pu.point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as 응원
from point_users pu15) CASE: 실전을 위한 트릭!
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. 서브쿼리를 이용해서 group by로 통계를 낼 수 있습니다.
select a.lv as level,count(*) as cnt 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 as level,count(*) as cnt from table1 a
group by a.lv09. SQL 문법 복습. 또 복습! (초급)
16) [퀴즈] 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
select pu.point_user_id,
pu.point,
case when pu.point > (select avg(point) from point_users) then '잘하고 있어요'
else '열심히 합시다' end as msg
from point_users pu
17) [퀴즈] 이메일 도메인별 유저의 수 세어보기
내가 쓴 쿼리
select substring_index(email, '@',-1) as domain, count(*) as cnt_domain from users u
group by domain강의노트 정답 쿼리
select a.domain, count(*) as cnt_domain from (
select substring_index(email, '@',-1) as domain from users u
) a
group by a.domain
18) [퀴즈] '화이팅'이 포함된 오늘의 다짐만 출력해보기
select * from checkins c
where c.comment like '%화이팅%'
10. SQL 문법 복습. 또 복습! (중급)
19) [퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
select a.enrolled_id,
a.done_cnt,
b.total_cnt
from (
select enrolled_id, count(*) as done_cnt from enrolleds_detail ed1
where done = 1
group by enrolled_id
)a
inner join (
select enrolled_id ,count(*) as total_cnt from enrolleds_detail ed2
group by enrolled_id
) b on a.enrolled_id= b.enrolled_id
비율 구하기
select a.enrolled_id,
a.done_cnt,
b.total_cnt,
round(a.done_cnt/b.total_cnt,2) as ratio
from (
select enrolled_id, count(*) as done_cnt from enrolleds_detail ed1
where done = 1
group by enrolled_id
)a
inner join (
select enrolled_id ,count(*) as total_cnt from enrolleds_detail ed2
group by enrolled_id
) b on a.enrolled_id= b.enrolled_id
20) [퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기
select ed.enrolled_id,
sum(ed.done) as done_cnt,
count(ed.done) as total_cnt,
round(sum(ed.done)/count(ed.done),2) as ratio
from enrolleds_detail ed
group by ed.enrolled_id
21) [함께] 그러나, 더 간단하게 만들 수 있지 않을까!
저번 수업에 들었던 것이 기억이 나서 간단한거 먼저 써보았다.
select ed.enrolled_id,
sum(ed.done) as done_cnt,
count(ed.done) as total_cnt
from enrolleds_detail ed
group by ed.enrolled_id
11. 끝 & 숙제 설명
4주 동안 고생 많았습니다! 숙제로 유종의 미를 같이 거둬볼까요?
마지막 숙제는, 문제를 푸는 것이 아니라, 문법을 정리해보는 것이에요!
SQL 쿼리문은 문법 종류가 다양하지 않아서, 한번 정리해두면 정말 요긴하게 써먹을 수 있습니다!
블로그를 하나 만들고 문법을 정리한 다음, 제출해주세요!
(이미 있는 분들은 쓰던 것을 사용하셔도 무방합니다)
'개발자 되는 중 > 개발 공부' 카테고리의 다른 글
| 후발대 강의 - 배열 (0) | 2022.12.05 |
|---|---|
| 얄팍한 코딩 HTML 개발일지 (0) | 2022.10.27 |
| 스파르타 코딩 SQL 개발일지 3주차 (복습) (0) | 2022.10.20 |
| 스파르타 코딩 SQL 개발일지 2주차 (복습) (0) | 2022.10.20 |
| 스파르타 코딩 SQL 개발일지 1주차 (복습) (0) | 2022.10.20 |