테이블에 원하는 데이터가 없어서 여러 테이블을 참조해야 하는 경우가 발생했어요.

👉 오늘의 팁

• Inner Join을 사용하여 테이블을 연결하고, 원하는 데이터를 뽑아보세요.
• 조건으로 다른 테이블을 참조하는 경우라면 where절에서 Subquery를 사용해보세요.
• where 필드명 in (subquery)* 이런 방식으로요!

01. Inner Join

예제 1) 네이버 이메일을 사용하는 유저 중, 성씨별 주문 건수를 세어보자.

• 힌트 : orders 테이블에는 이메일과 이름이 없어 users 테이블을 inner join하고, 네이버메일 유저를 찾아 성씨별로 주문 수를 카운트하면 돼요. OO별은 group by인거 아시죠?

 select u.name, count(u.name) as order_cnt from orders o 
 inner join users u on o.user_id = u.user_id 
 where u.email like '%naver.com'
 group by u.name 

👉 위 쿼리가 실행되는 순서: from → join → where → group by → select

1. from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙입니다.
2. inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙입니다. (*users 테이블에 u라는 별칭을 붙입니다)
3. where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져옵니다.
4. group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐줍니다.
5. select u.name, count(u.name) as count_name : users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

• Join의 실행 순서는 항상 from 과 붙어다닌다고 생각하면 편해요!

02. where 필드명 in (subquery)

예제 2) 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해 보자.

• 힌트 : order 테이블에서 카카오페이로 결제한 유저아이디를 찾아, 유저테이블에서 확인하면 돼요. order 테이블 정보를 출력할 필요는 없어요!
• Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용해 보세요.
• where 필드명 in (subquery)* 이런 방식으로요!

select * from users
 where user_id in (
        select user_id from orders
        where payment_method = 'kakaopay'
  )

쿼리가 실행되는 순서를 이렇게 상상하면 편해요! (1) from 실행: users 데이터를 가져와줌 (2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌 (3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌 (4) 조건에 맞는 결과 출력

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

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

1) Subquery 사용방법 익혀보기

👉 Subquery란, 하나의 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'

방법 2. Subquery로!

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

02. 자주 쓰이는 Subquery 유형

: Subquery는 where, select, from 절에서 유용하게 사용될 수 있어요!

1) Where 에 들어가는 Subquery

👉 Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다.

• where 필드명 in (subquery) 이런 방식으로요!

▶︎ 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때는 아래와 같이 표현할 수 있겠죠.

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) 조건에 맞는 결과 출력

2) Select 에 들어가는 Subquery

👉 Select는 결과를 출력해주는 부분이죠? 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용합니다.

• select 필드명, 필드명, (subquery) from .. 이렇게요!

▶︎ '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금할 수 있겠죠?

1. 그럼, 평균을 먼저 구해봅시다! (user_id='4b8a10e6' 를 예시로!)

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) 함께 출력해준다!

3) From 에 들어가는 Subquery (가장 많이 사용되는 유형!)

👉 From은 언제 사용하면 좋을까요? 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱이겠죠!

• select * from (subquery) 별칭

• select * from 테이블t inner join (subquery) 별칭a on t.key=a.key

• A : 우선 유저 별 좋아요 평균을 먼저 구해볼까요? → checkins 테이블을 user_id로 group by 하면 되겠죠?

select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

• 이제 A에서 해당 유저 별 포인트를 보고 싶다면? → 그러면, 포인트와 like의 상관정도를 알 수 있겠죠?

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

- 쿼리가 실행되는 순서를 이렇게 상상하면 편해요! (1) 먼저 서브쿼리의 select가 실행되고, (2) 이것을 테이블처럼 여기고 밖의 select가 실행!

03. Subquery 연습

1) Where 절에 들어가는 Subquery

👉 [오늘의 팁!]

• 같은 테이블을 Subquery로 사용할 수도 있어요.
• 필요한 경우, Subquery 안에서 여러 테이블을 Join 할수도 있어요.

▶︎ 포인트가 평균보다 많은 사람들의 데이터를 추출해보자! (평균 포인트는 5380점)

