오라클 OVER (PARTITION BY..)

오라클 OVER (PARTITION BY ...)

오라클에서 OVER (PARTITION BY ...)는 윈도우 함수(Window Function)와 함께 사용되는 핵심 구문입니다. 일반적인 GROUP BY 절과 달리, PARTITION BY는 데이터를 그룹화하지만 원래의 행들을 유지하면서 각 그룹 내에서 집계 또는 순위 계산을 수행할 수 있게 해줍니다.

---

GROUP BY vs PARTITION BY의 핵심 차이점

• GROUP BY

  데이터를 그룹화하고 각 그룹에 대해 하나의 요약된 행을 반환합니다. 그룹 내의 개별 행 데이터는 사라지고 집계된 결과만 남습니다.

  SELECT category, SUM(sales_amount)
  
  FROM sales_data
  
  GROUP BY category;
  
  -- 결과: 각 카테고리별 총 매출액 (각 카테고리당 1행)

• PARTITION BY (with OVER)

  데이터를 논리적인 파티션(그룹)으로 나누지만, 모든 원본 행을 유지합니다. 그리고 각 행에 대해 해당 행이 속한 파티션 내에서 계산된 값을 함께 보여줍니다.

  SELECT product, category, sales_amount,
  
         SUM(sales_amount) OVER (PARTITION BY category) AS total_sales_per_category
  
  FROM sales_data;
  
  -- 결과: 각 상품별 정보와 함께 해당 상품이 속한 카테고리의 총 매출액이 모든 행에 표시됩니다.

---

OVER (PARTITION BY ...)의 작동 방식

1. 데이터 분할 (PARTITION BY): PARTITION BY 절에 지정된 컬럼들의 값을 기준으로 전체 결과 집합을 논리적인 파티션으로 나눕니다. 예를 들어, PARTITION BY category라고 하면 'Electronics', 'Clothing' 등 category 컬럼의 값에 따라 데이터가 분리됩니다.
2. 윈도우 함수 적용: OVER 절 앞에 오는 윈도우 함수(예: SUM(), AVG(), COUNT(), ROW_NUMBER(), RANK(), LEAD(), LAG() 등)는 이 파티션 내에서 작동합니다.
3. 결과 반환: 각 원본 행에 대해, 해당 행이 속한 파티션 내에서 계산된 결과가 새로운 컬럼으로 추가되어 반환됩니다.

---

주요 활용 사례

• 그룹별 집계 값 확인 (전체 행 유지)

  각 직원의 급여 정보와 함께 해당 직원이 속한 부서의 평균 급여를 함께 보고 싶을 때 유용합니다.

  SELECT emp_name, department, salary,
  
         AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
  
  FROM employees;

• 그룹 내 순위 부여

  각 부서 내에서 직원의 급여 순위를 매기거나, 특정 카테고리 내에서 상품의 판매량 순위를 매길 때 사용됩니다. ROW_NUMBER(), RANK(), DENSE_RANK()와 함께 자주 사용됩니다.

  SELECT emp_name, department, salary,
  
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank_in_dept
  
  FROM employees;

  • ROW_NUMBER(): 파티션 내에서 고유한 일련번호를 부여합니다.
  • RANK(): 같은 값에는 같은 순위를 부여하고, 다음 순위는 건너뜁니다 (예: 1, 2, 2, 4).
  • DENSE_RANK(): 같은 값에는 같은 순위를 부여하고, 다음 순위는 건너뛰지 않습니다 (예: 1, 2, 2, 3).

• 누적 합계 또는 이동 평균

  시간이나 특정 기준에 따라 누적 합계 또는 이동 평균을 계산할 때 사용됩니다. ORDER BY와 ROWS BETWEEN ... AND ... 또는 RANGE BETWEEN ... AND ...와 함께 사용될 수 있습니다.

  SELECT order_date, customer_id, amount,
  
         SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_customer_sales
  
  FROM orders;

• 이전/다음 행의 값 참조

  LAG() (이전 행) 또는 LEAD() (다음 행) 함수를 사용하여 파티션 내에서 이전 또는 다음 행의 값을 가져올 수 있습니다.

  SELECT order_date, product, price,
  
         LAG(price, 1, 0) OVER (PARTITION BY product ORDER BY order_date) AS previous_price
  
  FROM daily_prices;

---

ORDER BY 절과의 관계

OVER (PARTITION BY ... ORDER BY ...) 에서 ORDER BY 절은 각 파티션 내에서 윈도우 함수가 적용될 순서를 정의합니다. ROW_NUMBER(), RANK(), LAG(), LEAD()와 같은 함수들은 ORDER BY 절이 필수적입니다. SUM(), AVG()와 같은 집계 함수는 ORDER BY 없이도 사용할 수 있지만, ORDER BY를 추가하면 누적 합계나 이동 평균과 같은 특정 순서에 따른 계산을 수행할 수 있습니다.

---

요약

OVER (PARTITION BY ...)는 Oracle SQL에서 매우 강력한 분석 기능으로, GROUP BY의 한계를 극복하고 원본 데이터를 유지하면서 복잡한 그룹 내 계산을 수행할 수 있도록 해줍니다. 이를 통해 쿼리를 더 간결하고 효율적으로 작성할 수 있습니다.