나만의 정리노트 - SQL 개발일지

1주차 : Where절

/* 네이버 이메일을 사용하면서 웹개발 종합반을 신청하였고,
결제는 카카오페이로 이루어진 주문데이터 추출하기

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

2주차 : Group by 와 Order by

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

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

3주차 : Inner join || Left join || Union

Join 연습4

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

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

Join 연습5

/* 추가적으로 8월 1일 이후에 구매한 고객들만 찾아보기

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

Left Join

/* 7월 10일 - 7월 19일 가입고객 중,
포인트를 가진 고객의 숫자, 전체 고객 수 그리고 비율 알아내기
(users 테이블에 point_users 테이블을 갖다붙임 : users > point_users)

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 ALL

/* Join 연습5 추가 예시 - order by는 union 후에 쓸 수 있음

(
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
order 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
order by c1.title, c2.week
)

4주차 : Subquery

Where절

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

select * from point_users pu 
where point > (
	select avg(point) from point_users
)


이어서 문제

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

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

또는

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

Select절

/* '오늘의 다짐' 좋아요의 수가 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지

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

 이어서 문제

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

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 
  
이어서 문제

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

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절

/* 포인트가 많은 유저들은 평균 좋아요의 수도 높을까?

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

/* course_id별 유저 체크인 수에 전체 인원 붙이기

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


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

이어서 문제

/* 등록 유저 수 대비 체크인 유저 수의 비율 구하기

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

이어서 문제

/* course_id를 title로 표현하기

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 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 - 문자열

/* 문자열 쪼개기

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


/* 문자열 일부만 출력하기

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


/* 응용1 - 날짜별 구매 수

select substring(created_at,1,10)as date, count(*) from orders
group by date


/* 응용2 - 날짜 중 시간만 뽑아내기

select created_at, substring(created_at,12,8) from orders

유용한 SQL - Case

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


/* 응용1 - case한 결과를 기준으로 개수세기

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


/* 응용2 - with절로 깔끔하게 표현하기

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

퀴즈1

/* 평균이상 포인트를 가지고 있으면 '잘하고 있어요' 
낮으면 '열심히 합시다!'로 표기하기 
- case에 subquery가 있을 경우 괄호해주기

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

퀴즈2

/* 이메일 도메인별 유저의 수 세어보기

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

퀴즈3

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

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

퀴즈4

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

select a.enrolled_id, a.done_cnt, b.total_cnt from (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
) a
inner join (
	select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id 
) b on a.enrolled_id = b.enrolled_id


/* 응용 - with 절로 더 쉽게

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 from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

퀴즈5

/* 수강등록정보(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)
  from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id


/* 응용 - 더 간단하게 생각해보기

select enrolled_id,
	   sum(done) as done_cnt,
	   count(*) as total_cnt
  from enrolleds_detail
group by enrolled_id