[SQL] 스파르타코딩클럽 내일배움단 "엑셀보다 쉬운 SQL" 4주차 개발일지 (내일배움카드 SQL)

대망의 마지막 4주차!

1. 오늘 배울 것

 이전 3주차까지의 내용으로 웬만한 SQL 문법은 다 배웠다고 보면 되고, 이후에는 이것들을 조합하거나 트릭을 쓰거나 응용하는 식으로 실력을 키워 나가게 될 것이다.

• 이번 주에는 쿼리를 좀 더 복잡하게 쓸 수 있게 하는 subquery라는 테크닉을 배워 볼 것.
• 또 subquery를 더 간결하게 쓸 수 있게 하는 with구문을 배울 것.
• 현실에서의 데이터는 지저분하고 복잡하다. 그래서 현실에서의 지저분한 데이터를 가공할 수 있는, 문자열을 가지고 노는 법을 배울 것이고,
• case문이라 하는 조건문에 대해서도 배울 것.

 

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

일단 디비버를 켜고 테이블을 다 열어 보자.

 

예) kakaopay로 결제한 유저들의 정보를 보고 싶다

그럼 users와 orders를 join 해서 볼 수 있겠져. 그다음 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. 일단 kakaopay로 결제한 user_id를 모두 구해본다.

select user_id from orders
where payment_method = 'kakaopay'

 

2. 그다음, users 테이블에서 user_id가 1번에 포함되는 유저들만 골라보기 (이것이 서브쿼리)

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

 

subquery의 특징

• subquery를 where절, select절, from절에 모두 쓸 수 있다.
• subquery 쓸 때는 탭을 잘 써서 줄맞춤을 잘해 주는 것이 중요하다. 안 그러면 뭐가 서브인지 나중에 헷갈림.
• subquery를 잘 쓰면 엑셀로 처리하기 어려운 복잡한 작업들을 할 수 있다.

 

where에 들어가는 Subquery

위의 예시가 이 케이스. 먼저 뽑은 다음에, "where 필드명 in subquery" ([안에 이게 있는~] 필드를 뱉어내어라) 형태.

 

select에 들어가는 Subquery

"select 필드명, 필드명, (subquery) from" ([~가 ~한] 필드를 뱉어내어라) 형태.

 

예) checkins 테이블에서 user id별로 likes의 평균을 보고 싶다:

group by를 쓸 수도 있지만, 이렇게 해 보자.

 

일단 임의의 유저아이디 하나 가지고 평균 내 보기

select avg(likes) from checkins
where user_id = '4b8a1036'

 

그다음에 이걸 일단 밑에 내려놓고:

사람들의 likes와 평균 likes를 보고 싶다:

select checkin_id, user_id, likes from checkins

select avg(likes) from checkins
where user_id = '4b8a1036'

 

줄 바꿈을 해서 괄호를 끼워 넣고 밑에 치워놨던 걸 그대로 넣어 본다. 테이블에도 alias 처리해 주기.

select c.checkin_id,
           c.user_id,
           c.likes,
           (
          select avg(likes) from checkins
           where user_id = c.user_id
           ) as avg_likes_user
from checkins c

이렇게 되면 select문이 실행될 때마다 괄호 속 애가 실행되는 것. 첫 줄부터 각 유저 아이디의 평균 * 여러 번 실행.

 

from에 들어가는 Subquery

실무에서도 가장 많이 사용되는 유형.

 

예) 유저 별 좋아요 평균을 구하고 싶다:

일단 그동안 배운 대로 해 봄

select user_id, round(avg(likes)) as avg_likes from checkins c
group by user_id

 

알고 싶은 것: 포인트가 높은 유저들은 열심히 활동하는 유저들일 텐데 평균 like도 많을까?

select user_id, point from point_users pu

select user_id, round(avg(likes)) as avg_likes from checkins c
group by user_id

 

이걸 조인하면 바로 붙여서 볼 수 있을 텐데!

이때 subquery를 사용하면 이걸 마치 있는 테이블처럼 쓸 수 있는 것임.

select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
         select user_id, round(avg(likes)) as avg_likes from checkins c
         group by user_id
) a on pu.user_id = a.user_id

