항해99 온보딩 스터디[SQL 학습] 14일차

Hohomi·2023년 3월 22일

항해99 온보딩 스터디

목록 보기

13/17

스터디 14일차 : SQL 학습

14일차 스터디에는 SQL 강의 3,4주차를 듣는 커리였는데 상당히 빡셌다.. join과 subQuery 등을 처음 보는 상태에서 습득하고, 퀴즈까지 풀려니 좀 빡빡했달까. 그래도 SQL 기본 문법이 생각보다 어려운 편은 아니어서 다행^^이기도 했고, 몇 줄의 문장으로 원하는 데이터가 쏙쏙 뽑혀나오는 게 놀랍기도 했다. 정말 컴퓨터란.. 놀랍군~!

🥕 Left Join과 Inner Join

join이란?
: 두 테이블의 공통된 정보(key값)를 기준으로 테이블을 연결하여 한 테이블처럼 보는 것. 테이블이 이어진다!
left join (left/right outer join)
: 조인하는 테이블에 데이터가 없더라도 기준 테이블에만 데이터가 존재하면 조회가 된다.
(기준 테이블의 데이터는 모두 조회됨)
: 조인 테이블에 조회를 원하는 데이터가 없을 경우 NULL로 표시된다.
: left join에서는 inner join과 달리, 어떤 테이블에 어떤 필드를 붙일 건지의 순서가 중요하다.(기준 테이블-조인 테이블 상관관계)

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

inner join
: 기준 테이블, 조인 테이블 모두 가지고 있는 데이터만 조회가 가능하다.

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

실습

--- 네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기!
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

--- 7월10일 ~ 7월19일에 가입한 고객 중 포인트를 가진 고객의 숫자, 전체 숫자, 비율 조회
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'
 
 --- ⭐️ count는 NULL을 세지 않음!!

🥕 결과물을 합치는 Union

한 필드의 데이터 조건별 결과를 한꺼번에 보고 싶을 경우에 사용한다.(select 한번에 2가지 필드가 이어져서 출력됨)
필드명은 당연히 같아야 한다.
예를 들어 한 필드 내의 데이터 중 월별로 분류된 데이터들을 한꺼번에 보고 싶을 때 사용할 수 있다.
단, union을 사용하면 내부 정렬이 먹지 않는다.(이런 경우 SubQuery를 사용하면 정렬까지 가능하다.)

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

🥕 Subquery

Subquery란?
: 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것
where 필드명 in (subquery) : subquery의 결과를 조건에 활용

--- 카카오페이로 결제한 유저들의 정보 보기
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'
)

select 필드명, 필드명, (subquery) from ..

--- '오늘의 다짐' 좋아요 수와 평소 받은 좋아요 수의 평균을 비교하는 데이터 조회
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;

from에 들어가는 subquery : 가장 많이 사용되는 유형!
: 내가 만든 select와 이미 있는 테이블을 join 하고 싶을 때 사용

--- 유저들의 평균 좋아요 수와 포인트 조회 (좋아요 수와 포인트의 상관 관계 추측 가능)
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

🥕 쿼리문을 깔끔하게 정리하는 with절

서브쿼리를 많이 사용하는 등 쿼리문이 길어지는 상황에서 쿼리문을 정리해줌으로써 핵심 쿼리문이 눈에 잘 들어오게 하는 역할을 한다.

--- 예를 들어 아래처럼 서브쿼리가 2개 붙어서 길어진 쿼리문의 경우
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

--- with절을 사용하면 select 이후 쿼리문이 훨씬 깔끔해진다.
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

🥕 문자열 데이터 다루기

현업에서는 문자열 데이터를 원하는 형태로 정리해야 하는 경우가 많다.(고 한다.)

--- 문자열 쪼개기 : SUBSTRING_INDEX
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

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

--- 서브스트링 인덱스 맨 뒤의 숫자가 1이면 '@'를 기준으로 앞부분(아이디)이, -1이면 뒷부분(도메인)이 조회된다.

--- 문자열 일부만 출력 : SUBSTRING
select order_no, created_at, substring(created_at,1,10) as date from orders

--- SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
--- 위 경우 created_at 날짜의 필요한 부분만 출력된다.

🥕 경우에 따라 원하는 값을 출력하는 CASE

select pu.point_user_id, pu.point,
case 
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu

--- 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 level
	from point_users pu
)
select level, count(*) as cnt from table1
group by level