• 힌트! → point_users 테이블을 이용해서 avg를 구하고, 다시 point_users와 조인하세요!

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 pu.point > 
     (select avg(pu2.point) from point_users pu2
      inner join users u on pu2.user_id = u.user_id
      where u.name = '이**')

2) Select 절에 들어가는 Subquery

▶︎ checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select c1.checkin_id, c1.course_id, c1.user_id, c1.likes,
        (select round(avg(c2.likes),1) from checkins c2 
        where c1.course_id = c2.course_id) as avg_like 
  from checkins c1

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

• 직전에 실습했던 것에, courses 테이블을 join 하면 되겠죠?

select c1.checkin_id, c3.title, c1.user_id, c1.likes,
        (select round(avg(c2.likes),1) from checkins c2 
        where c1.course_id = c2.course_id) as avg_like 
  from checkins c1
 inner join courses c3 on c1.course_id = c3.course_id 

3) From 절에 들어가는 Subquery

▶︎ course_id별 like 개수에 전체 인원을 붙이기

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

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

[준비 2] course_id별 인원을 구해보기!

select course_id, count(*) as total_cnt from orders
group by course_id

[최종] 준비1과 준비2를 inner join 하면 됩니다!

select a.course_id, a.checkin_cnt, b.total_cnt 
  from (
    select course_id, count(distinct(user_id)) as checkin_cnt from checkins
    group by course_id 
) a 
inner join (
    select course_id, count(*) as total_cnt from orders
    group by course_id 
) b on a.course_id = b.course_id 

[한걸음 더] 전체 중 얼마나 like를 하는지 알아보면 좋겠죠? 퍼센트 나타내고, 강의 제목도 표기해 주세요.

select c.title, a.checkin_cnt, b.total_cnt,
       (a.checkin_cnt/b.total_cnt) as ratio
  from (
    select course_id, count(distinct(user_id)) as checkin_cnt from checkins
    group by course_id 
) a 
inner join (
    select course_id, count(*) as total_cnt 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 

04. with절 연습

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

다시 위의 코드를 볼까요?

select c.title, 
       a.checkin_cnt, 
       b.total_cnt,
       (a.checkin_cnt/b.total_cnt) as ratio
  from (
    select course_id, count(distinct(user_id)) as checkin_cnt from checkins
    group by course_id 
) a 
inner join (
    select course_id, count(*) as total_cnt 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절은 쿼리 젤 위에 작성하고, 실행할 때는 블록으로 감싸야 오류가 안나요!

with table1 as (
    select course_id, count(distinct(user_id)) as checkin_cnt from checkins
    group by course_id 
), table2 as (
    select course_id, count(*) as total_cnt from orders
    group by course_id
)

select c.title, 
       a.checkin_cnt, 
       b.total_cnt,
       (a.checkin_cnt/b.total_cnt) 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 

05. 실전에서 유용한 SQL 문법 (문자열)

1) 문자열 쪼개보기 : SUBSTRING_INDEX

👉 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!

*SUBSTRING_INDEX *라는 문법을 사용하면 됩니다.

• SUBSTRING_INDEX(email, '@', 1) @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
• SUBSTRING_INDEX(email, '@', -1) @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

▶︎ 이메일에서 아이디만 가져와보기

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

▶︎ 이메일에서 이메일 도메인만 가져와보기

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

2) 문자열 일부만 출력하기 : SUBSTRING

👉 orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!

SUBSTRING 이라는 문법을 사용하면 됩니다. - substring(created_at,1,10) SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

▶︎ orders 테이블에서 날짜까지 출력하게 해보기

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

06. 실전에서 유용한 SQL 문법 (Case)

1) CASE : 경우에 따라 원하는 값을 새 필드에 출력해보기

👉 특정 조건에 따라, 데이터를 구분해서 정리해주고 싶을 때가 있겠죠? 이런 경우에 CASE 라는 문법이 사용됩니다.

[예시] 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?

▶︎ 포인트 보유액에 따라 다르게 표시해주기

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

2) CASE: 실전을 위한 트릭!

👉 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 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

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

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

👉 이번 건 쉽죠? 잊어버리셨을까봐! like를 어떻게 썼더라~

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

08. SQL 문법 복습. 또 복습! (중급)

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

