Weekly I Learned (SQL, 12/27–1/2)
[수업 목표]
- Subquery(서브쿼리)의 사용 방법을 배워본다
- 실전에서 유용한 SQL 문법을 더 배워본다
- SQL을 사용하여 실전과 같은 데이터분석을 진행해본다
where 필드명 in (subquery)
1 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’
1–1 우선 kakaopay로 결제한 user_id를 모두 구해보기 (작은 chunk)
select user_id from orders where payment_method = ‘kakaopay’
1–2 그 후에, user_id가 작은 chunk에 있는 유저들만 골라보기
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 필드명, 필드명, (subquery) from ..
select c.checkin_id,
c2.title,
c.user_id,
c.likes,
(
select round(avg(likes),1) from checkins c
where course_id = c.course_id
) as course_avg
from checkins c
inner join courses c2 on c.course_id = c2.course_id
from 절에 들어가는 subquery
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
Quiz
with table1 as (
select enrolled_id, count(done) as done_cnt from enrolleds_detail ed
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail ed
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