밑에 놨던 애들을 괄호 안에 넣고, 이 테이블의 이름을 a라고 부름. (닫는 괄호 뒤에 alias 추가)

 

 

3. Subquery 연습해 보기 (where, select, from, inner join) - 어려움

where절에 들어가는 subquery

퀴즈 1. 전체 유저의 포인트 평균보다 높은 유저들의 데이터 추출하기

select * from point_usrs pu
where point > ()

여기서 () 자리에 전체 평균을 구하는 subquery가 들어가면 됨.

우선 select * from point_users pu로 평균을 구하는 쿼리를 먼저 만들고, 그걸 괄호 안에 넣으면 될 것이다.

select * from point_users pu 
where point > (
select round(avg(point)) as avg_pt from point_users p
)

 

퀴즈 2. 이 씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터 추출

select * from point_users pu 
where point > (

)

select * from point_users

select * from users

여기서 출발! join, subquery 둘 다로 풀 수 있다.

 

1) 이렇게 풀어보자:

일단 이씨 성을 가진 유저들의 평균 포인트를 구해 봄

select avg(pu.point) from users u
inner join point_users pu on u.user_id = pu.user_id 
where u.name = '이**'

이걸 잘라서 괄호 안에 넣어 보자.

select * from point_users pu
where pu.point > (
            select avg(pu.point) from users u
            inner join point_users pu on u.user_id = pu.user_id 
            where name = '이**'
)

나는 2번째 줄에 inner join users u on u.user_id = pu.user_id를 넣었는데 이건 괄호 안에서 이미 이너조인을 한 번 했기 때문에 두 번 쓸 필요가 없었군.

 

2) 그리고 이렇게 풀 수도 있다:

일단 이씨 성을 가진 사람들의 포인트를 구해서 평균 처리를 한다.

select avg(point) from point_users pu
where user_id in (
            select user_id from users where name = '이**'
)

그다음 이걸 괄호 안에 끼워 넣음. 이건 서브쿼리 안에 서브쿼리가 들어간 케이스.

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 = '이**'
            )
)

 

장기적으로 볼 때, 한눈에 보기 좋게 쿼리를 짜는 것이 가장 중요하다. 지금처럼 서브쿼리가 많이 들어가는 경우에는 tab을 잘 활용하기.

 

select절에 들어가는 subquery

퀴즈 3. checkins 테이블에 course_id별 평균 likes 수 필드 우측에 붙여보기

여기서 출발:

SELECT checkin_id,
              course_id,
              user_id,
              likes,
              ()
from checkins c

select * from checkins c 
where course_id = '5f0ae408765dae0006002817'

임의의 코스아이디값에다가 c.course_id를 넣어주고, 앞에다가 다 c. 를 붙여준 후 괄호 안에 넣어준다.

SELECT c.checkin_id,
              c.course_id,
              c.user_id,
              c.likes,
              (
              select avg(likes) from checkins
              where course_id = c.course_id
              )as course_avg
from checkins c

 

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

퀴즈 3에다가 courses에 있는 과목명을 join 해서 최종적으로 완성하면 됨.

SELECT c.checkin_id, co.title, c.user_id, c.likes,
              (
              select avg(likes) from checkins
              where course_id = c.course_id
              ) as course_avg
from checkins c
inner join courses co on c.course_id = co.course_id

 

팁: as course_avg 밑 줄에 co.*라고 추가하면 "co에 모든 것이 잘 붙었는지 본다"는 뜻. 추가 실행하면 co의 모든 것을 보여줌.

팁 2: 쿼리문은 한 번에 정답 맞히는 게임이 아니다. 이렇게 해 보고 저렇게 해 보다가 맞으면 아 내가 또 하나를 해냈구나 하면 됨. 중간고사 기말 같은 게 아니라 조금씩 시행착오를 거치며 완성해 나가면 된다.

 

from절에 들어가는 subquery

퀴즈 5. course_id별 유저의 체크인 개수를 구해보기

checkins 테이블을 course_id로 group by 해서 본다.

근데 체크인 개수만 봐서 비교를 하면 안 된다. 등록한 인원 자체가 다를 수 있기 때문. (distinct로 유저를 세야 함)

그래서 체크인뿐 아니라 등록한 사람들 수와, 비율을 같이 봐야 제대로 된 비교가 된다.