👉 [힌트!] subquery 두 개를 만들어놓고, inner join!

살펴볼 테이블: enrolled_details

done_cnt는 들은 강의의 수(done=1), total_cnt는 전체 강의의 수

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

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

1. 여러 테이블의 정보를 연결하는 Join을 이해
2. 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습
3. 아래 위로 결과를 연결하는 Union을 공부

01. Join

: 여러 테이블을 연결해 한 눈에 보고 싶다면?

1) Join 이란?

👉 Join이란? 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요.

▶︎ user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!

위의 예시와 같이, 두 테이블의 정보를 연결해서 함께 보고싶을 때가 있겠죠?

그럴 때를 대비해서 무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는답니다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 불러요.

select * from point_users
left join users
on point_users.user_id = users.user_id

👉 [오늘의 꿀팁!] 혹시 엑셀을 잘 쓰신다면? SQL의 Join은 엑셀의 vlookup과 동일하다고 생각하시면 됩니다 :-)

2) Join의 종류: Left Join, Inner Join

▶︎ Left Join 이해하기 여기서 A와 B는 각각의 테이블을 의미합니다. 둘 사이의 겹치는 부분은, 뭔가 테이블 A와 B의 key 값이 연결되는 부분일 것 같지 않나요?

select * from users u
left join point_users p
on u.user_id = p.user_id;

👉 어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있습니다. (NULL)

• 꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
• 비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

▶︎ Inner Join 이해하기

여기서 A와 B는 각각의 테이블을 의미합니다. 이 그림은 뭔가, 두 테이블의 교집합을 이야기하고 있는 것 같지 않나요?

select * from users u
 inner join point_users p on u.user_id = p.user_id;

👉 앗, 여기서는 비어있는 필드가 있는 데이터가 없어요!

그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문이에요.

02. Join 본격 사용해보기

1) Join 함께 연습해보기

▶︎ orders 테이블에 users 테이블 연결해보기

• 주문 정보에 유저 정보를 연결해 분석을 위한 준비를 하려고 합니다! 우선 inner Join을 사용해서 주문 정보에, 유저 정보를 붙여서 볼까요?

select * from orders o
 inner join users u on o.user_id = u.user_id;

▶︎ checkins 테이블에 users 테이블 연결해보기

• '오늘의 다짐' 테이블에 유저 정보를 연결해 분석을 위한 준비를 하려고 합니다! 우선 Inner Join을 사용해서 '오늘의 다짐' 테이블에, 유저 테이블을 붙여서 볼까요?

select * from checkins c
inner join users u
on c.user_id = u.user_id;

👉 [오늘의 팁!] 연결의 기준이 되고싶은 테이블을 from 절에, 기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치해 놓습니다.

▶︎ enrolleds 테이블에 courses 테이블 연결해보기

• '수강 등록' 테이블에 과목 정보를 연결해 분석을 위한 준비를 하려고 합니다! 우선 Inner Join을 사용해서 '수강 등록' 테이블에, 과목 테이블을 붙여서 볼까요?

select * from enrolleds e
 inner join courses c on e.course_id = c.course_id;

2) Join : SQL 쿼리가 실행되는 순서

👉 위 쿼리가 실행되는 순서: from → join → select

1. from enrolleds: enrolleds 테이블 데이터 전체를 가져옵니다.
2. inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙입니다.
3. select * : 붙여진 모든 데이터를 출력합니다.

• 항상 from에 들어간 테이블을 기준으로, 다른 테이블이 붙는다고 생각하면 편합니다!

03. 배웠던 문법을 Join과 함께 사용해보기

1) 배웠던 문법 Join과 함께 연습해보기

▶︎ checkins 테이블에 courses 테이블 연결해서 통계치 내보기

• '오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자!

select co.title, count(co.title) as checkin_count from checkins ci
 inner join courses co on ci.course_id = co.course_id 
 group by co.title

👉 [오늘의 팁!] 2주차에 배운 alias는 이렇게 사용하면 편합니다. 연결되는 테이블이 많아지면서 필드명과 테이블명이 헷갈려 실수할 수 있는데, 이렇게 alias를 지정해 주면 편하고 깔끔하게 SQL 쿼리를 작성할 수 있어요.

