(해당 포스트는 Inflearn - [백문이불여일타] 데이터 분석을 위한 고급 SQL 강의록입니다.)
서브쿼리
sub + query
쿼리 안에 들어가는 또 다른 쿼리
select절, from절, where절에서 사용 가능
(select절 서브쿼리는 누적합을 구하기 위한 경우가 많으며 윈도우 함수 부분에서 자세하게 설명 예정)
from절 서브쿼리
mysql 작업을 할 때에 머릿속으로 그려놓은 테이블을 생각하며 '이렇게 할 수는 없을까?' 라고 했을 때 그 '이렇게'에 해당하는 기능이 실제로 존재하면 좋겠다고 생각했다. 알고보니 그게 바로 from절 서브쿼리였다! (다시 한 번 느끼지만 안되는 개발은(==mysql쿼리는) 없다.)
crimes라는 테이블이 다음과 같을 때,
2주차의 일평균 범죄 발생건수를 구해야한다면 아래와 같은 순서로 쿼리문을 작성해야 한다.
1) 2주차에 해당하는 결과들을 일자별로 한 번 group by를 이용하여 쿼리한 후
2) 그 result set을 가지고 다시 한 번 일자별 평균을 쿼리 (역시 order by 이용 필요)
select daily_stats.week, avg(daily_stats.incidents_daily) # ❗️
from (select week, date, count(incident_id) AS incidents_daily
from crimes
group by week, date) as daily_stats # 1)
group by daily_stats.week # 2)이 때에 각별히 주의할 점이 있다. (❗️ 부분)
지금은 crimes 테이블에서 1/5부터 1/11까지 하루도 비는 날이 없이 범죄가 발생했다. 하지만 만일 1/6 하루만 범죄가 발생하지 않았다고 쳐보자. 그럼 이 때 avg() 함수를 사용한 결과는 원하는 결과가 나오지 않을 수 있다! 실제 현업에서 많이 등장하는 실수이기 때문에 이런 부분을 잘 캐치하여 평균값을 계산해야 한다.
즉 평균 계산이 들어가는 쿼리에서는 나누어지는 수가 정확한 값이 들어갔는지 각별히 주의하는 습관이 필요하다.
where절 서브쿼리
crimes테이블에서 가장 최신의 범죄에 대한 정보를 모두 보는 쿼리
select *
from crimes
where date = (select max(Date)
from crimes)위와 같은 서브쿼리를 작성할 때에는 주의할 점이 있다.
where절에서 date 뒤에 = 기호를 사용했으므로 '~와 같을 때'라는 의미가 된다. 따라서 반드시 = 뒤에 서브쿼리로 작성한 쿼리문은 결과물이 한 개만 나오도록 작성해야 한다. 지금같은 경우 서브쿼리에 min() 집계함수를 사용했으니 하나의 값이 나온다.
하지만 결과물이 한 개가 아닌 경우의 서브쿼리가 필요할 때는 어떻게 하느냐 하면 아래와 같다.
select *
from crimes
where date in (select distinct date # 하루에 범죄가 여러개일 수 있으니 distinct 사용을 잊지 말자
from crimes
order by date
desc limit 5)이 경우는 가장 최신의 5개의 범죄 기록을 보고 싶을 때 사용할 수 있다. 위 처럼 where절에서 서브쿼리 전에 =가 아닌 in을 사용하면 결과 값이 하나가 아닌 경우라도 서브쿼리에 이용할 수 있다.
