스파르타코딩클럽 SQL 4주차 강의 정리노트

3주차에는 'join'을 바탕으로 내가 출력하고자 하는 데이터를 여러 테이블이 합쳐진 새로운 테이블에서 추출해내는 법을 배웠다. 4주차에는 훨씬 더 복잡한 명령과 연산을 하기 위해 'SUBQUERY'를 배우게 되는데, 서브쿼리의 활용도가 굉장히 높기 때문에, '아, 이래서 SQL이 데이터를 출력하는 데 좋은 언어구나'라는 것을 실감하기도 했다.

4주차 수업은 조금 어려웠지만, 암튼 정리는 시작해보겠다.

Subquery 사용법 익히기

'Subquery'란, 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것을 의미한다.

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

라는 쿼리로 정보를 받았을 것이다. 그러나 같은 결과를 얻기 위해 서브쿼리를 이용한 다른 쿼리를 쓸 수도 있는데,

Select user from orders
Whee payment_method = 'kakopay'
라고 입력하면 결제방법이 'kakaopay'인 유저아이디들이 뜬다.

그러면 저 유저아이디들을 이용해서 users 차트에 있는 정보에 접근한다면, 첫 예제에 나온 결과 테이블과 똑같은 결과를 얻게 되는 것이다.

다음과 같이,
Select user_id, name, email from users
Where user_id in (
Select user from orders
Whee payment_method = 'kakopay'
)

라고 쓰면, 결과적으로는
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'

와 같은 결과를 얻게 되는 것이다.

쉽게 풀 수 있는 문제를 어렵게 풀고있는 느낌이 들지만, 선생님이 Subquery의 기능을 이해하기 위해 이런 예제를 활용해 설명하시는 것 같다.

**Subquery는 대체로,
1. Where 절과 함께,
2. Select 와 함께,
3. From 이후에

주로 들어간다.**

Subquery 본격 사용해보기 (예제)

1. Where와 함께 사용되는 서브쿼리 - 위의 첫 예제에서 활용한 바로 그 쿼리가 예시가 되겠다.

Select user_id, name, email from users
Where user_id in (
Select user from orders
Whee payment_method = 'kakopay'
)
(굳이 어렵게 쓴 그 쿼리문)

2. Select 와 함께 사용되는 서브쿼리 - 편리한 점은 Select에 서브쿼리가 들어가면, 늘 변동하는 데이터 값에 대처하여, 내가 원하는 연산이 수시로 적용된다는 점이다. 즉, 내가 원하는 자료를 뽑을 때마다, 각 테이블과 필드 안에서 변동하고 있는 데이터값이 연산에 자동으로 반영되어 결과 자료도 변경되어 출력된다는 점이 매우 유용하다.

예) '4b8qsl'이라는 유저는 자신이 받은 '좋아요' 평균값에 비해 더 높게 받거나, 낮게 좋아요를 받은 적이 있는가? 매일 받는 '좋아요' 수와, 그 사람이 받는 평균 좋아요 수를 놓고 비교해보자.
라는 미션을 수행해야 한다면,

우선 '4b8qsl'이라는 유저가 받은 평균 좋아요 수를 뽑아야 한다.

Select avg(likes) from checkins
Where user_id = '4b8qsl'
로 쉽게 구할 수 있다.

그런데 문제는 매번 '4b8qsl'이 받은 좋아요 수와 그가 받은 평균 좋아요 수를 비교해야 한다는 점이다.

다음과 같이 쓰면 되는데,

Select checkin_id,
user_id,
likes,
(
Select avg(likes) from checkins
Where user_id = '4b8qsl'
)
from checkins

라고 입력하게 된다면, 모든 유저들이 매번 받은 좋아요 수를 '4b8qsl'가 받은 좋아요 평균 수와 비교하게 된다.

이것을 활용해서, 조금만 명령어를 수정하면,

'각 유저별로 자신이 받은 평균 좋아요 수와 자신이 매번 받는 좋아요 수를 비교할 수' 있게 된다.

다음과 같이 써보자.

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 checkin_id, user_id, likes from checkins

옆에 붙이게 되며, 그러면 우리는 자연스럽게 사용자들이 매번 받은 좋아요 수와 그들이 받은 좋아요의 평균값을 비교할 수 있게 되는 것이다.

글로 쓰니까 복잡하지만, SQL 쿼리를 실제로 돌려보면 차트가 직관적으로 나오기 때문에 이해가 쉬울 것이다...