▶︎ point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

• 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!

select * from point_users p
 inner join users u on p.user_id = u.user_id
 order by p.point desc

▶︎ orders 테이블에 users 테이블 연결해서 통계치 내보기

• 주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!

select u.name, count(u.name) as count_name from orders o
 inner join users u on o.user_id = u.user_id 
 where u.email like '%naver.com'
 group by u.name

2) SQL 쿼리가 실행되는 순서

👉 위 쿼리가 실행되는 순서: from → join → where → group by → select

1. from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙입니다.
2. inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙입니다. (*users 테이블에 u라는 별칭을 붙입니다)
3. where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져옵니다.
4. group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐줍니다.
5. select u.name, count(u.name) as count_name : users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

• Join의 실행 순서는 항상 from 과 붙어다닌다고 생각하면 편해요!

04. 이제는 실전! 본격 쿼리 작성

퀴즈 1) 결제 수단 별 유저 포인트의 평균값 구해보기

• join 할 테이블: point_users 에, orders 를 붙이기
• 꿀팁! → round(숫자,자릿수) 를 이용해서 반올림을 해볼까요?

select o.payment_method, round(AVG(p.point)) from point_users p
 inner join orders o on p.user_id = o.user_id 
 group by o.payment_method

퀴즈 2) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

• join 할 테이블: enrolleds 에, users 를 붙이기
• 꿀팁! → is_registered = 0 인 사람들을 세어보아요!
• 꿀팁! → order by 를 이용해서 내림차순으로 정렬하면 보기 좋겠죠?

select name, count(*) as cnt_name from enrolleds e
 inner join users u on e.user_id = u.user_id 
 where is_registered = 0
 group by name
 order by cnt_name desc

퀴즈 3) 과목 별로 시작하지 않은 유저들을 세어보기

• join 할 테이블: courses에, enrolleds 를 붙이기
• 꿀팁! → is_registered = 0 인 사람들을 세어보아요!

select c.course_id, c.title, count(*) as cnt_notstart from courses c
inner join enrolleds e 
on c.course_id = e.course_id
where is_registered = 0
group by c.course_id

05. 한번 더 총복습!

연습 1) 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!

• join 할 테이블: checkins에, courses 를 붙이기
• 꿀팁! → group by, order by에 콤마로 이어서 두 개 필드를 걸어보세요!

select c2.title, c1.week, count(*) from checkins c1
 inner join courses c2 on c1.course_id = c2.course_id 
 group by c2.title, c1.week 
 order by c2.title, c1.week 

연습 2) 연습1번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!

• join 할 테이블: checkins에, courses 를 붙이고! + checkins 에, orders 를 한번 더 붙이기!
• 꿀팁! → checkins 테이블에 inner join을 한번 더 걸고, where 절로 마무리!

select c2.title, c1.week, count(*) as cnt from checkins c1
 inner join courses c2 on c1.course_id = c2.course_id
 inner join orders o on c1.user_id = o.user_id
 where o.created_at >= '2020-08-01'
 group by c2.title, c1.week 
 order by c2.title, c1.week 

06. Left Join 복습

1) 다시한번 복습! inner join vs left join

• inner join 은 교집합, left join 은 첫번째 원에 붙이는 것!

• left join은 어디에 → 뭐를 붙일건지, 순서가 중요해요!

2) Left join 언제쓸까요?

예를 들면 모든 유저가 포인트를 갖고 있지를 않을 수 있잖아요! users 테이블과 ↔ point_users 테이블을 left join 해봅시다.

select * from users u
  left join point_users pu on u.user_id = pu.user_id

이 상태에선, 이런 게 가능해요. 💡 유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계!

• 속닥속닥) is NULL , is not NULL 을 함께 배워보아요!

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name

select name, count(*) from users u
  left join point_users pu on u.user_id = pu.user_id
 where pu.point_user_id is not NULL
 group by name

3) 여기서 퀴즈! - 막해보기

▶︎ 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!

• 힌트1 → count 은 NULL을 세지 않는답니다!
• 힌트2 → Alias(별칭)도 잘 붙여주세요!
• 힌트3 → 비율은 소수점 둘째자리에서 반올림!

