스파르타 코딩클럽 - 엑셀보다 쉬운 SQL 4주차

Subquery

쿼리 안의 쿼리

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

1) where절에 들어가는 subquery

select * from users

where user_id in (

select user_id from orders

where payment_method = 'kakaopay'

);

괄호 안에 있는 것이 subquery 이다.

실행순서는 괄호 안에 있는 서브쿼리 먼저 실행해서 결과를 만들고
밖에있는 것들을 실행함

subquery는 where절, select절, from절 각각에 다 활용할 수 있다.

2) select절에 들어가는 subquery

e.g. select 필드명, 필드명, (subquery) from... 이런 형태

select c.checkin_id, 
	   c.user_id, 
	   c.likes,
	   (
	   	select avg(likes) from checkins 
	   	where user_id = c.user_id 
	   	) as ave_likes_user
from checkins c

3) from절에 들어가는 subquery -> 가장 많이 사용되는 유형!

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;

내가 만든 서브쿼리를 원래 있었던 테이블처럼 사용할 수 있는게 주로 from절에서의

활용 방법임.

Subquery QUIZ

1) 전체 유저의 포인트 평균보다 큰 유저들의 데이터를 추출해라

-> 포인트가 평균보다 많은 사람들의 데이터를 추출해보자 * 참고 : 평균 포인트는 5380점
힌트 point_users테이블을 이용해서 avg를 구하고 다시 point_users와 join

select * from point_users pu 
where point > (
	select round(avg(point),1) from point_users
)

평균을 숫자로 안 박고 쿼리로 하는 이유는 테이블에 새로운 값이 계속 들어올거고

평균은 계속 바뀔거기 때문에~ ~ ~

2) 이씨 성을 가진 유저의 포인트 평균보다 큰 유저들의 데이터 추출하기

select user_id from point_users
where point > (
	select avg(point) from point_users pu
	inner join users u on pu.user_id = u.user_id 
	where u.name like '이%'
)

3) select 절에 들어가는 subquery 연습해보기

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

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

4) from 절 subquery 퀴즈~

1) course_id별 유저의 체크인 개수 구하기

2) course_id별 인원 구하기

3) course_id별 like 개수에 전체 인원을 붙이기

4) 퍼센트 나타내기...

5) 강의 제목(title)도 나타내기

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 table 1 as ( 서브쿼리1), table2 as (서브쿼리2), ...

실전에서 유용한 SQL 문자열

문자열 쪼개보기

SUBSTRING_INDEX

e.g. 이메일에서 id만 가져오기

골뱅이를 기준으로 첫번째꺼랑 마지막꺼만 가지고 올 수 있음

substring_index(email, '@', 1)

substring(created_at,1,8)

substring(필드명,시작점,부터몇글자)

문자열 일부만 출력하기

Case When 조건문

select pu.user_id, pu.point,
	(case when pu.point > 10000 then '잘 하고 있어요!'
    else '조금만 더 파이팅!' end) as msg
   from point_users pu

case when을 잘 쓰면 통계도 낼수있다

e.g. 구간별로 몇 개가 있는지

select a.lv, count(*) as cnt from (
	select pu.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 a.lv

1만이상, 5천이상, 5천미만 구간별로 몇명이 있는지~ 카운트 가능

JuJu in the World