4주차에서는 서브쿼리와 자주 쓰이는 SQL문법에 대해 공부했다. 서브쿼리는 데이터를 좀 더 간편하고 알아보기 쉬운 문구로 작성하여 뽑아내기 위해 쓰인다. 수학 문제나 나올 때 괄호 안의 식을 먼저 계산해 주는 것 같은 개념으로 이해하면 될 것 같다.
사용 프로그램 : DBeaver , 사용 DB : 스파르타 코딩클럽
학습자료
Subquery
서브쿼리가 자주 사용되는 유형으로는 where, select, from 절에서 자주 사용 된다.
where
select * from users u
where u.user_id in (select o.user_id from orders o
where o.payment_method = 'kakaopay')이 경우 orders 테이블에서 payment_method가 kakaopay인 user_id 값이 where u.user_id in (여기)에 들어간다고 보면 된다. 결과적으로 users 테이블에서 kakaopay가 payment_method인 user_id를 구할 수 있게 된다.
select
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;괄호 안의 select 문을 먼저 파악해보면 checkins 테이블에서 c2.user_id와 c.user_id가 같은 경우에 likes의 평균값을 구하는 것이다. 여기서 밖의 select문의 결과가 출력될 때 한 줄씩 출력 되는 과정이므로 avg_like_user라는 필드의 결과값은 한 줄씩 출력되는 c_user_id값이 c2.user_id값와 같은 avg(likes)를 출력한다고 이해하면 될 것 이다.
from
from 은 불러오는 테이블을 지칭하기 때문에 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괄호 안의 select문을 보면 checkins 테이블에서 user_id를 기준으로 group by 하여 user_id와 likes의 평균을 소수점 1자리 까지 반올림하는 테이블이 만들어진다. 바깥의 select 문을 보면 point_users 와 괄호안의 select문의 결과로 만들어진 테이블을 user_id를 키값으로 조인하여 pu.user_id, a.avg_like, pu.point 의 값을 도출해 낼 수 있다.
! 서브쿼리를 사용할 경우 별칭사용에 주의 하자 !
With
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이런 식으로 표현되면 table1 과 table2로 서브 쿼리의 내용을 쉽게 파악 할 수 있고 select문에서는 어떤 형태의 문법인지 파악하기가 용이하다.
! 실행 시 전체문장이 이어져 있는지 확인해야된다 !
! with 문과 select 문이 떨어져 있을 경우 드래그 하여 실행 !
SUBSTRING
문자열 중에서 일부만 가져오고 싶을 때 사용한다.
substring(created_at,1,10)
앞 칸은 가져올 필드 값, 두번째는 가져오기 시작하는 위치,세번째는 가져올 길이를 뜻한다.
SUBSTRING_INDEX
문자열 중 기준을 잡아 그 뒤나 앞의 문자열만 가져오는 기능이다.
SUBSTRING_INDEX(email, '@', 1)
앞 칸은 가져올 필드 값, 두번째는 기준 문자, 세번째는 1이면 앞의 문자열, -1이면 뒤의 문자열을 가져오면 된다.
Case
if와 비슷한 의미로 경우에 따라 값을 다르게 출력하게 해주는 명령어이다.
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu이런 식으로 pu.point가 10000보다 크면 '1만 이상'을 5000보다 크면 '5천 이상'을 아니면 5천 미만을 출력한다
else pu.point 같이 else에 원래 필드 값을 넣으면 나머지 부분은 값이 그대로 출력 된다.
느낀점
3주차까지는 직관적으로 보고 해석하기가 쉬웠는데 4주차가 되고 서브쿼리를 배우면서 퀴즈를 풀 때 고민하는 시간도 길어지고 오류도 더 자주 뜨는 것 같았다. 어떠한 데이터를 뽑는 문제에서 쿼리문을 작성할 때 서브쿼리를 어떻게 써야 내가 원하는 방식의 과정을 만들지 잘 생각이 나지 않는다. 코딩테스트 같은 문제를 풀면서 익숙해지는 수 밖에 없을 것 같다. 분발하자!!
