SQL에서 서브쿼리와 GROUP BY를 효과적으로 결합하는 방법

SQL에서 서브쿼리와 GROUP BY를 결합하면 복잡한 데이터 집계나 분석을 효율적으로 수행할 수 있습니다. 이 기사에서는 서브쿼리와 GROUP BY의 기본 개념부터 구체적인 결합 방법, 그리고 응용 사례와 성능 최적화 포인트까지 자세히 설명합니다. SQL 스킬을 향상시키고자 하는 분들에게 유용한 내용입니다.

서브쿼리의 기본

서브쿼리는 SQL문 내에 포함된 다른 SQL문을 가리킵니다. 이를 통해 복잡한 쿼리를 단계적으로 작성하고, 결과를 일시적으로 저장하여 사용할 수 있습니다. 서브쿼리는 다음과 같이 사용됩니다.

서브쿼리의 구문

기본적인 서브쿼리 구문은 다음과 같습니다.

SELECT 열명 FROM 테이블명 WHERE 열명 = (SELECT 열명 FROM 테이블명 WHERE 조건);

서브쿼리의 종류

서브쿼리는 크게 스칼라 서브쿼리, 행 서브쿼리, 테이블 서브쿼리의 3가지 종류로 나뉩니다.

스칼라 서브쿼리

단일 값을 반환하는 서브쿼리입니다. 예:

SELECT name FROM employees WHERE id = (SELECT manager_id FROM departments WHERE name = 'Sales');

행 서브쿼리

한 행의 데이터를 반환하는 서브쿼리입니다. 예:

SELECT * FROM employees WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

테이블 서브쿼리

여러 행과 열을 반환하는 서브쿼리입니다. 예:

SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

다음 항목을 지정하십시오.

GROUP BY의 기본

GROUP BY 구문은 SQL에서 데이터를 그룹화하고, 각 그룹마다 집계를 수행하기 위해 사용됩니다. 이를 통해 동일한 속성을 가진 데이터를 하나로 묶어 집계 및 분석이 가능합니다.

GROUP BY의 구문

기본적인 GROUP BY 구문의 구문은 다음과 같습니다.

SELECT 열명, 집계함수(열명) FROM 테이블명 GROUP BY 열명;

자주 사용되는 집계 함수

GROUP BY 구문과 함께 자주 사용되는 집계 함수는 다음과 같습니다:

COUNT

레코드의 수를 셉니다. 예:

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

SUM

열의 합계를 계산합니다. 예:

SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;

AVG

열의 평균을 계산합니다. 예:

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

MAX

열의 최대값을 가져옵니다. 예:

SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

MIN

열의 최소값을 가져옵니다. 예:

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id;

HAVING 구문의 사용

HAVING 구문은 GROUP BY로 그룹화한 후 조건을 지정하기 위해 사용됩니다. WHERE 구문이 행에 대해 조건을 지정하는 것과 달리, HAVING 구문은 그룹에 대해 조건을 지정합니다. 예:

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000;

서브쿼리와 GROUP BY의 결합 예제

서브쿼리와 GROUP BY를 결합하면 복잡한 집계나 필터링을 수행할 수 있습니다. 아래에 구체적인 예를 제시합니다.

서브쿼리로 필터링한 데이터를 GROUP BY로 집계

다음 예제에서는 직원 테이블에서 특정 조건을 만족하는 데이터를 서브쿼리로 추출하고, 그 결과를 GROUP BY로 집계합니다.

SELECT department_id, AVG(salary) AS avg_salary 
FROM (SELECT * FROM employees WHERE hire_date >= '2020-01-01') AS recent_hires 
GROUP BY department_id;

이 쿼리에서는 먼저 서브쿼리로 2020년 1월 1일 이후에 고용된 직원을 추출한 후, 각 부서별 평균 급여를 계산합니다.

집계 결과를 서브쿼리로 활용

다음은 GROUP BY를 사용하여 집계한 결과를 외부 쿼리에서 추가로 처리하는 예입니다.

SELECT department_id, avg_salary 
FROM (SELECT department_id, AVG(salary) AS avg_salary 
      FROM employees 
      GROUP BY department_id) AS department_avg 
