SQL 4주차

Subquery

 

Subquery : 쿼리 안의 쿼리

 

1) where

결과를 조건에 활용하는 방식으로 유용하게 사용 

👉  where 필드명 in (subquery)

카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때는 아래와 같이 표현할 수 있겠죠.

 

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

 

쿼리 실행 순서

(1) from 실행: users 데이터를 가져와줌

(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌

(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌

(4) 조건에 맞는 결과 출력

 

 

2) Select

기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용

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

'오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 확인

 

먼저 평균부터 구하기 (user_id = '4b8a10e6'를 예시로)

select avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'

 

그러면 이렇게 표현 가능

select c.checkin_id, c.user_id, c.likes, 
(select avg(likes) from checkins c2
where c2.user_id = c.user_id) as avg_like_user
from checkins c;

 

쿼리가 실행되는 순서

(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서

(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데

(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서

(4) 함께 출력해준다!

 

3) from(가장 많이 사용되는 유형)

내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용

유저 별 좋아요 평균을 먼저 구해볼까요?

select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

 

여기서 해당 유저 별 포인트를 보고 싶다면?

select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
) a on pu.user_id = a.user_id

 

쿼리 실행 순서

(1) 먼저 서브쿼리의 select가 실행

(2) 이것을 테이블처럼 여기고 밖의 select가 실행!

 

 

4) 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 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문법 (문자열, Case)

 

1) SUBSTRING_INDEX 문법

➡️ 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶을때

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

➡️ @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!

 

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

➡️ @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

 

2) SUBSTRING 문법

➡️ 문자열 일부만 출력하기 

SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

orders 테이블에서 날짜까지 출력하게 해보기

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

 

일별로 몇 개씩 주문이 일어났는지 살펴보기

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

 

3) CASE 문법

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

10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까?

 

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;