[SQL] 집계 함수를 쓰기 어려울 때 over()와 서브 쿼리 중 뭐를 사용해야 할까?

🎈시작

MySQL에서 world database를 사용해 쿼리를 뚱땅거리던 중 다음과 같은 쿼리를 작성해야하는 경우가 있었다.

어떤 데이터에 대한 조회를 해야할 때 각 튜플의 데이터를 확인하면서 튜플이 속한 그룹의 평균 값을 확인하고 싶은 경우가 있었는데 (예를 들자면 전체 학생을 조회할 때 학생이 속한 반의 평균 키를 함께 조회하고 싶은 경우...) 

여기서 over와 서브쿼리를 사용하며 알게된 점을 작성해보려고 한다.

 

 

🧩 서브 쿼리와 OVER()

• 본문에 들어가기 전 서브 쿼리와 OVER() 가 무엇인지 알아보자

 

서브 쿼리

• 서브 쿼리란 SQL 쿼리 내에서 다른 SQL 쿼리의 일부로 사용되는 쿼리이다.
• 즉, 하나의 쿼리 안에 다른 SQL 쿼리가 포함되는 것!

예를 들어, 도시 테이블에서 전체 인구 평균 이상의 도시를 조회하는 구문이다.

SELECT name, population
FROM city
WHERE population > (
    SELECT AVG(population)
    FROM city
);

이처럼 where 절 안에 서브쿼리를 두어 서브 쿼리에서 전체 인구의 평균을 반환하도록 한다.

 

 

이렇게 서브쿼리는 쿼리내에 다음 위치에서 사용될 수 있다.

1. WHERE 절
   • 위에서 봤던 예시처럼 where 절 내에서 메인 쿼리 결과를 필터링 하는데 사용된다.
2. FROM 절
   • 서브쿼리를 테이블처럼 사용하여 데이터를 JOIN하거나 다른 테이블과 함께 쿼리할 수 있다.
3. SELECT 절
   • 서브 쿼리의 결과를 메인 쿼리의 SELECT 절에서 사용하여 데이터를 검색하거나 표시할 수 있다.
4. HAVING 절
   • GROUP BY와 함께 사용하여 그룹화된 데이터를 필터링하는데 사용될 수 있다.

 

 

서브 쿼리의 목적

• 데이터 필터링
  • 메인 쿼리의 결과 집합을 결정하기 위해 서브쿼리를 사용하여 특정 조건을 충족하는 데이터를 선택한다.
• 데이터 검색
  • 서브쿼리를 사용하여 메인 쿼리의 결과 집합을 결정하기 위해 다른 테이블에서 데이터를 검색한다.
• 데이터 집계
  • 서브쿼리를 사용하여 메인 쿼리의 결과 집합에서 집계 함수를 사용하여 요약된 정보를 얻는다.

 

 

OVER() 함수

• SQL의 윈도우 함수와 함께 사용되는 구문이다.
  • 윈도우 함수는 데이터 집계 및 분석 작업을 수행하는데 사용되며,
  • 데이터 집합을 여러 부분( 창 또는 윈도우)으로 나누어 각 부분에 대한 계산을 수행한다.
• OVER() 구문은 윈도우 함수의 파티셔닝 및 정렬(정렬 순서)을 정의하는데 사용된다.

<윈도우 함수>() OVER (
    [PARTITION BY <파티션 열 목록>]
    [ORDER BY <정렬 열 목록>]
    [<기타 옵션>]
)

• 윈도우 함수 
  • count, sum, avg, row_number 등과 같은 윈도우 함수
• partition by
  • 데이터를 파티션으로 나누기 위한 열 목록
  • 동일한 파티션 내에서 윈도우 함수 작동
• order by
  • 데이터를 정렬하기 위한 열 목록
  • 정렬 순서
• 기타옵션
  • 필요에 따라 추가 옵션 지정

 

 

예를 들어 도시 인구의 평균과 각 도시를 비교하는 쿼리를 작성해보자

SELECT name, population,
  AVG(population) OVER () AS global_avg_population
FROM city;

• OVER()를 통해 전체 데이터를 기준으로 집계할 수 있다.

 

 

 

🧩 서브 쿼리 VS OVER() 

문제

MySQL에서 제공하는 World DB에서 country에서 국가별로 gnp와 함께 전세계 평균, 대륙별 평균을 함께 조회해라!

 

 

서브 쿼리 사용

select continent, name, gnp, 
	(
		select avg(gnp)
		from country p
	) as "전세계 평균", 
	(
		select avg(gnp)
		from country p
		where c.continent = p.continent
	) as "대륙 평균"
from country c
order by continent;

 

 

OVER() 사용

use world;
select continent, name, gnp, 
avg(gnp) over() '전세계 평균',
avg(gnp) over( partition by continent ) '대륙별 평균'
from country;

 

 

프로파일링을 통한 처리 속도 확인

프로파일링 설정 확인

SET profiling = 1; -- 프로파일링 활성화

-- 여기에 수행하려는 쿼리 작성

SHOW PROFILES; -- 실행 중인 프로파일링 프로세스 목록 확인

-- 실행 중인 프로파일링 프로세스 ID를 사용하여 세부 정보 확인
SHOW PROFILE FOR QUERY query_id;

 

처리 속도

• 2번은 서브 쿼리 사용한 쿼리 실행
• 3번은 OVER() 사용한 쿼리 실행

 

왜 OVER()를 사용했을 때 더 빠를까?

• OVER 함수는 윈도우 함수를 활용해서 한번의 쿼리 실행으로 평균을 계산한다.
• 서브쿼리의 경우 각 행에 대해 서브쿼리를 실행하기 때문에 조회된 튜플의 개수만큼 실행된다.
• OVER(partition by)를 사용한 경우도 partition에 따라 집계하여 평균을 계산하고 이를 사용하기 때문에 서브쿼리를 사용한 경우보다 빠르다

 

 

🎁 결론

• 집계에 대한 결과가 필요할 때는 최대한 OVER와 윈도움함수를 활용하자!