스파르타 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_id

a 가

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_id

select 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 (
	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