고급 SQL 쿼리 가이드: 서브쿼리, 조인, 집계 함수 및 윈도우 함수

1. 서브쿼리: 쿼리 안의 쿼리

서브쿼리는 하나의 SQL 쿼리 내에서 다른 SQL 쿼리를 포함하는 구조로, 복잡한 데이터 조회를 가능하게 합니다. 이는 주로 데이터를 필터링하거나 보조적인 계산을 수행하는 데 사용됩니다.

서브쿼리의 주요 유형

1. 단일 행 서브쿼리: 결과가 단일 값으로 반환됩니다.

   SELECT name
   FROM employees
   WHERE salary > (SELECT AVG(salary) FROM employees);

   • 평균 급여보다 높은 급여를 받는 직원의 이름을 조회합니다.

2. 다중 행 서브쿼리: 여러 행이 반환됩니다.

   SELECT name
   FROM employees
   WHERE department_id IN (SELECT id FROM departments WHERE location = 'Seoul');

   • 서울에 위치한 부서에 속한 직원들의 이름을 조회합니다.

3. 상관 서브쿼리: 외부 쿼리에 의존하여 실행됩니다.

   SELECT e1.name
   FROM employees e1
   WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

   • 각 부서별 평균 급여보다 높은 급여를 받는 직원들을 조회합니다.

활용 사례

• 고객 데이터 분석:

  SELECT customer_id, total_spent
  FROM (
      SELECT customer_id, SUM(amount) AS total_spent
      FROM sales
      GROUP BY customer_id
  ) AS spending_summary
  WHERE total_spent > 1000;

  • 특정 금액 이상 지출한 고객 목록을 추출합니다.

• 복잡한 보고서 작성: 여러 데이터 테이블을 결합하여 특정 조건을 만족하는 데이터를 생성합니다.

---

2. 조인: 테이블 간의 데이터 결합

조인은 두 개 이상의 테이블에서 데이터를 결합하여 하나의 결과 집합을 만드는 방법입니다. 이는 관계형 데이터베이스에서 필수적인 작업으로, 데이터를 통합하고 분석하는 데 핵심 역할을 합니다.

조인의 주요 유형

1. INNER JOIN: 두 테이블에서 일치하는 행만 반환합니다.

   SELECT *
   FROM customers c
   INNER JOIN orders o ON c.customer_id = o.customer_id;

   • 고객과 그들의 주문 데이터를 결합합니다.

2. LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환합니다.

   SELECT c.name, o.order_id
   FROM customers c
   LEFT JOIN orders o ON c.customer_id = o.customer_id;

   • 모든 고객과 해당 주문 번호를 반환하며, 주문이 없는 경우 NULL로 표시됩니다.

3. FULL OUTER JOIN: 양쪽 테이블의 모든 데이터를 포함합니다.

   SELECT c.name, o.order_id
   FROM customers c
   FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

   • 누락된 데이터를 포함하여 전체 데이터를 결합합니다.

4. CROSS JOIN: 두 테이블 간 가능한 모든 조합을 생성합니다.

   SELECT *
   FROM products p
   CROSS JOIN categories c;

   • 모든 제품과 카테고리의 조합을 반환합니다.

활용 사례

• 마케팅 분석: 고객 데이터와 구매 기록을 결합하여 행동 패턴을 분석합니다.
• 재무 보고서 생성: 직원과 급여 정보를 결합하여 각 직원의 급여 명세서를 생성합니다.
• 데이터 통합: 여러 데이터 소스를 결합하여 하나의 데이터 세트를 만듭니다.

---

3. 집계 함수: 데이터 요약 및 통계

집계 함수는 데이터를 요약하고 통계를 계산하는 데 사용됩니다. 이를 통해 대규모 데이터에서 핵심 정보를 추출할 수 있습니다.

주요 집계 함수

• SUM: 값의 합계를 계산합니다.
• AVG: 값의 평균을 계산합니다.
• MAX/MIN: 최대값과 최소값을 찾습니다.
• COUNT: 행의 개수를 계산합니다.

예제

1. 기본 집계:

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

   • 부서별 직원 수를 계산합니다.

2. 복합 집계:

   SELECT category, SUM(sales) AS total_sales, AVG(price) AS avg_price
   FROM sales
   GROUP BY category;

   • 각 카테고리별 총 매출액과 평균 가격을 계산합니다.

3. HAVING 절 활용:

   SELECT department, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department
   HAVING AVG(salary) > 5000;

   • 평균 급여가 5000 이상인 부서를 필터링합니다.

활용 사례

• 매출 분석: 특정 기간 동안의 매출 요약.
• 운영 효율화: 부서별 성과를 분석하여 개선 방안을 도출합니다.
• 고객 세분화: 고객 데이터를 기반으로 세그먼트를 정의합니다.

---

4. 윈도우 함수: 세부 분석을 위한 도구

윈도우 함수는 데이터를 그룹화하지 않고도 각 행에 대한 추가적인 통계를 계산할 수 있는 강력한 도구입니다. 이는 데이터의 흐름과 비교를 분석하는 데 유용합니다.

윈도우 함수 구조

함수명() OVER (PARTITION BY column1 ORDER BY column2)

• PARTITION BY: 데이터를 나누는 기준 열입니다.
• ORDER BY: 데이터를 정렬하는 기준 열입니다.

예제

1. 부서별 평균 급여:

   SELECT id, name, department, salary,
          AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
   FROM employees;

   • 부서별 평균 급여를 각 행에 표시합니다.

2. 누적 합계 계산:

   SELECT id, name, salary,
          SUM(salary) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
   FROM employees;

   • 직원 ID 순서대로 누적 급여를 계산합니다.

3. 순위 계산:

   SELECT id, name, salary,
          RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
   FROM employees;

   • 부서별 급여 순위를 계산합니다.

활용 사례

• 트렌드 분석: 누적 매출을 통해 시간에 따른 변화를 분석합니다.
• 성과 평가: 직원 개개인의 성과를 부서 평균과 비교합니다.
• 순위 변화 추적: 특정 이벤트에 따른 순위 변화를 분석합니다.

---

결론

고급 SQL 쿼리는 데이터를 효율적으로 관리하고 분석하는 데 필수적인 도구입니다. 서브쿼리, 조인, 집계 함수 및 윈도우 함수는 복잡한 데이터를 처리하고 비즈니스 인사이트를 도출하는 데 강력한 도구가 됩니다. 다양한 사례와 코드를 실습하여 SQL 스킬을 강화하고 데이터 중심의 의사결정을 뒷받침하는 데 활용해 보세요.