[엑셀보다 쉬운 SQL] 4주차 + 기본 sql 문법

원하는 데이터를 쉽게, Subquery

큰 쿼리문 안에 들어가는 쿼리문들을 다 서브쿼리문이라고 한다.

핵심 내용을 서브쿼리가, 그다음 부가적인 부분을 밖에서 처리한다.

 

where절에 들어갈 수도,

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

 

select절에 들어갈 수도,

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

 

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

---

깔끔하게 바꿔줄 with절

with 테이블A as ( sql),
테이블B as (sql)
(본 sql문)

select c.title,
	  a.cnt_checkins,
	  b.cnt_total,
	(a.cnt_checkins/b.cnt_total) asratio
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 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 email을 나눌건데 @기준으로 마지막 것만 보여줘(-1) email을 나눌건데 @기준으로 첫번째 것만 보여줘(1)

 

문자열 일부만 출력

 

SUBSTRING

select order_no, SUBSTRING(created_at, 1, 10) as date from orders 시작포인트 부터 10자까지 출력

---

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

 

CASE

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

 

해석) point가 10000이 넘으면 '잘 하고 있어요!' 문구를 10000이 넘지 않으면 '조금만 더 파이팅!'문구를 써주고 끝내라(end)

 

CASE subquery형식으로 통계를 낼 수 있다.

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

 

CASE subquery형식으로 통계를 낼 수 있다. + 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

---

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

*테이블: enrolled_details

*done_cnt는 들은 강의의 수 (done = 1)

*total_cnt는 전체 강의의 수

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

 

💡 수강등록정보(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,
	  round((a.done_cnt/b.total_cnt), 2) as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

 

💡 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 비율까지 출력해 보기 (+다른 방식으로도 가능!)

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

<해석>

done이 1인 강의의 수 3개를 합치면 = 3

done이 1인 값을 3번 더하면 = 3

(코드가 다르기에 순서는 다를 수 있다는 점 참고!)