SQL 조인 테이블과 GROUP BY를 사용하여 복잡한 데이터를 집계하는 방법

SQL을 사용한 데이터베이스 작업에서 여러 테이블에서 관련 데이터를 검색하고 집계하는 것은 일반적입니다. 특히 대규모 데이터 집합에 대한 복잡한 분석을 위해서는 조인 테이블과 GROUP BY를 활용하는 것이 필수적입니다. 이 기사에서는 기본 개념부터 실용적인 쿼리 작성 및 최적화 기법까지 이러한 SQL 기능을 사용한 데이터 집계 방법을 자세히 설명합니다.

조인 테이블의 기본

조인 테이블은 여러 테이블을 결합하여 관련 데이터를 검색하는 데 사용되는 SQL 기능입니다. 아래는 주요 조인의 유형과 사용 방법입니다.

INNER JOIN

INNER JOIN은 두 테이블 모두에 공통적으로 존재하는 데이터만 반환합니다. 주로 일치하는 레코드를 얻는 데 사용됩니다.

SELECT A.column1, B.column2
FROM TableA A
INNER JOIN TableB B ON A.id = B.id;

LEFT JOIN

LEFT JOIN은 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 일치하는 데이터를 반환합니다. 오른쪽에 일치하는 데이터가 없으면 NULL이 반환됩니다.

SELECT A.column1, B.column2
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id;

RIGHT JOIN

RIGHT JOIN은 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 일치하는 데이터를 반환합니다. 왼쪽에 일치하는 데이터가 없으면 NULL이 반환됩니다.

SELECT A.column1, B.column2
FROM TableA A
RIGHT JOIN TableB B ON A.id = B.id;

FULL JOIN

FULL JOIN은 두 테이블의 모든 데이터를 반환하고 일치하지 않는 경우 NULL을 채웁니다. 두 테이블의 모든 데이터를 포함하려는 경우 사용됩니다.

SELECT A.column1, B.column2<br>FROM TableA A<br>FULL JOIN TableB B ON A.id = B.id;

GROUP BY의 기본

GROUP BY는 지정된 열을 기준으로 데이터를 그룹화하고 각 그룹에 대해 집계 함수를 사용하여 결과를 반환하는 SQL 기능입니다. 주로 특정 카테고리별로 데이터를 집계하려는 경우 사용됩니다.

GROUP BY의 기본 구문

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

SELECT column, AGGREGATE_FUNCTION(column)
FROM Table
GROUP BY column;

여기서 AGGREGATE_FUNCTION은 SUM, AVG, COUNT, MAX, MIN 등이 될 수 있습니다.

예제

예를 들어, 각 부서의 평균 급여를 찾는 쿼리는 다음과 같습니다.

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

이 쿼리는 직원 테이블에서 각 부서의 평균 급여를 계산합니다.

여러 열로 그룹화

데이터는 여러 열로도 그룹화할 수 있습니다.

SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;

이 쿼리는 부서와 직급별 직원 수를 집계합니다.

복잡한 데이터 집계 예제

조인 테이블과 GROUP BY를 결합하면 여러 테이블의 데이터를 집계하여 더 복잡한 분석을 수행할 수 있습니다. 여기에는 직원과 부서 관련 데이터를 집계하는 구체적인 예제가 있습니다.

여러 테이블에서 데이터 집계

예를 들어, 각 부서의 직원 수와 평균 급여를 집계하려면 다음 쿼리를 사용할 수 있습니다.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

이 쿼리는 departments 테이블과 employees 테이블을 INNER JOIN을 사용하여 결합하고 각 부서별로 직원 수와 평균 급여를 집계합니다.

여러 집계 함수 사용

또한 여러 집계 함수를 결합하여 다양한 통계 정보를 얻을 수 있습니다.

SELECT d.department_name, 
       COUNT(e.employee_id) AS num_employees, 
       AVG(e.salary) AS avg_salary, 
       MAX(e.salary) AS max_salary, 
       MIN(e.salary) AS min_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

이 쿼리는 각 부서별로 직원 수, 평균 급여, 최대 급여 및 최소 급여를 한 번에 집계합니다.

조건부 집계

집계를 수행하기 전에 조건으로 데이터를 필터링하는 것도 가능합니다.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
WHERE e.salary > 50000
GROUP BY d.department_name;

이 쿼리는 급여가 50,000을 초과하는 직원만을 고려하여 각 부서별로 직원 수와 평균 급여를 집계합니다.

보시다시피, 조인 테이블과 GROUP BY를 결합하면 여러 테이블에서 데이터를 검색하고 다양한 관점에서 데이터를 분석할 수 있습니다.

실용적인 쿼리 작성

여기에서는 실제 비즈니스 시나리오를 기반으로 복잡한 쿼리를 작성하는 방법을 소개합니다. 예를 들어, 판매 데이터베이스에서 각 상점의 월별 판매량을 집계하는 경우를 고려해 보겠습니다.

시나리오: 각 상점의 월별 판매량 집계

이 시나리오에서 테이블은 다음 데이터를 포함합니다:

• stores 테이블: 각 상점에 대한 정보를 포함
• orders 테이블: 주문 정보를 포함

먼저 각 상점의 월별 판매량을 집계하는 쿼리를 작성합니다.