select count(point_user_id) as pnt_user_cnt,
       count(*) as tot_user_cnt,
       round(count(point_user_id)/count(*),2) as ratio
  from users u
  left join point_users pu on u.user_id = pu.user_id
 where u.created_at between '2020-07-10' and '2020-07-20'

07. 결과물 합치기! Union 배우기

1) Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우, 있을걸요!

👉 근데, 그러려면 한 가지 조건이 있어요! 노란색과 파란색 박스의 필드명이 같아야 한답니다. 🙂 (당연하겠죠?)

2) Union을 이용해서 아래와 같은 모습을 만들어볼까요?

• 8월

select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
 inner join courses c on c2.course_id = c.course_id
 inner join orders o on o.user_id = c2.user_id
 where o.created_at > '2020-08-01'
 group by c2.course_id, c2.week
 order by c2.course_id, c2.week

• 7월

select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
 inner join courses c on c2.course_id = c.course_id
 inner join orders o on o.user_id = c2.user_id
 where o.created_at < '2020-08-01'
 group by c2.course_id, c2.week
 order by c2.course_id, c2.week

• 여기에 아래 위로 Union all을 사용해 붙여주면 끝!

(
    select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
     inner join courses c on c2.course_id = c.course_id
     inner join orders o on o.user_id = c2.user_id
     where o.created_at < '2020-08-01'
     group by c2.course_id, c2.week
     order by c2.course_id, c2.week
)
union all
(
    select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
     inner join courses c on c2.course_id = c.course_id
     inner join orders o on o.user_id = c2.user_id
     where o.created_at > '2020-08-01'
      group by c2.course_id, c2.week
     order by c2.course_id, c2.week
)

• 앗, 그런데, 한 가지! 정렬이 깨졌네요!? 😂 👉 네 맞습니다! union을 사용하면 내부 정렬이 먹지 않아요. 이 때 유용한 방법이 있지요. 바로, SubQuery(서브쿼리)! 4주차에 공부한다고 하니 기대해주세요!

08. 3주차 숙제

enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

• 조인해야 하는 테이블: enrolleds, enrolleds_detail
• 조인하는 필드: enrolled_id

select e.enrolled_id,
         e.user_id,
         count(*) as cnt
  from enrolleds e
 inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
 where ed.done = 1
 group by e.enrolled_id, e.user_id
 order by cnt desc

3주차 수업 완강했어요! 👏

💡공부를 마치며...

1) 연결의 기준이 되고싶은 테이블을 from 절에, 기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치해 놓습니다. ㄴ 기준 정하는게 좀 헷갈린다...더 많은 예제를 통해 확인해 봐야겠다.

1. 동일한 범주의 데이터를 묶어서 통계를 내주는 Group by를 이해
2. 출력하는 데이터를 필드의 값으로 정렬하여 출력하는 Order by를 익히기
3. 조금 더 복잡한 분석을 위해 자주 사용되는 유용한 문법을 익히기

01. Group by

1) Group by란?

동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것을 의미해요. Group by를 이용하면 1) 같은 성씨의 데이터를 하나로 묶고 2) 각 성씨의 회원수를 구할 수 있어요.

select name, count(*) from users
group by name;

성씨별로 회원이 몇 명인지, 세어진 것을 확인할 수 있죠?

1. from users: users 테이블에서 데이터를 불러옵니다
2. group by name: name이라는 필드에서 동일한 값을 갖는 데이터를 하나로 합쳐줍니다
3. select name, count(): 이름과 count()를 출력해 주는데, 여기서 count(*)는 group by로 합쳐진 데이터의 개수를 세어주는 것입니다!

2) Group by : SQL 쿼리가 실행되는 순서

👉 위 쿼리가 실행되는 순서: from → group by → select

1. from users: users 테이블 데이터 전체를 가져옵니다.
2. group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐줍니다.
3. select name, count(): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어줍니다. 예) 이*, 이, 김, 김, 박 이렇게 데이터가 있었다면, 이는 2개, 김은 2개, 박**은 1개겠죠!

02. Group by 기능 알아보기

1) 동일한 범주의 개수 구하기

