엑셀보다 쉬운 SQL 4주차

Subquery

 

쿼리 안의 쿼리라는 의미

하위 쿼리의 결과를 상위 쿼리에서 사용하면 SQL 쿼리가 간단해진다.

 

kakaopay 로 결제한 유저들의 정보보기

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'

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

---

where 필드명 in (subquery)

 

select user_id, name, email  from users
where 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
		where user_id = c.user_id	   
	   ) as avg_likes_user
from checkins c

---

from 에 들어가는 subquery

 

순서

1. course_id 별 유저의 체크인 개수를 구해보기

2. course_id 별 인원 구해보기

3. course_id 별 like 개수에 전체 인원을 붙이기

4. 퍼센트를 나타내기

5. 강의제목도 나타내기

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

---

문자열

 

- 문자열 쪼개보기

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

- 문자열 일부만 출력하기

select order_no, created_at, substring(created_at, 1, 10) as date from orders
-- orders 테이블에서 날짜까지 출력하게 하기

select created_at, substring(created_at, 1, 10) as date, count(*) from orders
group by date
-- 일별로 몇 개씩 주문이 일어났는지 살펴보기

---

CASE

 

경우에 따라 원하는 값을 새 필드에 출력

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

subquery 를 이용하면 통계도 낼 수 있다.

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 를 많이 사용하며 문제를 풀어봐야할듯하당