SELECT s.store_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_name, month
ORDER BY s.store_name, month;

이 쿼리의 주요 포인트는 다음과 같습니다:

• INNER JOIN을 사용하여 stores 테이블과 orders 테이블을 결합
• DATE_FORMAT 함수를 사용하여 order_date를 월 단위로 형식화
• SUM 함수를 사용하여 각 월의 총 판매량을 계산
• GROUP BY를 사용하여 데이터를 상점 이름과 월별로 그룹화
• ORDER BY를 사용하여 결과를 상점 이름과 월별로 정렬

시나리오: 제품 카테고리별 월별 판매량 집계

다음으로, 제품 카테고리별로 월별 판매량을 집계하는 시나리오를 고려해 보겠습니다. 테이블은 다음을 포함합니다:

• products 테이블: 각 제품에 대한 정보를 포함
• categories 테이블: 각 제품의 카테고리 정보를 포함
• order_items 테이블: 주문의 세부 정보를 포함

쿼리는 다음과 같습니다:

SELECT c.category_name, 
       DATE_FORMAT(o.order_date, '%Y-%m') AS month, 
       SUM(oi.quantity * p.price) AS total_sales
FROM categories c
INNER JOIN products p ON c.category_id = p.category_id
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
GROUP BY c.category_name, month
ORDER BY c.category_name, month;

이 쿼리의 주요 포인트는 다음과 같습니다:

• 여러 INNER JOIN을 사용하여 categories, products, orders 및 order items 테이블을 결합
• quantity * price를 사용하여 각 제품의 판매량을 계산하고 SUM 함수를 사용하여 집계
• GROUP BY를 사용하여 데이터를 카테고리 이름과 월별로 그룹화
• ORDER BY를 사용하여 결과를 카테고리 이름과 월별로 정렬

이 예제에서 알 수 있듯이, 실제 비즈니스 시나리오를 기반으로 쿼리를 작성하려면 테이블 간의 관계를 이해하고 적절한 조인 및 집계 함수를 결합해야 합니다.

최적화 기법

복잡한 쿼리의 성능을 향상시키기 위해 적절한 최적화 기법을 사용하는 것이 중요합니다. 쿼리 실행 속도를 향상시키기 위한 몇 가지 방법은 다음과 같습니다.

인덱스 사용

적절한 인덱스를 사용하면 데이터베이스 검색 속도를 크게 향상시킬 수 있습니다. 조인 및 검색 조건에 사용되는 열에 인덱스를 생성하십시오.

CREATE INDEX idx_department_id ON employees(department_id);
CREATE INDEX idx_order_date ON orders(order_date);

이 예제에서는 department_id 및 order_date 열에 인덱스를 생성합니다. 이를 통해 이러한 인덱스를 사용한 조인 및 검색 조건이 빨라집니다.

서브쿼리 활용

서브쿼리를 사용하여 데이터를 사전 처리하면 메인 쿼리의 효율성을 높일 수 있습니다. 이는 특히 대량의 데이터를 처리할 때 효과적입니다.

SELECT department_name, num_employees, avg_salary
FROM (
  SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
  FROM departments d
  INNER JOIN employees e ON d.department_id = e.department_id
  GROUP BY d.department_name
) sub;

이 예제에서는 서브쿼리가 각 부서별로 직원 수와 평균 급여를 계산하고, 메인 쿼리는 이러한 결과를 활용합니다.

조인 순서 최적화

테이블 조인의 순서를 최적화하면 쿼리 성능을 향상시킬 수 있습니다. 작은 테이블부터 조인하는 것이 효과적입니다.

EXPLAIN SELECT s.store_name, DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(o.amount) AS total_sales
FROM stores s
INNER JOIN orders o ON s.store_id = o.store_id
GROUP BY s.store_name, month
ORDER BY s.store_name, month;

EXPLAIN을 사용하여 쿼리 계획을 확인하고 데이터베이스 엔진이 최적의 조인 순서를 사용하도록 하십시오.

적절한 집계 함수 사용

적절한 집계 함수를 사용하면 쿼리 성능을 향상시킬 수 있습니다. 예를 들어, 불필요한 집계를 피하기 위해 최소한의 필요한 집계 함수만 사용하십시오.

SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

이 예제에서는 필요한 정보를 얻기 위해 COUNT 및 AVG 집계 함수만 사용됩니다.

뷰(View) 사용

뷰를 사용하면 복잡한 쿼리를 단순화하고 재사용 가능한 쿼리를 생성할 수 있습니다. 뷰를 정의하면 쿼리 가독성이 향상됩니다.

CREATE VIEW department_summary AS
SELECT d.department_name, COUNT(e.employee_id) AS num_employees, AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

이 예제에서는 각 부서별로 직원 수와 평균 급여를 계산하는 뷰를 생성합니다. 뷰를 사용하면 나중에 집계된 결과를 쉽게 참조할 수 있습니다.

결론

조인 테이블과 GROUP BY를 사용한 데이터 집계는 복잡한 데이터 분석에 매우 유용합니다. 적절한 조인의 유형과 GROUP BY 사용법을 이해하고 쿼리 최적화 기법을 활용하면 효율적이고 효과적인 데이터 집계가 가능합니다. 이러한 기술을 사용하여 데이터베이스 성능을 최대한 활용하십시오.