👉 동일한 범주의 갯수는 count(*)를 사용해서 해요.

▶︎ 주차별 '오늘의 다짐' 개수 구하기

select week, count(*) from checkins
group by week;

select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명;

2) 동일한 범주에서의 최솟값 구하기

👉 동일한 범주 특정 필드의 최솟값은 min(필드명)을 사용해서 해요.

▶︎ 주차별 '오늘의 다짐'의 좋아요 최솟값 구하기

select week, min(likes) from checkins
group by week;

select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

3) 동일한 범주에서의 최댓값 구하기

👉 동일한 범주 특정 필드의 최댓값은 max(필드명)을 사용해서 해요.

▶︎ 주차별 '오늘의 다짐'의 좋아요 최댓값 구하기

select week, max(likes) from checkins
group by week;

select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

4) 동일한 범주의 평균 구하기

👉 동일한 범주 특정 필드의 평균값은 avg(필드명)을 사용해서 해요.

▶︎ 주차별 '오늘의 다짐'의 좋아요 평균값 구하기

select week, avg(likes) from checkins
group by week;

select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

select week, round(avg(likes),2) from checkins
group by week;

평균을 구할 때는 소수점이 나와서 보기 좋지가 않아, round(필드명,소수점자리수)를 함께써서 원하는 숫자로 표기해요. 0은 그냥 반올림. 2는 소수점 둘째자리까지 표기.

5) 동일한 범주의 합계 구하기

👉 동일한 범주 특정 필드의 합계는 sum(필드명)을 사용해서 해요.

▶︎ 주차별 '오늘의 다짐'의 좋아요 합계 구하기

select week, sum(likes) from checkins
group by week;

select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

03. Order by

: 깔끔한 정렬이 필요할 땐?

1) 오름차순으로 정렬해보기

👉 기존 작성된 코드에 *order by count() *만 추가해줬어요. 갯수 (count() 값)을 기준으로 정렬해달라는 뜻이에요.

• 기본 오름차순으로 asc(어센딩)정렬되며, 내림차순은 desc(디센딩) 추가

▶︎ 성씨별 회원수 데이터를 오름차순으로 정렬해보기

select name, count(*) from users
group by name
order by count(*);

2) 내림차순으로 정렬해보기

👉 order by count()에 *desc**만 붙여줬어요.

▶︎ 성씨별 회원수 데이터를 내림차순으로 정렬해보기

select name, count(*) from users
group by name
order by count(*) desc;

3) Order by 사용해보기

▶︎ like를 많이 받은 순서대로 '오늘의 다짐'을 출력해 볼까요?

select * from checkins
order by likes desc;

select * from 테이블명
order by 정렬의 기준이 될 필드명;

4) Order by : SQL 쿼리가 실행되는 순서

select name, count(*) from users
group by name
order by count(*);

👉 위 쿼리가 실행되는 순서: from → group by → select → order by

1. from users: users 테이블 데이터 전체를 가져옵니다.
2. group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐줍니다.
3. select name, count(): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어줍니다. 예) 이*, 이, 김, 김, 박 이렇게 데이터가 있었다면, 이는 2개, 김은 2개, 박**은 1개겠죠!
4. order by count(*): 합쳐진 데이터의 개수에 따라 오름차순으로 정렬해줍니다.

04. Where와 함께 사용해보기

1) Where와 Group by, Order by 함께 사용해보기

▶︎ 웹개발 종합반의 결제수단별 주문건수 세어보기

😎 원리는 간단해요. Where절로 조건이 하나 추가되고, 그 이후에 Group by, Order by가 실행되는 것!

[순서]

1. orders 테이블에서 주문 데이터를 읽어오고
2. 웹개발 종합반 데이터만 남기고
3. 결제수단(범주) 별로 그룹화하고
4. 결제수단별 주문건수를 세어준다!

select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method;

위와 같이, group by와 select 사이에 where로 조건을 넣어주면 끝!

2) SQL 쿼리가 실행되는 순서

👉 위 쿼리가 실행되는 순서: from → where → group by → select