SELECT checkin_id,
              course_id,
              user_id,
              likes,
              ()
from checkins c

select * from checkins c 
where course_id = '5f0ae408765dae0006002817'

1) 먼저 course_id별로 체크인한 수를 구한다. 중복 없이 user id를 세기 위해 distinct 사용. (cnt_total)

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

2) orders 테이블에서 course_id의 수를 센다. (cnt_total)

select course_id, count(user_id) as cnt_total from orders
group by course_id 

3) 1과 2를 join 한다.

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(user_id) as cnt_total from orders
group by course_id 
) b on a.course_id = b.course_id

4) 비율을 구한다.

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(user_id) as cnt_total from orders
group by course_id 
) b on a.course_id = b.course_id

5) 강의 제목을 붙인다. courses 테이블과 조인.

(탭 처리가 귀찮아서 이미지로 붙임)

참고: from 앞에 c.* 붙여주면 c테이블과 조인 잘 되었는지 확인할 수 있음

 

엉엉 조혼나 복잡하네

 

 

 

 

 

4. with절 연습하기

from절의 subquery를 보기 좋게 간단하게 볼 수 있게 해 주는 것이 with절.

위 3번-퀴즈 5에서 작업하던 코드를 그대로 가져와서 시작!

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(user_id) 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을 두 번 했는데, 저 괄호 때문에 지금 복잡하게 보인다.

alias를 써서 테이블을 간단한 알파벳으로 치환했듯, () 속의 테이블을 간단히 치환시켜버릴 수 있다.

복잡한 괄호를 맨 위로 꺼내 이렇게 써버리고 원래의 괄호 부분을 table1, table2로 치환시켜 쓰면:

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

쨘 이렇게 보기 좋은 쿼리를 완성시킬 수 있다.

 

 

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

1) 문자열을 가지고 노는 법

실제 업무에서는 생각보다 데이터가 지저분해서, 문자열 데이터를 원하는 형태로 정리해야 하는 경우가 많다.

SUBSTRING_INDEX라는 문법을 배워볼 것.

 

users 테이블에서 @을 기준으로 이메일을 쪼개는 법

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

"email 필드를, @을 시작포인트로, 첫 번째까지 쪼갠다."

이러면 아이디만 출력된다. 1 자리에 -1을 넣으면 반대로 맨 끝에 있는 도메인만 얻을 수 있다.

 

orders 테이블에서 created_at을 날짜까지만 출력하는 법

select order_no, created_at, substring(created_at,1,10) as date from orders

이번에는 substring만 쓴다. "created_at 필드를, 1째 자리를 시작포인트로, 10자리까지 쪼갠다."

그러면 이렇게 10의 자리까지 나옴. (몇 자리까지 가져오고 싶은지는 대충 한번 넣어 보면 됨. 해보면서 맞추기.)

여기에 group by date, count를 하면 주문일자 별로 주문건수까지 볼 수 있음!

 

2) 조건문(Case문)

경우에 따라 원하는 값을 필드에 출력해 주는 문법임.

예를 들어 point_users 테이블에서 포인트를 구간 별로 표시해 주고 싶다:

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

 

(파이썬에서는 if인데 SQL에서는 case,

그리고 파이썬에서는 print인데 SQL에서는 따로 print는 안 쓰고 걍 슬쩍 하고싶은 말을 얹는군)

 

점수 구간 별로 나눠서 볼 수도 있다.

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

여기서 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

 

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

 

기야아아아악 이게다뭐여

 

6. SQL 문법 복습. 또 복습! 

퀴즈를 초급부터 풀어 봅시다,,

퀴즈 1. CASE문을 사용해서 메시지를 포인트 구간별로 보여주기.
(평균 이상이면 '잘하고 있어요!', 낮으면 '열심히 합시다!')

select point_user_id,
           point,
           (case when point >= (select avg(point) from point_users) then '잘 하고 있어요!'
                    else '열심히 합시다!' end) as msg
from point_users pu

 

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

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

난 이렇게 했는데, subquery를 활용하면 아래처럼 만들 수도 있다. (어렵다;)

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

 

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

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

훼이크였다,, substring을 굳이 써도 되지 않는 경우임 물론 난 넘어가지 않았지 촤하핫

 

 