3. from 뒤에 나오는 서브쿼리 - 이런 경우는 내가 이미 만들어놓은 Select 조건과 다른 테이블을 Join 하고싶을 때 사용하면 되는데, 그 활용도가 매우 높아 실전에서 자주 쓰인다고 한다.

예시) 과연 포인트를 많이 쌓은 사람들이 checkin에서 like도 많이 받았을까?

유저별 좋아요 평균을 먼저 구한다.
Select user_id, round(avg(likes),1) as avg_likes from checkins
Group by user_id

이 때,
유저별로 포인트를 보려면, 다음과 같은 쿼리를 써야 한다.

Select pu.user_id, pu.point from point_users pu
group by user_id

그런데 내가 보고자 하는 정보는 유저별 좋아요 평균을 포인트 차트에 덧붙이는 것이므로, 유저별 좋아요 평균 차트를 유저별 포인트 차트에 Inner join을 해줘야 하는 것이다. 그럼 쿼리가 다음과 같이 되는데,

Select * pu.user_id, pu.point, a.avg_likes from point_users pu
Inner join (
Select user_id, round(avg(likes),1) as avg_likes from checkins
Group by user_id
) as a on pu.user_id = a.user_id

이렇게 쓰면 내가 원하는 여러 테이블에 흩어져있는 정보들을 합쳐서 내가 원하는 방식으로 편집할 수 있다.

쉽게 말해, 내가 select를 이용해 뽑아낸 데이터를, 다른 테이블과 join을 시킬 수 있도록 하는 것이 바로 from 뒤에 나오는 서브쿼리이다. From 뒤에 나오는 서브쿼리는 Join기능을 통해 그 활용도가 매우 높아진다.

서브쿼리 연습하기 (문제풀이)

Where와 Select 와 함께 사용되는 서브쿼리 문제

1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
   Select * from point_users pu
   Where point > (
   Select avg(point) from point_users
   )

-평균값을 구해서 넣는 것이 아니라 평균을 구하는 서브쿼리를 작성해서 넣는 이유는 데이터가 지속적으로 변동할 시, 평균은 계속 변하기 때문에 수시로 변하는 데이터에 대처하기 위해서이다.

2. 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터를 구하기

Select * from point_users
Where point > (
Select avg(pu.point) from point_users pu
Inner join users u on pu.user_id = u.user_id
Where u.name = '이**'
)

위와 같은 결과를 얻을 수 있는 똑같은 방법!

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

이렇게도 쓸 수 있다....

3. Checkins 테이블에 Course_id별 평균 Likes 수 필드를 우측에 붙이기.

Select c.checkin_id,
c.course_id,
c.user_id,
(
Selct course_id, round(avg(likes),1) from checkins
Where course_id = 'c.course_id'
) as course_avg
likes from checkins c

4. 3번의 결과값에 course 별 타이틀 붙여주기.

Select c.checkin_id,
c.course_id,
c2.title,
c.user_id,
(
Selct course_id, round(avg(likes),1) from checkins
Where course_id = 'c.course_id'
) as course_avg
likes from checkins c
Inner join courses c2 on c.course_id = c2.course_id

이렇게 쓰면 되더라... 조금 복잡하지만 차근차근 과정을 따라가면 이해할 수 있었다. 실전에서 여러 데이터를 가지고 연습을 해야 할 것 같다.

From과 함께 사용되는 서브쿼리 문제