1. from orders: users 테이블 데이터 전체를 가져옵니다.
2. where course_title = "웹개발 종합반": 웹개발 종합반 데이터만 남겨줍니다.
3. group by payment_method: 같은 payment_method을 갖는 데이터를 합쳐줍니다.
4. select payment_method, count(*): payment_method에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어줍니다. 예) CARD, CARD, kakaopay 이렇게 데이터가 있었다면, CARD는 2개, kakaopay는 1개겠죠!

만약 order by가 추가된다면? order by는 맨 나중에 실행됩니다! (결과물을 정렬해주는 것이기 때문!)

05. 이외 유용한 문법 배워보기

1) 별칭 기능: Alias

쿼리가 점점 길어지면서 종종 헷갈리는 일이 생길 수 있습니다. 그래서 SQL은 Alias(알리아스)라는 별칭 기능을 지원합니다.

select * from orders o
where o.course_title = '앱개발 종합반'

요렇게 테이블에 이름을 붙여서 별칭을 추가하는 것도 가능하고,

select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method

출력될 필드에 as를 붙여서 별칭을 추가하는 것도 가능해요! 그럼, count(*)가 아니라 cnt로 출력되었네요!

06. 2주차 숙제

네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기

select payment_method, count(*) from orders
where email like '%naver.com' and course_title = '앱개발 종합반'
group by payment_method

2주차 수업 완강했어요 👏

💡 공부를 마치며...

1) order by의 기본정렬은 오름차순이며, 내림차순정렬은 desc를 붙이면 돼요. 2) 평균을 구하는 avg(필드명)은 보통 소수점자리로 계산되어, round()와 함께 쓰면 좋아요. round(avg(필드명),소수점자리수)로 표기하며, 0은 일단위, 2는 소수점 둘째자리까지 표시한다는 의미이다. 3) OO별..에서 OO이 범주필드를 의미한다.

1. DB에서 데이터를 가져오는 기초적인 문법인 Select와 Where문을 이해
2. 조금 더 복잡한 분석을 위해 자주 사용되는 유용한 문법을 익히기

** * 필수 프로그램 설치 ** : DBeaver [디비버] (다운로드 링크)

01. Select 쿼리문이란

1) Select 쿼리문

쿼리(Query)문이란? 쿼리는 질의를 의미하죠. 데이터베이스에 명령을 내리는 것을 의미합니다. 여기서 Select 쿼리문은, 데이터베이스에서 '데이터를 선택해서 가져오겠다'는 의미입니다.

👉 Select 쿼리문은 1) 어떤 테이블에서 2) 어떤 필드의 데이터를 가져올지 로 구성됩니다.

2) Select 쿼리문 연습

▶︎ orders 테이블의 데이터 가져와보기

select * from orders;

▶︎ orders 테이블의 데이터 가져와보기

select * from orders;

▶︎ orders 테이블의 데이터 가져와보기

select * from orders;

▶︎ orders 테이블의 특정 필드만 가져와보기

select created_at, course_title, payment_method, email from orders;

02. Where

: 특정 데이터만 가져오고 싶다면?

1) Where 절의 개념

👉 Where 절은, Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것을 의미해요.

▶︎ orders 테이블에서 결제수단이 카카오페이인 데이터만 가져와줘!

select * from orders
where payment_method = "kakaopay";

▶︎ point_users 테이블에서 포인트가 5000점 이상인 데이터만 가져와줘!

select * from point_users
where point >= 5000;

▶︎ orders 테이블에서 주문한 강의가 앱개발 종합반이면서, 결제수단이 카드인 데이터만 가져와줘!

select * from orders
where course_title = "앱개발 종합반" and payment_method = "kakaopay";

2) Select 쿼리문에 Where 절 함께 써보기

👉 (1) 원하는 테이블과 (2) 조건, 이 두 가지만 기억 여러 조건은 and로 함께 넣을 수 있다.

▶︎ 포인트가 20000점보다 많은 유저만 뽑아보기!

select * from point_users
where point > 20000;

▶︎ 성이 황씨인 유저만 뽑아보기!

select * from users
where name = "황**";

▶︎ 웹개발 종합반이면서 결제수단이 CARD인 주문건만 뽑아보기!

select * from orders
where course_title = "웹개발 종합반" and payment_method = "CARD";

