Subquery
원하는 데이터를 더 쉽게
1. Where 에 들어가는 Subquery
* 카카오페이로 결제한 사람들의 데이터를 찾아보기
select u.user_id, u.name, u.email, o.payment_method from orders o
inner join users u on o.user_id = u.user_id
WHERE o.payment_method = 'kakaopay'
select user_id , name, email from users u
WHERE user_id in (
select user_id from orders o
WHERE payment_method = 'kakaopay'
)
// 이런 식으로 쿼리문 안에 들어가는 쿼리문을 subquery라고 한다.
// 엑셀로 하기 어려운 처리들을 subquery로 좀 더 쉽게 할 수 있다.
2. Select 에 들어가는 Subquery
Select는 결과를 출력해주는 부분! 기존 테이블에 함께 보고 싶은 통계 데이터를 손쉽게 붙이는 것에 사용.
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
3. From 에 들어가는 Subquery(가장 많이 사용되는 유형!)
내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때!
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
Where 절에 들어가는 Subquery 연습
이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
select * from point_users pu2
WHERE point > (
select avg(point) from point_users pu
inner join users u on pu.user_id = u.user_id
WHERE u.name = '이**'
)
Select 절에 들어가는 Subquery 연습
checkins 테이블에 과목명별 평균 likes 수 필드 우측에 붙여보기
select c3.title, c.checkin_id, c.course_id, c.user_id, c.likes,
(
select round(avg(likes),1) from checkins c2
WHERE course_id = c.course_id
) as course_avg
from checkins c
inner join courses c3 on c.course_id = c3.course_id
From 절에 들어가는 Subquery 연습
Quiz.
1. course_id별 유저의 체크인 개수를 구해보기(distinct로 세기)
2. course_id별 인원 구하기
3. course_id별 like 개수에 전체 인원 붙이기
4. 퍼센트 나타내기
5. 강의 제목으로 나타내기
select c2.title,
COUNT(DISTINCT(c.user_id)) as cnt_checkins,
a.cnt_total,
COUNT(DISTINCT(c.user_id))/a.cnt_total as ratio from checkins c
inner join (
select o.course_id, COUNT(*) as cnt_total from orders o
group by o.course_id
) a on c.course_id = a.course_id
inner join courses c2 on c.course_id = c2.course_id
group by c.course_id
With
subquery가 많이 들어가 가독성이 떨어질 때는
코드 가장 위에서 with table as (...), table2 as(...) 로 table을 지정해준 뒤 쓰면 좋다.
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 문법
1. 문자열 - 문자열 데이터 다뤄보기
1) 문자열 쪼개보기
이메일 주소에서 @ 앞의 아이디만 가져오거나, @ 뒤의 이메일 도메인을 가져오고 싶어요.
SUBSTRING_INDEX 라는 문법을 사용하면 된다.
select user_id,
email,
SUBSTRING_INDEX(email, '@', 1),
SUBSTRING_INDEX(email, '@', -1)
from users u
// email을 @기준으로 나눴을 때 1번째(아이디), -1번째(마지막)(도메인)
2) 문자열 일부만 출력하기
orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!
select SUBSTRING(created_at, 1, 10) as date, COUNT(*) from orders o
group by date
// SUBSTRING(created_at, 1, 10) : created_at을 1번째부터 10개의 글자만큼 자른다.
2. Case
1) 경우에 따라 원하는 값을 새 필드에 출력해보기
select pu.user_id, point,
(case when point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as msg
from point_users pu
2) 실전을 위한 트릭.
with table1 as (
select pu.user_id, point,
(case when 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
'STUDY > SQL' 카테고리의 다른 글
스파르타 코딩클럽 <엑셀보다 쉬운 SQL> 3주차 문법 정리 (0) | 2023.05.13 |
---|---|
스파르타 코딩클럽 <엑셀보다 쉬운 SQL> 2주차 문법 정리 (0) | 2023.05.13 |
스파르타 코딩클럽 <엑셀보다 쉬운 SQL> 1주차 문법 정리 (0) | 2023.05.13 |