스파르타 코딩클럽 SQL 4주차

[수업 목표]

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

01. 오늘 배울 것

1.  Subquery: 원하는 데이터를 더 쉽게 얻어보기
2.  실전에서 유용한 SQL 문법들

---

02. Query in Query!  Subquery

1) Where에 들어가는 Subquery

• Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용
• where 필드명 in (subquery)

2) Select에 들어가는 Subquery

• Select는 결과를 출력해주는 부분
• 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
• select 필드명, 필드명, (subquery) from ...

3) From에 들어가는 Subquery

• 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용
• 가장 많이 사용되는 유형

03. Subquery 연습하기

1) Where 절에 들어가는 Subquery 연습해보기

• 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select * from point_users pu
where pu.point > 
(select avg(pu2.point) from point_users pu2)

---

• 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

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 like '이%')

---

2) Select 절에 들어가는 Subquery 연습해보기

• 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

---

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

SELECT c.checkin_id, 
       cs.title,
       c.user_id, 
       c.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 cs 
on cs.course_id = c.course_id

---

3) From 절에 들어가는 Subquery 연습해보기

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

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
group by course_id 

 

• [진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기

SELECT a.course_id, a.cnt_checkins, b.cnt_total 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

 

• 퍼센트를 나타내기

SELECT a.course_id, 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

 

• 강의 제목도 나타나면 좋겠네요!

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

---

03. with절 연습하기

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

• 코스제목별 like 개수, 전체, 비율

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

• 이렇게 계속 Subquery를 사용하면 헷갈리고 보기 힘들어져요
• → 그 때 쓰는 것이 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

---

04. 실전에서 유용한 SQL 문법 (문자열, Case)

• 문자열 쪼개기 ( SUBSTRING_INDEX )

• 이메일에서 아이디만 가져와보기
• @를 기준으로 텍스트를 쪼개고 그 중 첫 번째 조각을 가져온다

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

• 이메일에서 이메일 도메인만 가져와보기
• @를 기준으로 텍스트를 쪼개고 그 중 마지막 조각을 가져온다.

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

 

 

• 문자열 일부만 출력하기 ( SUBSTRING ) 

• orders 테이블에서 날짜까지 출력하게 해보기
• 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: 실전을 위한 트릭!

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

---

05. 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_domain from
(select SUBSTRING_INDEX(email,'@',-1)as domain from users) a
group by domain

 

 

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

' LIKE'

SELECT * from checkins
where comment like '%화이팅%'

 

 

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

• subquery 두 개를 만들어놓고, inner join!
• 살펴볼 테이블: enrolled_details
• done_cnt는 들은 강의의 수(done=1)
• total_cnt는 전체 강의의 수

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 
from table1 a 
inner join table2 b on a.enrolled_id  = b.enrolled_id 

 

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

 

 

6) 그러나, 더 간단하게 만들 수 있지 않을까!

select enrolled_id,
       sum(done) as cnt_done,
       count(*) as cnt_total,
       round(sum(done)/count(*),2) as ratio
  from enrolleds_detail ed
 group by enrolled_id