개발일지 4 - 엑셀보다 쉬운 SQL 배워보자, Subquery

개발일지 4 - 엑셀보다 쉬운 SQL 배워보자, Subquery

내일배움카드사용으로 배우는  SQL초보 코딩, 스파르타코딩클럽에서 배우기 시작 4번째

 

 

1. Subquery

쿼리 안의 쿼리

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

이제까지 Subquery없이도 원하는 데이터를 뽑아냈지만 Subquery를 사용하면 더 간단하게 데이터를 뽑아낼 수 있다는 말!

 

1.1 kakaopay로 결제한 유저들의 정보 보기 (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'

1.2 kakaopay로 결제한 유저들의 정보 보기 (subquery 사용)

1.2.1 먼저 kakaopay로 결제한 user_id 모두 구하기 → K 라고 하자, 

select user_id from orders
where payment_method = 'kakaopay'

1.2.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'
)

 

2. 자주 사용되는 Subquery유형

Where, Select, From 절에서 유용하게 사용된다.

2.1 Where 에 들어가는 Subquery

Where은 조건문, 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.2 Select  에 들어가는 Subquery

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;

쿼리 실행 순서

1. 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
2. select 안의 subquery가 매 데이터 한줄마다 실행되는데
3. 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
4. 함께 출력

2.3 From 에 들어가는 Subquery(가장 많이 사용됨)

내가 만든 Select와 이미 있는 테이블을 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가 실행된다.

 

 

3. Subquery 연습

3.1 포인트가 평균보다 많은 사람들의 데이터 추출 *참고 : 평균 포인트는 5380점

select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2);

3.2 checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

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

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

select a.course_id, b.cnt_checkins, a.cnt_total from
(
   select course_id, count(*) as cnt_total from orders
   group by course_id
) a
inner join (
   select course_id, count(distinct(user_id)) as cnt_checkins from checkins
   group by course_id
) b
on a.course_id = b.course_id

 

4. 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

이렇게 계속 서브쿼리가 붙으면 inner join 안쪽이 헷갈린다. 

그때 쓰는 것이 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

 

5. 실전에서 유용한 SQL 문법 (문자열, case)

5.1 문자열 쪼개기

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

Substring_index

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

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

@를 기준으로 이메일 도메인만 가져오기

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

5.2 문자열 일부만 출력하기

Substring 사용

order 테이블에서 날짜까치 출력하기

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

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

5.3 Case : 경우에 따라 원하는 값을 새 필드에 출력하기

예제) 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시하기

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