[수업 목표]
- Subquery(서브쿼리)의 사용 방법을 배워본다
- 실전에서 유용한 SQL 문법을 더 배워본다
- SQL을 사용하여 실전과 같은 데이터분석을 진행해본다
원하는 데이터를 더 쉽게: Subquery
- Subquery 사용방법 익혀보기
- Subquery : 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것
- ex) kakaopay로 결제한 유저들의 정보 보기
- 우선, 이렇게 볼 수 있겠죠? users와 orders의 inner join으로!
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' - subquery 이용하기
1. kakaopay로 결제한 user_id를 모두 구해보기 - k
select user_id from orders
where payment_method = 'kakaopay'
2. user_id가 k에 있는 유저들만 골라보기
select u.user_id, u.name, u.email from users u
where u.user_id in (
select user_id from orders
where payment_method = 'kakaopay'
)
- 우선, 이렇게 볼 수 있겠죠? users와 orders의 inner join으로!
- 자주 쓰이는 Suqquery 유형
- Where 절에 들어가는 Subquery
- Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용. -
ex) where 필드명 in (subquery) - ex) 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력하기
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) 조건에 맞는 결과 출력
- Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용. -
- Select 절에 들어가는 Subquery
- 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
ex) select 필드명, 필드명, (subquery) from .. - ex) '오늘의 다짐' 데이터에서, 좋아요의 수를 본인이 받는 좋아요 수의 평균값과 비교하고 싶을때
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) 함께 출력해준다!
- 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
- From 절에 들어가는 Subquery (가장 많이 사용되는 유형)
- 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용
- ex)포인트와 like의 상관정도 알아보기
- 유저 별 좋아요 평균
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id - 포인트 정보와 inner join
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가 실행!
- Where 절에 들어가는 Subquery
With절
- with 절로 더 깔끔하게 쿼리문을 정리하기
- with절이란? 이름을 가진 SubQuery를 정의한 후 사용하는 구문.
- Query의 전체적인 가독성을 높이고, 재사용할 수 있는 장점이 있음.
- with [별명] as (subquery)
실전에서 유용한 SQL 문법 (문자열, Case)
- 문자열 데이터 다뤄보기
- SUBSTRING_INDEX - 문자열 쪼개기
- ex) 이메일에서 아이디만 가져와보기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻 - ex) 이메일에서 이메일 도메인만 가져와보기
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻
- ex) 이메일에서 아이디만 가져와보기
- SUBSTRING - 문자열 일부만 출력하기
- ex) orders 테이블에서 날짜까지 출력하게 해보기
select order_no, created_at, substring(created_at,1,10) as date from orders
SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지) - ex) 일별로 몇 개씩 주문이 일어났는지 살펴보기
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
- ex) orders 테이블에서 날짜까지 출력하게 해보기
- SUBSTRING_INDEX - 문자열 쪼개기
- CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기
- ex) 포인트 보유액에 따라 다르게 표시해주기
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu; - ex) 심화과정
1. 우선 몇 가지로 구분을 나누고,
select pu.point_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
2. 서브쿼리를 이용해서 group by로 통계
select level, count(*) as cnt from (
select pu.point_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 lv
3.with 절로 정리하기
with table1 as (
select pu.point_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 level, count(*) as cnt from table1
group by lv
- ex) 포인트 보유액에 따라 다르게 표시해주기