Select a.course_id,
c.title,
a.cnt_checkins,
b.cnt_total,
(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
Inner join courses c on a.course_id = c.course_id

서브쿼리가 여러 번 등장하는 복잡한 쿼리문을 간단하게 정리할 수 있도록 도와주는 'With' 쿼리

'With'는 서브쿼리를 여러 번, 복잡하게 활용하여 완성된 결과물을 만들어냈을 때, 완성된 전체 쿼리문을 간결하게 정리해서 복기하기 편리하게 만들어주는 쿼리이다. 완성된 쿼리문에 쓰이는 서브쿼리들에 별칭을 붙여주어, '앞으로 보이는 쿼리문에서는 해당 서브쿼리를 별칭으로 부르겠다'라고 명시하는 서문의 기능을 하는 것이 'with' 인 셈이다.

예를 들면, 위에 완성되어 있는 복잡한 쿼리문을 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 a.course_id,
c.title,
a.cnt_checkins,
b.cnt_total,
(a.cnt_checkins/b.cnt_total)
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 문법

문자열 쪼개기 - 'Substring_index'쿼리.
'Substring_index'는 차트에 보이는 문자열을 부분적으로 쪼개어 보여주라는 명령어인데, 'Substring_index(필드명, '기준점', 1 혹은 -1)'이라고 적어 사용한다. 기준점 뒤에 1을 붙이는 경우는 기준점보다 앞에 있는것, -1을 붙이는 경우는 뒤에 있는 것을 보여달라는 뜻이다.

예를 들어,
Select substring_index(email,'@',1) from users
라고 쓰면 email필드에 있는 데이터들 중, @ 앞에 있는 정보, 즉 사용자가 설정한 자기 이메일 아이디만 얻을 수 있게 되고,
select substring_index(email, '@',-1) from users
라고 쓰면 email필드에 있는 데이터들 중, @ 뒤에 있는 정보, 즉 사용자들이 등록한 이메일 도메인만 얻을 수 있게 된다.

문자열 일부만 출력하기 - 'Substring' 쿼리
Substring은 어떤 필드에 있는 데이터를 원하는 만큼만 잘라서 보여주는 쿼리인데, 'substring(필드명,시작점,보고싶은 글자 수)'으로 적어 사용한다. 데이터 내용이 길 경우, 시작점과 종료지점을 글자수에 맞춰 적으면 되는데, 예를 들면 다음과 같다.

Select order_no, created_at, substring(created_at,1,8) from orders

라고 적는다면, created_at이 '2020-07-08 11:52:34'와 같이 사용자가 등록한 날짜와 시간이 상세하게 적힌 필드이므로, 1번째 문자인 2부터, 8글자만 보여주게 된다. 다시 말해, 시간을 제외한 날짜만 보여줄 수 있는 것이다.
따라서,

Select order_no, created_at from orders
라고 하면, 각 주문이 생성된 날짜와 시간까지 볼 수 있다면,

Select order_no, substring(created_at,1,10) as date from orders
라고 입력하면, 각 주문이 생성된 날짜만 출력할 수 있는 것이다.

실전에서 자주 사용하는 Case 문법

'Case'는 내가 Select를 통해 완성한 결과물에 내가 원하는 정보를 임의로 입력하고 싶을 때 사용하는 명령어이다.

예를 들어, 포인트가 10000보다 높은 유저들에게는 '잘하고 있어요!라는 메시지를, 그리고 나머지 유저들에게는 '조금만 더 파이팅!'이라는 메시지를 남기고 싶다면 다음과 같이 쓰면 된다.

Select pu.user_id, pu.point,
(case when pu.point > 10000 then '잘하고 있어요!'
else '조금만 더 파이팅!' end) as message
from point_users pu

여기서, Case문 안에 서브쿼리를 이용하면 통계를 낼 수도 있다.

예를 들어,

Select pu.user_id, pu.point,
(case when pu.point > 1000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu

를 활용하여,

Select a.lv, count(*) as cnt from (
Select pu.user_id, pu.point,
(case when pu.point > 1000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
) a
group by a.lv

라고 입력하면 각 포인트를 레벨로 나누어, 각 레벨 별로 사용자가 몇 명인지를 셀 수 있다.
위 결과를 with를 활용하여 간결하게 정리하면,

With table1 as (
Select pu.user_id, pu.point,
(case when pu.point > 1000 then '1만 이상'
when pu.point > 5000 then '5천 이상'
else '5천 미만' end) as lv
from point_users pu
)

Select a.lv, count(*) as cnt from table1 a
group by a.lv

로 적을 수 있다.

예제 풀이

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

Select pu.user_id, pu.point,
(Case when pu.point > (Select avg(point) from point_users) '잘하고 있어요'
else '열심히 합시다!' end) as message
from point_users pu

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

Select substring_index(email,'@',-1) as domain from users 를 먼저 구한 뒤, 이것을 서브쿼리로 활용,

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

이라고 하면 끝!

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

Select * from checkins
Where checkin like '%화이팅%'

이렇게 스파르타코딩클럽 SQL 수업을 마쳤다.

그동안 퇴근 후에 짬짬이 시간을 내 듣느라 재미가 있는 수업을 더디게 따라가야 하는 것이 늘 힘들었다. 그러나 쉽게 알려주신 덕에 여러 번 연습을 하면서 이제 어느정도 숙련된 SQL을 구사하게 된 것 같고, 짧게 쪼개진 수업들을 소화하면서 내가 아는 것과 모르는 것을 매번 체크할 수 있다는 점도 유용했다. 그리고 이번에 4주차 강의를 한꺼번에 다시 들으면서 복습도 했고...

실전에서 수많은 데이터에 적용하면서 실력을 더 쌓을 수 있을 것 같다.