스파르타 SQL 4주차 일지
엑셀 보다 쉬운 SQL 강의 4주차.
4주차에서는, 보다 더 복잡하고 정교하게 쿼리문을 구성할 수 있도록 도와주는 Subquery 를 배워볼거야.
다음으로 그러한 subquery 를 좀 더 간결하게 보일 수 있도록 하는 with 구문에 대해서도 배워볼거야.
마지막으로, 지저분하고 복잡한 데이터들의 문자열을 한 번에 정리하거나, 조건에 따라 데이터를 구분하는 등 직관성있는 데이터로 가공시킬 수 있는 방법도 배워볼거야.
추가로, case when 이라는 조건문도 배워볼거야.
먼저, Subquery에 대해서 알아가보자.
——
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'
——
=> 유저들의 정보가 담겨있는 users 테이블과 유저들의 강의 구매 정보가 담겨 있는 orders 테이블을 inner join.
이러면 강의를 구매한 적이 있는 유저들이 각각 어떤 강의를 결제 하였는지 볼 수 있어. 이 상황에서 user_id, name, email 만 보고 있는 거야.
Subquery를 이용해서 위와 같은 결과를 도출해 낼 수 있어.
——
select user_id, name, email from users u
where user_id in (
select user_id from orders o
where payment_method = 'kakaopay'
)
——
=> users 테이블에서 데이터를 가져와 볼건데, user_id 필드값이 in () 에 포함되는 것만 가져오고 싶어.
() 안에는 user_id 라는 동일한 필드를 지닌 orders 테이블의 데이터를 가져오고 있는데,
orders 중에 결제 방식이 카카오페이인 결제 기록들의 user_id 만을 보여주고 있는거야.
그럼 select user_id 에 의해서, 결제 방식이 카카오페이인 user_id 들만 간추려 지겠지?
이 user_id 를 포함하는 데이터들을 users 테이블에서 추출하는거지.
실행순서는 가장 안에 있는 쿼리문부터 점점 바깥에 있는 쿼리문 순서로.
* from -> subquery -> where -> select
즉, 큰 쿼리문 안에 참조용 으로 들어가는 작은 쿼리를 sub query 라고 하는 거야.
이 참조용 서브 쿼리는 where절, select절, from절에 들어가는 경우도 있을 수 있어.
_________________________________________________
Where, select, from 절에서 각각 subquery를 알아보자.
where절에서 서브쿼리를 사용하는 경우는 바로 윗 경우.
서브쿼리의 결과를 조건에 활용하는 방식으로 유용하게 쓰여.
select절에 들어가는 subquery 알아보기.
——
select avg(likes) from checkins c
where user_id = '4b8a10e6'
——
=> 예를 들어서, ‘오늘의 다짐’ 을 등록한 유저가 평균적으로 얼마만큼은 ‘좋아요’ 를 받고 있는지 알아본다고 쳐.
——
SELECT c.checkin_id,
c.user_id,
c.likes,
(
select avg(likes) from checkins c
where user_id = c.user_id
) as avg_likes_user
from checkins c
——
=> select 로 내가 원하는 필드들의 값을 뽑아볼건데, select 중 하나를 subquery 로 작성하는거야.
해당 subquery는 checkins 테이블의 c.user_id 가 평균적으로 받게 되는 좋아요의 평균을 보여주고 있어.
기존의 데이터와 함께, 추가적으로 평균값이라는 필드에 대한 데이터들을 보여주는거야.
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력해준다!
마치 for문을 돌리듯이.
다음으로 from절에 subquery가 들어가는 경우에 대해 공부해보자.
From 절에서 subquery 가 들어가는 경우는 매우 많이 찾아볼 수 있고, 숙달해 두면 실무에서 두고두고 많이 활용할 수 있어.
From 절에서 subquery 를 사용한다는 것은, subquery 로 정리된 데이터를 하나의 테이블처럼 사용한다는 것.
유저별 좋아요 평균을 먼저 구해보자. 그리고 point_users 테이블도 함께 보자.
——
select user_id, round(avg(likes), 2) as avg_likes from checkins c
group by user_id
——
——
select user_id, point from point_users pu
——
=> 그렇다면, point 가 많은 유저들은, 좋아요의 평균도 높을까?
——
select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
select user_id,
round(avg(likes), 2) as avg_likes
from checkins c
group by user_id
) a
on pu.user_id = a.user_id
——
=> subquery 로 작성한 select 문을 inner join 으로 가져오는거야.
point_users 테이블에서는 유저들의 포인트를, 서브쿼리문 에서는 유저들의 평균 좋아요 수치를.
포인트가 높은 유저들은 평균 좋아요 수치도 높을까? 하면서 데이터를 같이 볼 수 있는 거지.
기본적으로 쿼리문은 ‘쪼개서’ 생각하는게 편해.
예를 들어서, 내가 만들고 싶은 테이블의 모습이 있어. 그 테이블을 구성하는 필드들 중에서 일부는 내가 만들 줄 알겠어.
그럼 일단 만들 수 있는 테이블부터 따로 만들고, 그것들을 join 이나 subquery 를 이용한다든지 해서 엮을 수 있는거야.
________________________________________________
Subquery 연습해보기.
퀴즈 1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기. 포인트가 평균보다 많은 사람들의 데이터를 추출해보자
——
select * from point_users pu
where point > (
select round(AVG(point), 1) from point_users pu
)
——
=> point_users 테이블에 등록되어 있는 point 들의 평균값을 구한 뒤, 그 값보다 point 가 높은 데이터들만 추출.
subquery 문을 where 절에 활용한 경우. 같은 테이블 끼리도 subquery로 연결할 수 있음을 보여주는 예시.
퀴즈 2. 이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터를 추출해보자
——
select * from point_users pu2
where point > (
select round(avg(pu.point)) from point_users pu
inner join users u
on pu.user_id = u.user_id
where u.name = '이**'
)
Or
select * from point_users pu2
where point > (
select round(avg(pu.point)) from point_users pu
inner join (
select user_id, name from users u
where name = '이**'
) u
on pu.user_id = u.user_id
)
——
=> 2개의 쿼리문 모두 결과는 같아. 과정이 조금 다를 뿐.
위에 건 이씨의 평균 포인트를 테이블끼리의 inner join 으로 구했고, 아래는 쿼리문으로 만든 데이터 테이블을 inner join 했어.
where point > () 구절의 () 안에는 round(avg(pu.point)) 라는 필드에 속한 평균값인 7454야.
즉, 이 경우에는 () 안에 하나의 값이 리턴된다는 느낌이지.
퀴즈 3. checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
* select 절에 들어가는 subquery 를 연습해보자.
——
select checkin_id, course_id, user_id, likes,
(
select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id
) as course_avg
from checkins c
——
=> 서브쿼리문에 group by 를 넣어주지 않아도 돼. 어차피 where 로 알아서 course_id 랑 매칭되면서 코스별로 평균값을 뽑아내 줘.
퀴즈 4. checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
——
select c.checkin_id, c3.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 c3
on c.course_id = c3.course_id
——
다음 퀴즈. From 절에서 쓰이는 subquery 연습해보기.
퀴즈 5. 강의 별로 체크인(오늘의 다짐을 남긴 횟수) 횟수를 알고 싶어. 동시에, 각 강의 당 얼마나 많이 결제 되었는지도 알아야 해.
그리고 결제 횟수에 비례한 오늘의 다짐 개수로 나타내고.
순차적으로 진행할거야.
——
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
=> 우선 checkins 테이블에서 course_id, 그니까 강의 종류별로 그룹을 지어.
그리고 count(distinct(user_id)) 필드를 만들건데, 이건 왜 만드냐,
‘오늘의 다짐’이 각 코스 별로 몇 개가 등록되어 있는지를 알고 싶은데, 유저에 따라선 한 강의에 1주차, 2주차 이렇게 다수의 다짐을 남겼을 수도 있어.
그래서 같은 유저가 남긴 오늘의 다짐의 중복은 다 제외하고 싶은거야.
그 다음으로, 각 코스별로 얼마나 많이 결제가 되었는지도 알고 싶어.
select course_id, count(*) from orders o
group by course_id
=> orders 테이블에서 각 코스별로 얼마나 많은 결제횟수가 있었느냐.
그럼 이제, 코스별로 유저들의 ‘오늘의 다짐’ 개수와, 비율을 알고 싶어서 추출한 ‘각 코스별 결제 총횟수’ 를 합쳐보자.
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 o
group by course_id
) b
on a.course_id = b.course_id
=> 하나는 from 절로, 하나는 join table 용으로. 사실 둘 다 from 절이지. 양 테이블을 동시에 들고와서 합치는 방법.
다음으로, 코스별 총 결제 횟수에 따른 좋아요 개수의 비례를 구해보자. 그것도 테이블에 같이 나타내주는 거지.
추가로, 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 o
group by course_id
) b
on a.course_id = b.course_id
inner join courses c
on c.course_id = a.course_id
——
=> 크게 보면 select from 한 번으로 테이블 들고 오고, inner join 연속으로 2번 한거야 그냥.
우선 from 으로 가져올 테이블을 설계하고, 거기서붜 하나씩 하나씩 붙여가면 돼.
_________________________________________________
With 절
예제. 바로 위에서 했던, 코스별 총 결제 수와 좋아요 수, 결제 횟수 당 좋아요 횟수 비례 구하기 sql문을 계속 사용.
——
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 o
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 c.course_id = a.course_id
——
=> 마치 변수에 저장해 두었다가 함수에서 사용하듯, subquery문으로 생성된 테이블을 with 절을 사용하여
임시 테이블에 저장해 두는 거야. 그리고 사용할 때는 임시 테이블 이름으로 사용할 수 있고.
이러면 보기에도 깔끔하고, SQL문의 구성도 알기 쉬워.
보통 subquery는 from절로 사용하는 경우가 대부분인데, 이럴 때 with절을 많이 이용하면 쿼리가 깔끔해지는거지.
__________________________________________
실전에서 유용한 SQL 문법들. (문자열)
- 문자열을 가지고 노는 법
- Case 문 (조건문)
실무에서는, 데이터에 담겨 있는 문자열이 내가 원하는 형태가 아닐 경우가 많아.
그래서 문자열을 쪼개거나 변형시켜야 하는 경우가 많아.
특정 문자를 기점으로 문자열을 앞 뒤로 자르자.
——
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users u
——
=> SUBSTRING_INDEX 라는 문법을 사용했어.
필드값인 문자열을 내가 원하는 방식으로 잘라서 원하는 부분만 사용하는 방법인데,
위의 예시의 경우 email 필드의 값을 자를건데, @를 중심으로 해서 자를거다, 라는 내용.
세 번째 인자가 1일 경우 첫 번째 조각을, -1 일 경우 마지막 조각을 보여줘.
ex) 이 경우 1 이면 이메일 중 @의 앞부분. -1이면 @의 뒷부분.
이번엔 문자열 에서 일부만 출력해보자.
——
select order_no, SUBSTRING(created_at, 1, 10) as date, count(*) from orders o
group by date
——
=> orders 테이블에서 날짜별로 그룹을 지어서 해당 날짜에 총 몇 개의 order가 발생했는지 알 수 있어.
SUBSTRING(created_at, 1, 10) 의 경우, 첫 번째 인자는 쪼개고 싶은 문자열 데이터,
두 번째 인자는 쪼개는 시작점, 세 번째 인자는 시작점 부터 몇 개 까지 자르 겠다.
즉, 문자열의 1번째 부분부터 10개 분량만큼 자르겠다.
다음으로, CASE 문법에 대해서 알아보자.
case 문법은, 경우에 따라서 원하는 값을 필드에 출력해주는 기능을 해.
예를 들어서, point_users 테이블에서 해당 유저의 포인트가 10,000만을 넘냐 안넘냐에 따라 다르게 표기하고 싶다면?
——
select pu.user_id, pu.point,
(case when pu.point > 10000 then '잘 하고 있어요!'
else '조금만 더 화이팅!' end) as msg
from point_users pu
——
=> 즉, 일종의 if문인거야. Case when, 즉 경우에 따라서 다르게 표기해라 이거지.
Case 문법을 잘 활용하면 통계를 낼 수도 있어.
——
with table1 as (
select pu.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 a.lv, count(*) from table1 a
group by a.lv
——
=> case when 은 if, else if 처럼 조건 중첩도 가능해.
그리고 이렇게 만든 case 문을 subquery 문으로서 활용하여 from 절에서 사용할 수도 있어.
추가로 with 절을 구성하여 전체 쿼리문을 더 깔끔하게 만들었지.
__________________________________________
SUBSTRING_INDEX, SUBSTRING, CASE 문법을 활용하여 퀴즈 풀어보기.
퀴즈 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
——
=> case when 구문이 하나의 필드를 나타내고 있어. 필드의 이름은 msg 이고,
pu.point의 값이 조건인 평균값보다 높냐 낮냐에 따라 필드값에 나타나는 문자열이 달라져.
퀴즈 2. 이메일 도메인별 유저의 수 세어보기
* SUBSTRING_INDEX와 Group by를 잘 사용하면 끝!
——
select SUBSTRING_INDEX(u.email, '@', -1) as domain, count(*) from users u
group by domain
Or
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(u.email, '@', -1) as domain from users u
) a
group by domain
——
=> SUBSTRING_INDEX 을 사용하여 이메일을 원하는대로 쪼개고, as domain 이라는 필드에 쪼개낸 값들을 담아.
그 후 group by로 그룹 지어주고, 각 도메인 별로 count(*) 하여 총 개수를 알려줘.
혹은, subquery 를 from 절에서 사용하여, 이메일의 도메인 부분만 잘라내서 데이터 테이블로 만든 다음 필드를 가져오고 그룹을 지어줄 수도.
퀴즈 3. '화이팅'이 포함된 오늘의 다짐만 출력해보기
* like 사용해보기.
——
select * from checkins c
where c.comment like '%화이팅%'
——
=> 기습 문제. ㅎㅎ
퀴즈 4. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
추가로, 수강등록정보 별 진도율도 출력해보기.
——
with done_db as (
select ed.enrolled_id, count(*) as done_cnt from enrolleds_detail ed
where ed.done = 1
group by ed.enrolled_id
), total_db as (
select ed2.enrolled_id, count(*) as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
)
select a.enrolled_id, a.done_cnt, b.total_cnt,
round((a.done_cnt / b.total_cnt), 2) as ratio
from done_db a
inner join total_db b
on a.enrolled_id = b.enrolled_id
참고용.
select ed.enrolled_id, count(*) as done_cnt, table2.total_cnt from enrolleds_detail ed
inner join (
select ed2.enrolled_id, count(*) as total_cnt from enrolleds_detail ed2
group by ed2.enrolled_id
) table2
on ed.enrolled_id = table2.enrolled_id
where ed.done = 1
group by ed.enrolled_id
——
=> 들은 강의 수 done 과 전체 강의 수 total. 두 테이블을 with 문으로 임시 테이블 등록하고,
inner join 으로 합쳐줘. Inner 이기 때문에 select from 이 어느 쪽이 되든 상관없어.
그리고 select 의 필드 중 하나로서 진도율 필드도 넣어주고.
사실 참고용 처럼 하나의 테이블을 베이스로 정하고 다른 하나를 join 의 () 안에 다 채워넣어주는 방법도 있긴 해.
허나 이와 같은 방법으로는 쿼리문이 산만해 지기도 하고, 진도율을 구할 때도 좀 골치 아파져.
뭐든 변수를 만들어서 그 안에 넣고 통제하는 게 가독성도 좋고 편리해.
하지만 반전으로… 사실 훨씬 더 나은 방법이 있었어..
——
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
——
=> 이런…