03. Where 절과 자주 같이 쓰는 문법

1) '같지 않음' 조건 걸어보기

'같지 않음' 조건은 != 로 걸 수 있습니다.

▶︎ '웹개발 종합반'을 제외하고 주문데이터를 보고 싶어졌어요. 어떻게 하면 좋을까요?

select * from orders
where course_title != "웹개발 종합반";

2) '범위' 조건 걸어보기

'범위' 조건은 between 으로 걸 수 있어요.

▶︎ 7월 13일, 7월 14일의 주문데이터만 보고 싶어졌어요. 어떻게 해야 할까요?

select * from orders
where created_at between "2020-07-13" and "2020-07-15";

3) '포함' 조건 걸어보기

'포함' 조건은 in 으로 걸 수 있어요.

▶︎ 1, 3주차 사람들의 '오늘의 다짐' 데이터만 보고 싶어졌어요. 어떻게 해야 할까요?

select * from checkins 
where week in (1, 3);

4) '패턴' (문자열 규칙) 조건 걸어보기

'패턴' 조건은 like 으로 걸 수 있어요.

• where email like 'a%': email 필드값이 a로 시작하는 모든 데이터
• where email like '%a' email 필드값이 a로 끝나는 모든 데이터
• where email like '%co%' email 필드값에 co를 포함하는 모든 데이터
• where email like 'a%o' email 필드값이 a로 시작하고 o로 끝나는 모든 데이터

▶︎ 다음 (daum) 이메일을 사용하는 유저만 보고 싶어졌어요. 어떻게 하죠?

select * from users 
where email like '%daum.net';

04. 이외 유용한 문법

1) 일부 데이터만 가져오기 : Limit

테이블에 어떤 데이터가 들어있나 잠깐 보려고 들어왔는데, 데이터를 다 불러오느라 시간이 오래 걸리면 힘들겠죠?

그런 경우를 대비해, 일부 데이터만 가져오는 Limit이라는 기능이 있습니다.

select * from orders 
where payment_method = "kakaopay"
limit 5;

요렇게, 맨 뒤에 limit을 써주고 몇 개 출력할지 숫자를 적어주면 끝! 딱 5개만 출력된 것을 알 수 있죠?

2) 중복 데이터는 제외하고 가져오기: Distinct

고객이 사용한 결제수단이 어떤 종류가 있을지 궁금할때 어떻게 할까요?

select distinct(payment_method) from orders;

출력하고 싶은 필드에 distinct로 감싸주면 끝! 몇 개의 결제수단 종류가 있는지 알 수 있어요.

3) 몇 개인지 숫자 세보기: Count

orders 테이블에 데이터가 몇 개 들어있는지 궁금해요!

select count(*) from orders

한 줄로 끝! select * from orders에서 *을, count로 감싸주면 됩니다.

4) Distinct와 Count를 같이 써보기

회원 분들의 성(family name)씨가 몇개인지 궁금하다면?

select distinct(name) from users;

이렇게 하면 성씨가 쭉 나오죠?

SELECT count(distinct(name)) from users;

그리고, 여기에 count를 입혀주면 몇 개의 성씨가 있는지 알 수 있습니다.

05. 1주차 숙제

naver 이메일을 사용하면서, 웹개발 종합반을 신청했고 결제는 kakaopay로 이뤄진 주문데이터 추출하기

select * from orders
where email like '%naver.com'
and course_title = '웹개발 종합반'
and payment_method = 'kakaopay'

1주차 수업 완강했어요! 👏

💡공부를 마치며...

1) 문자는 작은따옴표(')로 감싸고, 숫자는 그냥 써 주세요. 2) 테이블이 뭐가 있는지 다 보고 싶으면,

show tables;

3) 쿼리 마지막에 (;)세미콜론을 붙여서 종결되었다고 표기하기도 해요. 4) 조건에 %를 넣어서 앞뒤 조건의 모든것?을 찾겠다는 것을 의미해요.

이렇게 쿼리를 작성하면 편해요!

1) show tables로 어떤 테이블이 있는지 살펴보기 2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기 3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기 4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기 5) 범주별로 통계를 보고싶은 필드를 찾기 6) SQL 쿼리 작성하기!