Subquery 참고 사이트
Subquery
Subquery
쿼리 안의 쿼리를 서브 쿼리라고 한다. 현재 공부한 것을 토대로 subquery에서 중요하게 생각해야 할 부분은
subquery 의 경우 계산된 결과 값을 반환한 something 으로 이해해야 한다는 부분이다.
Subquery from
목표로 만들고 싶은 테이블이 있다고 가정하자. 그러한 테이블이 복수일 때 각각의 테이블을 우선적으로 만든 후
subquery를 통해서 붙여준다. 아래 예시를 통해 살펴보자.
select pu.user_id, pu.point, a.avg_like 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
위 query문은 크게 보면 2개의 테이블을 붙인 것으로 이해할 수 있다. 첫 번째 테이블은 point_users 라는
테이블에서 field(user_id, point)를 가지고 온 테이블 부분, 2번째 테이블은 checkins 테이블에서
user_id 별로 묶어서 유저 아이디, 유저 아이디 별 좋아요의 평균을 나타낸 테이블이다.
inner join을 할 때에는 공통 field를 대상으로 붙여야 하므로 각각 테이블 내에 있는 user_id를 기준으로
붙여준다.
Subquery where
select user_id, point from point_users pu
where point > ( select avg(point) from point_users)
위 query를 작성한 이유는 point_users 테이블에서 전체 user의 point 평균을 구하고 해당 평균 값보다
포인트가 큰 user를 추출하기 위해 작성한 subquery이다. 단순히 평균을 먼저 구해서 해당 값을 적어주는 것
보다 Subquery를 통해 명령어를 작성해주면 동적으로 반영해주는 특징이 있다.
Subquery where2
select * from point_users pu
where point > (
select round(avg(point)) from point_users pu
inner join users u
on u.user_id = pu.user_id
where name='이**'
)
Subquery where 절을 한 번더 쓴 이유는 point가 특정 값과 비교하기 위해서 select 하는 값은 하나의 값
만을 select 해야 하기 때문에 이를 다시 강조하고자 당시 작성했던 유효한 Subquery 구문을 가지고 왔다. 기
억하자 where 절 안의 subquery를 쓸 때에는 반드시 하나의 값만을 가지고 오는 subquery를 작성해야 한다.
꼭 그런 것은 아니겠지만 여기서에 한에서는 그러하다.
where 절 안의 where 절을 작성한 이중 쿼리
select * from point_users pu
where point > (
select avg(point) from point_users pu
where user_id in (
select user_id from users where name = '이**'
)
)
나는 이해력이 좀 느린 편이다. 그래서 Subquery로 작성한 많은 명령어를 봄으로써 해당 명령어에 익숙해지는 것이
가장 좋을 것 같다. 위 sql문은 point_users 테이블에서 특정 데이터를 가지고 올 것인데 user_id 가 이씨인
데이터들의 평균 포인트 보다 높은 포인트의 열을 가지고 올 것이다.
다시 정리하자면 이씨들의 평균포인트보다 높은 데이터들을 뽑을 것이다.
Subquery
select c.checkin_id,
c.course_id,
c.user_id,
c.likes,
(
select avg(likes) from checkins c2
where c2.course_id = c.course_id
) as course_avg
from checkins c
select 절 안의 subquery를 작성할 때에는 유의해야되는 것이 있다. 그것은 바로 테이블과 테이블의 관계를 이해하는
방법인데 설명하자면 이렇다. 바깥에 있는 테이블 즉 테이블 c에서 차례대로 checkin_id를 가져오고자 할 것이다.
checkin_id 뒤로 나열된 column 값 같은 경우 checkin 테이블을 살펴보았을 때 checkin_id 에 붙여서 나오는
column 값으로 이해하면 된다. 그 중 column 하나 -> course_avg; 강의별 평균 좋아요 항목을 신설해서 결과물
을 구성 할 것이다.
데이터를 가지고 올 때 중요한 것은 데이터를 하나씩 차례대로 가지고 온다는 점이다. 그런데 그 안에 테이블이 있다고 했
을 때 (c == c2 == checkins) 외부 테이블(c2)에서 가져오려는 데이터와의 접합점을 만들어 주어야 한다는 점이다.
이에 대한 해당 부분이 where c.course_id = c2.course_id 부분이다.
여기서 또 한가지 중요한 점은 c.course_id와 일치하는 깂을 c2 table에서 여러개를 가지고 올 수도 있는데 여기서
avg(likes)와 같이 강의 아이디 별로 하나의 값을 가질 수 있게 지정해 주어야한다. 아니면 select가 사실상 한번의
select로 매우 많은 데이터를 가지고 올 수 있기 때문에 Subquery returns more than 1 row 와 같은 error
를 발생시킬 수 있다.
예를 들어 likes의 값을 가지고 온다고 했을 때 course_id 의 종류는 2개이지만 checkin_id 별로 너무나 다양한
likes 의 값을 가지기 때문이다. 따라서 강좌 별로 딱 하나의 값을 지정해줄 수 있는 무엇이어야만 select 안의 셀
렉트를 쓸 수 있는 것이다.
Subquery 예시
select c.checkin_id,
c2.title,
c.user_id,
c.likes,
(
select round(avg(likes), 1) from checkins c2
where c.course_id = c2.course_id
) as course_avg
from checkins c
inner join courses c2
on c.course_id = c2.course_id
Subquery 예시2
select o.title,
count(distinct(c.user_id)) as cnt_checkins,
cnt_total,
count(distinct(c.user_id))/cnt_total
from checkins c
inner join (
select course_id, course_title as title, count(*) as cnt_total
from orders
group by course_id
) o on c.course_id = o.course_id
group by c.course_id
order by cnt_total desc
Subquery 예시2!
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
Subquery 예시2와 Subquery 예시3이 서로 같은 결과 값을 반환한다. 예시2는 필자가 작성한 query라서
우선적으로 예시3에 대해서 먼저 설명하고자 한다.
구조를 보면 select from () a inner join () b inner join c 라는 큰 틀을 가지고 있다. 여기서
전에 보지 못한 작성법을 볼 수 있는데 그것은 subquery 안에 원하는 내용을 적고 해당 테이블에 대한 별칭을
줄 수 있다는 것이다. 지정한 테이블에 대해서 각각 a, b, c 라는 별칭을 준 것을 확인할 수 있다.
이 부분이 이번에 새로 학습하게 된 부분이다. 따라서 각각 지정한 테이블을 inner join을 통해서 엮고 별칭을
준 테이블로 부터 원하는 정보를 select해서 결과물을 표시한다는 것을 알 수 있는 것이다.
하나의 테이블에 여러정보를 넣지 않는 이유는 하나의 테이블은 특정 한가지 데이터와 관련해서 저장하는 특성이 있
기 때문이다. 그것에 기반해서 위와 같은 복합 명령어를 작성해서 원하는 결과값을 추출해 내야한다.
Subquery 예시2!!
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 query문을 작성할 수 있다.
