SQL 기초 2

학습 강의 액셀보다 쉬운 SQL  3, 4 주차

 

Join - 여러 테이블을 연결

Join?

두 테이블의 공통된 정보(key값)을 기준으로 테이블을 연결해서 한 테이블처럼 보는 것

 

종류

• Left Join : 왼쪽 테이블을 중심으로 오른쪽 테이블을 매치(key 값 기준)
• Inner Join : 두 테이블의 교집합이 되는 값을 매치(key 값 기준)

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

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

 

연습

orders 테이블에 users 테이블 연결

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

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

 

checkins 테이블에 users 테이블 연결

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

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

 

enrolleds 테이블에 courses 테이블 연결

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

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

 

쿼리 작성 순서 및 실행 순서

작성 순서
select -> distinct -> from -> join -> on -> where -> group by -> having -> order by -> limit -> offset

실행 순서
from -> on -> join -> where -> group by -> having -> select -> distinct -> order by -> offset -> limit

 

연습2

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

• 과목별 오늘의 다짐 통계치 확인

select c.course_id, c2.title, count(*) as cnt from checkins c 
inner join courses c2 on c.course_id = c2.course_id
group by c.course_id

 

point_users 테이블에 users 테이블 연결해서 순서대로 정렬

• 많은 포인트를 얻은 순서대로 유저의 데이터 확인

select pu.user_id, u.name, u.email, pu.point from point_users pu
inner join users u on pu.user_id = u.user_id
order by pu.point desc

 

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

• 네이버 이메일을 사용하는 유저 중, 성씨별 주문 건수 세기

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

 

연습3

결제 수단 별 유저 포인트의 평균 값 구하기

point_users에 orders 붙이기

select o.payment_method, round(avg(pu.point),0) from point_users pu
inner join orders o on pu.user_id = o.user_id 
group by o.payment_method

 

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

enrolleds에 users를 붙이기

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

 

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

courses에 enrolleds를 붙이기

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

 

웹개발, 앱개발 종합반의 week 별 체크인 수를 세기

courses에 checkins를 붙이기

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

 

위 문제에서 8월 1일 이후에 구매한 고객들만 추출

courses에 checkins 붙이고, checkins에 orders 붙이기

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

 

left join 연습

select u.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 u.name

 

문제 7월 10일부터 7월 19일에 가입한 고객 중 포인트를 가진 고객의 수, 전체 수, 비율 확인

select count(pu.point) as pnt_user_cnt, 
       count(u.user_id) as tot_user_cnt,
       round(count(pu.point)/count(u.user_id), 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'

 

Union 사용

select로 선택한 쿼리 두 개를 위아래로 합쳐주는 쿼리.

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

 

 

3주차 숙제

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

enrolleds, enrolleds_detail join 조인 필드 enrolled_id

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

 

 

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'

 

위 코드를 subquery로 변경

select user_id, name, email from users
where user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
)

 

where subquery
where 필드명 in (subquery)

 

subquery 연습

유저별 오늘의 다짐 좋아요 수 및 평균 좋아요 수 확인.

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

 

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

 

 

from 절에 들어 가는 subquery

유저별 point 및 좋아요 평균 수 확인.

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 
) a on pu.user_id = a.user_id

 

 

연습

전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출

select user_id, point from point_users
where point > (
	select round(avg(point),1) from point_users
)

 

이씨 성을 가진 유저의 포인트 평균보다 큰 유저들의 데이터 추출

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

 

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

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

 

위 문제에서 course_id 위치에 title 넣기

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

 

from subquery 연습

 

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절 연습

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

 

 

실전에서 유용한 SQL

문자열 쪼개기

substringindex 사용해서 이메일의 아이디만 추출, 도메인만 추출하기

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

 

문자열 일부만 출력

substring 사용해서 orders 테이블의 날짜까지만 출력, 일별 주문 건수 확인

select order_no, SUBSTRING(created_at,1,10) as date  from orders
select SUBSTRING(created_at,1,10) as date, count(*) as cnt  from orders
group by date

 

Case

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

 

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(*) as cnt from table1 a
group by a.lv

 

문제 풀기

평균 이상 포인트를 가지고 있으면 '잘하고 있어요'/ 낮으면 '열심히 합시다' 출력

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

 

이메일 도메인 별 유저의 수 세기

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

 

화이팅이 포함된 오늘의 다짐만 뽑기

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

 

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

 

숙제 - 문법 정리