이제부터는 조오금 더 어려운 중급 퀴즈들.

퀴즈 4. 수강등록정보(enrolled_id) 별 전체 강의 수와 들은 강의의 수, 진도율(Ratio) 출력해 보기

사용할 테이블은 enrolleds_detail, 들은 강의는 done=1인 데이터들.

아까 위에서처럼 두 개를 만들어서 서브쿼리로 join 하기. 그리고 with로 정리하기.

with table1 as
(
        select enrolled_id, count(*) as total_cnt from enrolleds_detail ed
        group by enrolled_id
), table2 as
(
        select enrolled_id, count(*) as done_cnt from enrolleds_detail ed
        where done = 1
        group by enrolled_id
)

select a.enrolled_id, a.total_cnt, b.done_cnt, round(b.done_cnt/a.total_cnt,2) as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

혼자 쉬익쉬익 짜증 내면서 헤매면서 했는데 강사님과 똑같이 풀었다. 뿌듯!

• 처음에는 select(*)로 전부 다 늘어놓고 나중에 group by 해야 한다고 생각했는데 아니었음. 각 테이블에서 group by 먼저 하고 둘을 inner join으로 병합해야 하는 것이었다. 
• 그리고 한 줄 띄면 둘 중 한 문단만 실행되기 때문에 블록으로 전체 선택 후 실행해야 한다.

그런데..!

결국 숫자를 세는 것이니 이렇게도 풀 수가 있었다.

즉 done이 1 아니면 0이기 때문에 count 대신 sum(done)을 해도 됨!

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

 

아놔 머임 두줄로 풀리는 거였네 쏘 간단

 

교훈: 가끔 이렇게 멀리서 보면 더 나은 쿼리를 만들 수 있다!

지금은 연습을 많이 하고, 나중에 숙달되면 이런 부분도 고민하면서 해 보기 💡

 

 

7. 끝 & 숙제 설명

마지막 숙제는 문제를 푸는 게 아니라 지금까지 배운 문법을 정리해 보는 것이었다.

(안 그래도 따로 한번 정리해야지 하고 있었는데 세심한 스파르타코딩클럽! 다음 글에서 이어집니다.)

 

이제 SQL을 배웠으니, 그다음 단계는 (가능하다면) 회사의 개발자들에게 데이터베이스 권한을 달라고 당당하게 요청하는 것!

디비버와 연결하는 과정은 개발자들의 도움이 필요할 수 있는 점도 참고하기.

 

 

8. SQL 4강 완주 소회

끝! 드디어 완주했다!

 

 우선 강의와 관련해 이야기하고 싶은 부분은, 앞부분까지는 쉬웠는데 유독 4강이 갑자기 난이도가 헬이었다는 점이다. 3강까지는 평이하게 가다가 4강에 와다다다 어려운 것을 몰아넣어둔 기분..? (강의 시간도 제일 길다) 내 경우 서브쿼리가 어려웠다. 마치 영어를 처음 배울 때 that절을 처음 배운 느낌이랄까..? 그래서 0~3강은 하루 안에 후루룹찹찹 쌉가능이었는데 4강은 하루 안에 깔끔히 끝내지 못하고 좀 진행이 지지부진했던 것 같다. 그래도 퀴즈가 많아서, 짜증이 나도 참으면서 뿌시려고 노력을 하니 마지막 퀴즈는 혼자서도 잘 풀 수 있었다.

 

 스파르타코딩클럽의 강의에 대해 전체적으로 느낀 점은, 강의를 쉽고 세심하게 잘 만들었다는 것이다. 처음 해보는 사람 입장에서 무섭고 어려워서 중도에 도망가지 않고, 직관적이고 쉽게 이해할 수 있도록 쉽게 풀어서 설명하는 데 공을 들였음을 느낄 수 있었다. 그 덕에 큰 무리 없이 익힐 수 있어 고마운 강의였다. 강사님(=대표님)도 뭔가 인자하게(?) 우리가 안 도망갈 수 있도록 어르고 달래는(?) 것을 느낄 수 있었다. 덕분에 포기하지 않았습니다 감사합니다∙∙🙇🏻

 

 아무튼 잘했다 내 자신! 이제 남은 기간 동안 파이썬도 마스터해 보자. 🥳