엑셀보다 쉬운 SQL 4주차
빠르게 강의를 듣다 보니 벌써 4주차를 끝내고 쓰는 마지막주차 회고다. 제일 많이 쓰이고 조금은 심화된 내용을 다루는 4주차였는데 계속 반복해서 쿼리를 작성해보고 연습을 많이 해봐야 알 것같다! 끝나도 끝난게 아닌..
-
이번 주차에서 다룰 내용은 Subquery(서브쿼리)의 사용 방법에 대해서 배운다.
또한 실전에서 유용한 SQL문법을 더 배워보고 실전과 같은 데이터분석을 진행한다. -
Subquery란? 쿼리 안의 쿼리라는 의미이며 하위 쿼리 결과를 상의 쿼리에서 사용하면 SQL쿼리가 훨씬 간단해진다!
(예시) kakaopay로 결제한 유저들의 정보 보기
→ 우선 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'이렇게도 볼 수 있지만!
- 우선 kakaopay로 결제한 user_id를 모두 구해본다. → K라고 해보자.
select user_id from orders
where payment_method = 'kakaopay'- 그 후에, 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'
)자주 쓰이는 Subquery 유형 알아보기
Subquery는 where,select,from 절에서 유용하게 쓰인다!
1. Where에 들어가는 Subquery
where은 조건문이다. 서브쿼리의 결과를 조건에 활용하는 방식으로 사용한다.
→ where 필드명 in (subquery)
(예시) 카카오페이로 결제한 주문건 유저들만 유저 테이블에서 출력하기
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) 조건에 맞는 결과 출력
2. Select 에 들어가는 Subquery
Select는 결과를 출력해주는 부분이다. 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용한다.
→select 필드명, 필드명, (subquery) from ..
(예시) 앞서 보았던거처럼, '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 보기
1) 먼저 평균을 구해본다. user_id='4b8a10e6'를 예시로 한다.
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) 함께 출력해준다!
3. 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
여기서 해당 유저 별 포인트를 보고싶다면?
→ 그러면, 포인트와 like의 상관정도를 알 수 있다.
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_id
- 쿼리가 실행되는 순서
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행된다!
* Subquery 연습해보기 (where, select, from, inner join)
1. Where 절에 들어가는 Subquery 연습해보기
[연습1] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
(힌트)point_users 테이블을 이용해서 avg를 구하고, 다시 point_users와 조인하기!
select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2);👉위와 같이, 같은 테이블을 Subquery로 사용할 수도 있다.
[연습2]이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
(힌트)위 구문의 서브쿼리 내에서 users와 inner join하기
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 name = '이**')👉필요한 경우, Subquery 안에서 여러 테이블을 Join 할수도 있다.
2. Select 절에 들어가는 Subquery 연습해보기
[연습1] checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
select checkin_id, course_id,user_id ,likes,
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id ) as course_avg
from checkins c
[연습2] checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
연습1에서 실습했던 것에, courses 테이블을 join하기!
select checkin_id, c3.title, user_id, likes,
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses c3
on c.course_id = c3.course_id;
3. From 절에 들어가는 Subquery 연습해보기
[준비1] course_id별 유저의 체크인 개수를 구해보기!
→ checkins 테이블을 course_id로 group by 하고 distinct로 유저를 세본다.
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id 
[준비2] course_id별 인원을 구해보기!
select course_id, count(*) as cnt_total from orders o
group by course_id 
[진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기
→ 준비1과 준비2를 inner join하기
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
[반 걸음 더] 강의 제목 나타내기
select c.title, b.cnt_checkins, a.cnt_total, ( b.cnt_checkins / a.cnt_total ) as ratio from
(
select course_id, count( * ) as cnt_total from orders o
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
inner join courses c
on a.course_id = c.course_id 
* with절 연습하기
→ with절로 더 깔끔하게 쿼리문을 정리하기
with table1 as (
select course_id, count(*) as cnt_total from orders o
group by course_id
), table2 as (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
)
select c.title,
b.cnt_checkins,
a.cnt_total,
( b.cnt_checkins/a.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 문법(문자열, Case)
- 문자열 데이터 다뤄보기
(예시) 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오기
→ SUBSTRING_INDEX 라는 문법을 사용한다.
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
👉 @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
👉@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!
- 문자열 일부만 출력하기
(예시) orders 테이블에서 created_at을 날짜까지만 출력해보자!
SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
select order_no, created_at, substring(created_at,1,10) as date from orders
(예시) 일별로 몇 개씩 주문이 일어났는지 살펴보자!
select substring(created_at,1,10) as date, count( \* ) as cnt_date from orders
group by date
* CASE: 경우에 따른 원하는 값을 새 필드에 출력해보기
(예시)포인트 보유액에 따라 다르게 표시해주기
10000점 보다 높으면 '잘하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!'
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu
- CASE: 실전을 위한 트릭!
- 우선 몇 가지로 구분하고
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- 서브쿼리를 이용해서 group by 해주기
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- wuth절과 함께하기
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 문법 복습하기
[퀴즈1]이메일 도메인별 유저의 수 세어보기
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
[퀴즈2] '화이팅'이 포함된 오늘의 다짐만 출력해보기
select * from checkins
where comment like '%화이팅%'
[퀴즈3] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
(힌트) subquery 두 개를 만들어놓고, inner join!
살펴볼 테이블: enrolled_detail
done_cnt는 들은 강의의 수(done=1),
total_cnt는 전체 강의의 수
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
[퀴즈4] 수강등록정보(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,
round(a.cnt_done/b.cnt_total,2) as ratio
from lecture_done a
inner join lecture_total 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이렇게 해도 같은 결과가 나온다!