WHERE avg_salary > 60000;

이 쿼리에서는 먼저 각 부서별 평균 급여를 계산한 후, 외부 쿼리에서 필터링하여 평균 급여가 60,000을 초과하는 부서만 추출합니다.

서브쿼리를 사용한 중첩된 집계

더 복잡한 예로, 서브쿼리를 중첩하여 사용함으로써 더욱 세밀한 집계를 수행할 수 있습니다.

SELECT department_id, MAX(avg_salary) 
FROM (SELECT department_id, AVG(salary) AS avg_salary 
      FROM employees 
      GROUP BY department_id) AS department_avg 
GROUP BY department_id;

이 쿼리에서는 먼저 각 부서별 평균 급여를 계산하고, 그 결과를 다시 중첩된 서브쿼리에서 최대 평균 급여를 가진 부서를 추출합니다.

서브쿼리를 이용한 집계의 응용 사례

서브쿼리를 이용하여 보다 고급 집계나 분석을 수행할 수 있습니다. 아래에 몇 가지 응용 사례를 제시합니다.

서브쿼리로 랭킹 생성

다음 예에서는 직원의 급여를 기준으로 각 부서 내에서 급여 순위를 생성합니다.

SELECT employee_id, department_id, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

이 쿼리에서는 각 부서별로 직원의 급여를 내림차순으로 정렬하고, RANK() 함수를 사용하여 급여 순위를 계산합니다.

집계 결과를 활용한 비율 계산

다음 예에서는 각 부서의 직원 수가 전체 직원 수에서 차지하는 비율을 계산합니다.

SELECT department_id, 
       COUNT(*) AS dept_employee_count,
       (COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees)) AS employee_percentage
FROM employees
GROUP BY department_id;

이 쿼리에서는 서브쿼리를 사용하여 전체 직원 수를 가져오고, 이를 바탕으로 각 부서의 직원 수 비율을 계산합니다.

여러 집계를 결합한 쿼리

다음 예에서는 각 부서별 평균 급여와 해당 부서 내 최고 급여, 최저 급여를 동시에 가져옵니다.

SELECT department_id, 
       AVG(salary) AS avg_salary, 
       MAX(salary) AS max_salary, 
       MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;

이 쿼리에서는 GROUP BY 구문과 여러 집계 함수를 결합하여 각 부서의 급여와 관련된 세부 통계 정보를 한 번에 가져옵니다.

조건부 집계

특정 조건을 만족하는 데이터만을 대상으로 집계하는 예입니다.

SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
WHERE hire_date >= '2022-01-01'
GROUP BY department_id;

이 쿼리에서는 2022년 1월 1일 이후에 고용된 직원만을 대상으로 각 부서의 평균 급여를 계산합니다.

성능 최적화 포인트

서브쿼리와 GROUP BY를 효과적으로 사용하기 위해서는 성능 최적화가 중요합니다. 아래의 포인트를 숙지하여 쿼리 실행 속도를 향상시킬 수 있습니다.

인덱스 활용

인덱스는 데이터베이스 내 특정 열에 대해 생성되는 데이터 구조로, 검색이나 집계 속도를 향상시키기 위해 사용됩니다. 서브쿼리나 GROUP BY에서 자주 사용하는 열에는 인덱스를 설정하는 것이 좋습니다.

CREATE INDEX idx_employees_hire_date ON employees(hire_date);
CREATE INDEX idx_employees_department_id ON employees(department_id);

EXPLAIN 계획 확인

SQL 쿼리의 실행 계획을 확인하기 위해 EXPLAIN을 사용합니다. 이를 통해 쿼리가 어떻게 실행되는지 파악하고, 병목 현상을 식별할 수 있습니다.

EXPLAIN SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

서브쿼리 결과를 임시 테이블에 저장

서브쿼리 결과를 임시 테이블에 저장함으로써 쿼리 재실행을 피하고 전체 성능을 향상시킬 수 있습니다.

CREATE TEMPORARY TABLE temp_recent_hires AS 
SELECT * FROM employees WHERE hire_date >= '2020-01-01';

