[SQL] 4주차 (Subquery, with ...)

1. Subquery

Subquery

: 쿼리 안의 쿼리. 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 기능

 

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

→ 괄호 안에 있는 것이 서브쿼리. 

실행순서는 하위쿼리(서브쿼리) > 상위쿼리4

 

 

where 에 들어가는 subquery

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

 

select 에 들어가는 subquery

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 에 들어가는 subquery 

• 가장 많이 사용되는 유형

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

---

2. with

with

: 임시테이블 또는 가상 테이블의 개념으로, 반복되는 서브쿼리 블록을 하나의 with절 블록으로 만들어 사용 가능

 

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 절 사용시 from 뒤에 나오는 table1에 이름 붙여주기)

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

실행결과

---

3. 유용한 SQL 문법 (문자열)

• 실무에서 문자열 데이터를 원하는 형태로 정리하는 경우가 많음
• 문자열을 쪼갤 때는 SUBSTRING_INDEX  문법 사용
• 문자열 일부만 출력할 때는 SUBSTRING 문법 사용

SUBSTRING_INDEX

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

→ 이메일에서 도메인만 가져온 것으로, -1은 마지막을 의미함

 

SUBSTRING

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

→ create_at 에서 년도, 날짜만 가져온 것

---

4. 유용한 SQL 문법 (Case)

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

→ 포인트가 10000보다 크면 잘하고 있어요 라는 메세지를 보여주고, 그렇지 않으면 조금만 더 화이팅이라는 메세지를 보여주고 끝내기

 

 

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

→ subquery 를 이용하면 통계 내는 것도 가능

---

5. 퀴즈 & 과제

select * from point_users pu1
 where pu1.point > (
  	select avg(pu2.point) from point_users pu2
 )
 order by point

>> 포인트가 평균보다 많은 사람들의 데이터 추출

 

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

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

 

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

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

 

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

>> checkins 테이블에 과목명별 평균 likes 수 필드 우측에 붙이기

 

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

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

 

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

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

 

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

 

>> '화이팅'이 포함된 오늘의 다짐만 출력

 

with table1 as (
	select enrolled_id, 
		   count(*) as total_cnt 
	  from enrolleds_detail ed1 
	group by enrolled_id
), table2 as (
	select enrolled_id, 
		   count(*)as done_cnt 
	  from enrolleds_detail ed2
	 where done = 1
	 group by enrolled_id
)

select a.enrolled_id, 
	   b.done_cnt, 
	   a.total_cnt from table1 a
 inner join table2 b
    on a.enrolled_id = b.enrolled_id

>> 수강등록정보별 전체 강의 수와 들은 강의 수 출력

 

with table1 as (
	select enrolled_id, 
		   count(*) as total_cnt 
	  from enrolleds_detail ed1 
	group by enrolled_id
), table2 as (
	select enrolled_id, 
		   count(*)as done_cnt 
	  from enrolleds_detail ed2
	 where done = 1
	 group by enrolled_id
)

select a.enrolled_id, 
	   b.done_cnt, 
	   a.total_cnt,
	   round((b.done_cnt/a.total_cnt),2) as ratio 
  from table1 a
 inner join table2 b
    on a.enrolled_id = b.enrolled_id

>> 수강등록정보별 전체 강의 수와 들은 강의 수, 진도율 출력