[목표]

1. Subquery(서브쿼리)의 사용 방법을 배우기
2. 실전에서 유용한 SQL 문법을 더 배우기
3. SQL을 사용하여 실전과 같은 데이터분석을 진행해보기

01. 원하는 데이터를 더 쉽게: Subquery

1) Subquery 사용방법 익혀보기

👉 Subquery란, 하나의 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'

방법 2. Subquery로!

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'
)

02. 자주 쓰이는 Subquery 유형

: Subquery는 where, select, from 절에서 유용하게 사용될 수 있어요!

1) Where 에 들어가는 Subquery

👉 Where은 조건문이죠?
Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다.

• 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하고 싶을 때 사용하면 딱이겠죠!

• select * from (subquery) 별칭
• select * from 테이블t
  inner join (subquery) 별칭a on t.key=a.key

• A : 우선 유저 별 좋아요 평균을 먼저 구해볼까요?
  → checkins 테이블을 user_id로 group by 하면 되겠죠?

select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

• 이제 A에서 해당 유저 별 포인트를 보고 싶다면?
  → 그러면, 포인트와 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가 실행!

03. Subquery 연습

1) Where 절에 들어가는 Subquery

👉 [오늘의 팁!]

• 같은 테이블을 Subquery로 사용할 수도 있어요.
• 필요한 경우, Subquery 안에서 여러 테이블을 Join 할수도 있어요.

▶︎ 포인트가 평균보다 많은 사람들의 데이터를 추출해보자! (평균 포인트는 5380점)

• 힌트! → point_users 테이블을 이용해서 avg를 구하고, 다시 point_users와 조인하세요!

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 pu.point > 
	 (select avg(pu2.point) from point_users pu2
	  inner join users u on pu2.user_id = u.user_id
	  where u.name = '이**')

2) Select 절에 들어가는 Subquery

▶︎ checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select c1.checkin_id, c1.course_id, c1.user_id, c1.likes,
 	   (select round(avg(c2.likes),1) from checkins c2 
	    where c1.course_id = c2.course_id) as avg_like 
  from checkins c1
  

▶︎ checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

• 직전에 실습했던 것에, courses 테이블을 join 하면 되겠죠?

select c1.checkin_id, c3.title, c1.user_id, c1.likes,
 	   (select round(avg(c2.likes),1) from checkins c2 
	    where c1.course_id = c2.course_id) as avg_like 
  from checkins c1
 inner join courses c3 on c1.course_id = c3.course_id 

3) From 절에 들어가는 Subquery

▶︎ course_id별 like 개수에 전체 인원을 붙이기

[준비 1] course_id별 유저의 체크인 개수를 구해보기!

select course_id, count(distinct(user_id)) as checkin_cnt from checkins
group by course_id 

[준비 2] course_id별 인원을 구해보기!

select course_id, count(*) as total_cnt from orders
group by course_id

[최종] 준비1과 준비2를 inner join 하면 됩니다!

select a.course_id, a.checkin_cnt, b.total_cnt 
  from (
	select course_id, count(distinct(user_id)) as checkin_cnt from checkins
	group by course_id 
) a 
inner join (
	select course_id, count(*) as total_cnt from orders
	group by course_id 
) b on a.course_id = b.course_id 

[한걸음 더] 전체 중 얼마나 like를 하는지 알아보면 좋겠죠? 퍼센트 나타내고, 강의 제목도 표기해 주세요.

select c.title, a.checkin_cnt, b.total_cnt,
       (a.checkin_cnt/b.total_cnt) as ratio
  from (
	select course_id, count(distinct(user_id)) as checkin_cnt from checkins
	group by course_id 
) a 
inner join (
	select course_id, count(*) as total_cnt 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 

04. with절 연습

1) with 절로 더 깔끔하게 쿼리문을 정리하기

다시 위의 코드를 볼까요?

select c.title, 
       a.checkin_cnt, 
       b.total_cnt,
       (a.checkin_cnt/b.total_cnt) as ratio
  from (
	select course_id, count(distinct(user_id)) as checkin_cnt from checkins
	group by course_id 
) a 
inner join (
	select course_id, count(*) as total_cnt 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절은 쿼리 젤 위에 작성하고, 실행할 때는 블록으로 감싸야 오류가 안나요!

with table1 as (
	select course_id, count(distinct(user_id)) as checkin_cnt from checkins
	group by course_id 
), table2 as (
	select course_id, count(*) as total_cnt from orders
	group by course_id
)

select c.title, 
       a.checkin_cnt, 
       b.total_cnt,
       (a.checkin_cnt/b.total_cnt) 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 

05. 실전에서 유용한 SQL 문법 (문자열)

1) 문자열 쪼개보기 : SUBSTRING_INDEX

👉 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!

SUBSTRING_INDEX 라는 문법을 사용하면 됩니다.

• SUBSTRING_INDEX(email, '@', 1)
  @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
• SUBSTRING_INDEX(email, '@', -1)
  @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

▶︎ 이메일에서 아이디만 가져와보기

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

▶︎ 이메일에서 이메일 도메인만 가져와보기

select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

2) 문자열 일부만 출력하기 : SUBSTRING

👉 orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!

SUBSTRING 이라는 문법을 사용하면 됩니다.
- substring(created_at,1,10) SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

▶︎ orders 테이블에서 날짜까지 출력하게 해보기

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

06. 실전에서 유용한 SQL 문법 (Case)

1) CASE : 경우에 따라 원하는 값을 새 필드에 출력해보기

👉 특정 조건에 따라, 데이터를 구분해서 정리해주고 싶을 때가 있겠죠?
이런 경우에 CASE 라는 문법이 사용됩니다.

[예시]
10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?

▶︎ 포인트 보유액에 따라 다르게 표시해주기

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;

2) CASE: 실전을 위한 트릭!

👉 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 lv
from point_users pu

2. 서브쿼리를 이용해서 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 lv
	from point_users pu
) a
group by lv

3. with 절과 함께하면 금상첨화죠!

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 lv
	from point_users pu
)

select level, count(*) as cnt from table1
group by lv

07. SQL 문법 복습. 또 복습! (초급)

퀴즈 1) 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

👉 [힌트!] 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

퀴즈 2) 이메일 도메인별 유저의 수 세어보기

👉 [힌트!] SUBSTRING_INDEX와 Group by를 잘 사용하면 끝!

select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain

퀴즈 3) '화이팅'이 포함된 오늘의 다짐만 출력해보기

👉 이번 건 쉽죠? 잊어버리셨을까봐! like를 어떻게 썼더라~

select * from checkins c
where c.comment like '%화이팅%'

08. SQL 문법 복습. 또 복습! (중급)

퀴즈 1) 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

👉 [힌트!] subquery 두 개를 만들어놓고, inner join!

살펴볼 테이블: enrolled_details

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

퀴즈 2) 수강등록정보(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