SELECT department_id, AVG(salary) FROM temp_recent_hires GROUP BY department_id;

적절한 데이터 타입 사용

데이터 타입 선택은 쿼리 성능에 큰 영향을 미칩니다. 적절한 데이터 타입을 사용하여 메모리 사용량을 줄이고, 쿼리 실행 속도를 향상시킬 수 있습니다.

중복된 서브쿼리 회피

동일한 결과를 반환하는 여러 서브쿼리가 포함된 경우, 이를 하나로 합침으로써 쿼리 효율성을 향상시킬 수 있습니다.

SELECT department_id, AVG(salary) 
FROM employees 
WHERE hire_date >= '2020-01-01'
GROUP BY department_id;

자주 발생하는 오류와 대책

서브쿼리와 GROUP BY를 사용할 때 자주 발생하는 오류와 그에 대한 대책을 설명합니다.

오류: 서브쿼리가 여러 행을 반환

서브쿼리가 단일 값을 기대하는 곳에서 여러 행을 반환할 때 발생합니다. 대책으로 서브쿼리에 LIMIT 1을 추가하거나, 적절한 집계 함수를 사용합니다.

-- 여러 행을 반환하는 서브쿼리 예
SELECT name 
FROM employees 
WHERE id = (SELECT id FROM employees WHERE department_id = 1);

-- 대책: LIMIT 1 사용
SELECT name 
FROM employees 
WHERE id = (SELECT id FROM employees WHERE department_id = 1 LIMIT 1);

오류: GROUP BY에 포함되지 않은 열을 SELECT 구문에 포함

GROUP BY 구문에 포함되지 않은 열을 SELECT 구문에 포함하면 오류가 발생합니다. 대책으로 SELECT 구문에 포함되는 열은 모두 GROUP BY 구문에도 포함시킵니다.

-- 오류가 발생하는 예
SELECT department_id, name, AVG(salary) 
FROM employees 
GROUP BY department_id;

-- 대책: name을 GROUP BY에 추가
SELECT department_id, name, AVG(salary) 
FROM employees 
GROUP BY department_id, name;

오류: 서브쿼리의 성능이 낮음

서브쿼리 실행 속도가 느린 경우, 인덱스를 추가하거나 쿼리 최적화가 필요합니다. 또한, 임시 테이블을 사용하여 쿼리를 분할하는 것도 유효합니다.

-- 서브쿼리 성능이 낮은 예
SELECT department_id, (SELECT AVG(salary) FROM employees WHERE department_id = d.id) 
FROM departments d;

-- 대책: 인덱스 추가
CREATE INDEX idx_employees_department_id ON employees(department_id);

-- 또는 임시 테이블 사용
CREATE TEMPORARY TABLE temp_avg_salaries AS 
SELECT department_id, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department_id;

SELECT d.id, t.avg_salary 
FROM departments d 
JOIN temp_avg_salaries t ON d.id = t.department_id;

오류: 메모리 부족

대규모 서브쿼리나 집계는 메모리를 많이 소모하므로, 메모리 부족 오류가 발생할 수 있습니다. 대책으로 쿼리를 분할하여 실행하거나, 데이터베이스 설정을 조정합니다.

-- 쿼리를 분할하여 실행하는 예
CREATE TEMPORARY TABLE temp_large_query AS 
SELECT * FROM large_table WHERE condition;

SELECT * FROM temp_large_query WHERE another_condition;

요약

서브쿼리와 GROUP BY를 효과적으로 결합함으로써, SQL 쿼리의 표현력과 유연성을 크게 향상시킬 수 있습니다. 기본적인 사용법부터 시작해, 응용 예제와 성능 최적화 포인트를 숙지함으로써 복잡한 데이터 집계나 분석을 효율적으로 수행할 수 있습니다. 자주 발생하는 오류에 대한 대책도 이해하고 실천함으로써, 보다 견고하고 성능이 우수한 SQL 쿼리를 작성할 수 있습니다. 이러한 기술을 활용하여 데이터베이스 운영을 한 단계 더 강화해보세요.