스파르타 SQL 4주차 내용 정리
수업 목표
1. Subquery(서브쿼리)의 사용 방법을 배워본다
2. 실전에서 유용한 SQL 문법을 더 배워본다
3. SQL을 사용하여 실전과 같은 데이터 분석을 진행해본다.
Subquery: 원하는 데이터를 더 쉽게 얻어보기
👉 Subquery란? 쿼리 안의 쿼리라는 의미.
하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해진다.
SubQuery가 익숙해지면, With 이용해서 더 훌륭한 구문을 만들 수 있다.
하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것 을 의미.
kakaopay로 결제한 유저들의 정보 보기
1. users 와 orders 의 inner join으로 파악하기
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;
SELECT * FROM orders; 로 미리 조망 => user_id 공통
1) select * from users u
2) inner join orders o on u.user_id = o.user_id
3) where o.payment_method = 'kakaopay'
4) 1수정. select u.user_id, u.name, u.email from users u
조금 더 직관적으로 바꾸기
1. 우선 kakaopay로 결제한 user_id를 모두 구해보기 → K 라고 하자.
select user_id from orders
where payment_method = 'kakaopay'
2. 그 후에, user_id가 K 에 있는 유저들만 골라보기 => 서브쿼리
select u.user_id, u.name, u.email from users u
where u.user_id in (
select user_id from orders
where payment_method = 'kakaopay'
)
괄호 안에 있는 것이 서브쿼리. 괄호 안에 있는 것만 보겠다.
지금 상황에서는
select user_id from orders
where payment_method = 'kakaopay' 이거 자체가 괄호 안으로 들어갔다.
Subquery 본격 사용해보기
- 안에 있는 것부터 결과를 만들고 밖으로 간다.
- 서브쿼리는 where, select, from 절에서 유용하게 사용될 수 있다.
where에 들어가는 서브쿼리
카카오페이로 결제한 주문건 유저들만 출력하기
select * from users u
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay');
쿼리가 실행되는 순서
(1) from 실행: users 데이터를 가져와 줌
(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
(4) 조건에 맞는 결과 출력
Select 에 들어가는 Subquery
- Select는 결과를 출력해주는 부분
- 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용한다.
- select 필드명, 필드명, (subquery) from .. 이렇게
'오늘의 다짐' 좋아요의 수가, 본인이 평소 에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 알아보기
1. 평균 구하기
select avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'
2. 서브쿼리 포함하여 코드 작성하기
select c.checkin_id, c.user_id, c.likes,
(select avg(likes) from checkins c2
where c2.user_id = c.user_id) as avg_like_user
from checkins c;
쿼리가 실행되는 순서
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력해준다
From 에 들어가는 Subquery (가장 많이 사용)
- From은 언제 사용하는가.
- 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용한다.
유저별 좋아요 평균 구하기
- checkins 테이블을 user_id로 group by 하기
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id<작성 순서>
1) select * from checkins
2) group by user_id
3) 1수정. select user_id round (avg(likes), 1) from checkins
서브쿼리로 정리하면
select pu.user_id, a.avg_like, pu.point from point_users pu
inner join
(
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
)
a on pu.user_id = a.user_ida 가
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
지금 요 테이블을 의미
쿼리가 실행되는 순서
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!
Subquery 연습해보기(where, select)
[연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
- 평균 포인트는 5380점.
- point_users 테이블을 이용해서 avg를 구하고, 다시 point_users와 Join하기
select * from point_users pu
where point > 5000
평균 구하는 식
select avg(point) from point_users
포인트 평균 조건으로 Subquery 연습해보기
select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2);
[연습] 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
- 이씨 성을 가진 유저들의 평균 포인트는 7454점
- 위 구문의 서브쿼리 내에서 users와 inner join 하기
전체 모양
select * from point_users pu
where point > (
-- 이 부분에 들어갈 것을 만들면 됨.
)
select * from point_users pu
where pu.point >
(select avg(pu2.point) from point_users pu2
inner join users u
on pu2.user_id = u.user_id
where u.name = "이**");
select * from point_users
select * from users =======> 공통 user_id
<작성 순서>
1. inner 조인으로 풀기
1) select * from point_users pu
2) inner join users u on pu.user_id = u.user_id
3) where u.name = '이**'
4) 1수정 select avg(point) from point_users pu
2. 풀이 2.
1)) select * from point_users pu
2) where user _id in (
3) select * from users where name ='이**'
)
4) 3수정 select user_id from users where name = '이**'
5) 1수정 select avg(point) from point_users pu
정리하면 서브 쿼리 안에 서브 쿼리
select * from point_users pu
where point > (
select avg(point) from point_users pu
where user_id in (
select user_id from users
where name = '이**'
)
)
괄호 안에 들어갈 문장
(
1) select avg(point) from point_users pu
2) where user_id in (select user_id from users wher name = '이**'
)
Select 절에 들어가는 Subquery 연습해보기
[연습] checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
course_id별 평균 like수 붙이기
select c.checkin_id,
c.title,
c.user_id,
c.likes,
(select round(avg(c.likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
예시 답안
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
// 이것과 courses를 조인
inner join courses c2 on c.course_id = c2.course_id
Subquery 연습해보기(from, inner join)
[준비 1] course_id별 유저의 체크인 개수를 구해보기
- checkins 테이블을 course_id로 group by => distinct로 개수 세기
course_id별 체크인 개수
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id<작성 순서>
1) select * from checkins
2) group by course_id
3) 1수정 select course_id, count(*) from checkins
4) 재수정 select course_id count(distinct(user_id) as cnt_checkins from checkins
[준비 2] course_id별 인원을 구해보기
- orders 테이블을 course_id로 group by 하면 된다.
select course_id, count(*) as cnt_total from orders
group by course_id<작성 순서>
1) select * from orders
2) group by course_id
3) 1수정 select course_id, count(*) as cnt_total from orders
[결과물] course_id별 좋아요 개수에 전체 인원을 붙이기
- 준비 1과 준비 2를 inner join 하면 된다.
select * from
(
-- 준비 1
) a
inner join
(
-- 준비 2
) b on a.course_id = b.course_id
최종
select a.course_id, b.cnt_checkins, a.cnt_total from
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b
on a.course_id = b.course_id
[추가]퍼센트로 나타내려면?
select a.course_id,
b.cnt_checkins,
a.cnt_total,
(b.cnt_checkins/a.cnt_total) as ratio from -- 비율
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b
on a.course_id = b.course_id
[마무리] 강의 제목과 함께 나타내기
- courses 테이블과 Join 하면 된다.
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 a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id -- 이러면 타이틀이 붙여져 나오고
With절 연습하기
* with절로 더 깔끔하게 쿼리문을 정리하기
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 a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id
이렇게 계속 서브쿼리가 붙으면, inner join 안쪽이 너무 헷갈린다.
→ 그 때 쓰는 것이 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
실전에서 유용한 SQL 문법 (문자열)
문자열 쪼개보기
[문제] 이메일에서 아이디만 가져와보기
- @를 기준으로 텍스트롤 쪼개고, 그 중 첫 번째 조각을 가져온다.
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users[문제] 이메일에서 이메일 도메인만 가져와보기
- @를 기준으로 텍스트롤 쪼개고, 그 중 마지막 조각을 가져온다.
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
문자열 일부만 출력하기
[문제 1] orders 테이블에서 날짜까지 출력하게 해보기
select order_no, created_at, substring(created_at,1,10) as date from orders
[문제 2] 일별로 몇 개씩 주문이 일어났는지 살펴보기
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
실전에서 유용한 SQL 문법(Case)
- 10,000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!, 평균보다 낮으면 '조금 더 달려주세요!' 를 표기하려면?
- 구간별 표기 하고싶다.
이게 자바에서 switch문과 비슷!!!!!
[문제] 포인트 보유액에 따라 다르게 표시해주기
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;
Subquery를 이용한 통계 내기
1. 몇 가지로 기준을 나누고
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
2. 서브쿼리를 이용하여 통계 내기
select level, count(*) as cnt from (
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
) a
group by level
3. when절 활용하여 활용점정 만들기
with table1 as (
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
)
select level, count(*) as cnt from table1
group by level
SQL 문법 복습. 또 복습! (초급)
[퀴즈] 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기
- CASE 문법 사용, CASE 안에서 Subquery로 평균을 계산하여 비교하기
select pu.point_user_id, pu.point,
case
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu(select avg(pu2.point) from point_users pu2) = select avg(point) from point_users
[퀴즈] 이메일 도메인별 유저의 수 세어보기
- Substring_Index와 Group By를 사용하면 됨.
예시답안
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
[퀴즈] '화이팅'이 포함된 오늘의 다짐만 출력해보기
- like 활용하기
예시답안
select * from checkins c
where c.comment like '%화이팅%'
SQL 문법 복습. 또 복습! (중급)
[퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
- subquery 두 개를 만들어놓고, inner join
- 살펴볼 테이블: enrolled_details
- done_cnt는 들은 강의의 수(done=1)
- total_cnt는 전체 강의의 수
작성 순서
1) select * from enrolldes_ detail
2) where done = 1
3) group by enrolled_id
4) 1수정 select enrolled_id, count(*) from enrolleds_detail
5) 1또수정 select enrolled_id, count(*) from as done_cnt enrolleds_detail
2번째 작업 - done 빼기
1) select enrolled_id, count(*) from as total_cnt enrolleds_detail
2) group by enrolled_id
이제 서브쿼리로 잇기
select *from (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
) a
inner join (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
) b on a.enrolled_id = b.enrolled_idselect a.enrolled_id, a.done_cnt, b.total_cnt from (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
) a
inner join (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
) b on a.enrolled_id = b.enrolled_idselect a.enrolled_id, a.done_cnt, b.total_cnt from (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
) a
inner join (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
) b on a.enrolled_id = b.enrolled_id
select a.enrolled_id, a.done_cnt, b.total_cnt from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id
with 절로 변경하기
with table1 as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
)
정답 쿼리
with lecture_done as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
)
select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id
[퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기
- 진도율 = (들은 강의의 수 / 전체 강의 수)
with table1 as (
select enrolled_id, count(*) as done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail
group by 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
더 간단하게 만들기
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id