[SQL] OVER()

# OVER()

• OVER 함수는 윈도우 함수(Window Function)와 함께 사용되어, 집계 또는 분석 작업을 수행할 때 특정 범위(윈도우) 내에서 연산을 처리할 수 있도록 도와준다.
• OVER는 데이터의 그룹화 없이 각 행에 대해 계산을 수행하고, 지정된 범위(윈도우)에 대해서만 집계 함수가 적용되도록 한다.
• 주로 PARTITION BY, ORDER BY, 그리고 ROWS BETWEEN과 함께 사용된다.

 

# 기본 구문

SELECT column1, column2, ..., 
       aggregate_function(column) OVER (PARTITION BY column ORDER BY column ROWS BETWEEN ... AND ...)
FROM table;

 

 

# 주요 구성 요소

• PARTITION BY: 데이터를 그룹화하여 그룹 내에서 연산을 수행할 수 있게 한다.
• ORDER BY: 데이터를 정렬하여 연산 순서를 결정한다.
• ROWS BETWEEN: 특정 범위(윈도우)를 설정하여 집계 범위를 정의한다.

 

# 주요 윈도우 함수

• ROW_NUMBER(): 순차적인 번호를 매긴다.
• RANK(): 순위를 매기며, 동일 순위가 있을 경우 동일 순위를 부여하고, 그 다음 순위는 건너뛴다.
• DENSE_RANK(): 순위를 매기며, 동일 순위가 있어도 순위를 건너뛰지 않고 1씩 증가시킨다.
• LEAD(): 현재 행 이후의 값을 가져온다.
• LAG(): 현재 행 이전의 값을 가져온다.
• SUM(), AVG(), MIN(), MAX(): 집계 함수

# OVER 함수에서 사용할 수 있는 주요 윈도우 범위

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 첫 번째 행부터 현재 행까지의 값을 사용해 계산한다.
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  • 현재 행부터 마지막 행까지의 값을 사용해 계산한다.
• ROWS BETWEEN x PRECEDING AND y FOLLOWING
  • 현재 행을 기준으로 이전 x개의 행과 이후 y개의 행까지 범위를 설정한다.

 

# 예시1: 이동 평균 구하기

SELECT
    date,
    value,
    AVG(value) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    your_table;

 

• ROWS BETWEEN 4 PRECEDING AND CURRENT ROW: 현재 행을 포함하여 이전 4개 행을 포함한 범위에서 이동 평균을 계산한다.
• ORDER BY date: 날짜 순으로 데이터를 정렬한다.

 

 

# 예시2: 부서별 평균 급여 구하

SELECT
    department,
    employee,
    salary,
    AVG(salary) OVER (PARTITION BY department ORDER BY salary) AS avg_salary_per_department
FROM
    employees;

 

• PARTITION BY department: 부서별로 데이터를 그룹화하여 각 부서 내에서 평균을 계산한다.
• ORDER BY salary: 급여 순서대로 정렬하여 평균을 계산한다.

 

 

# 예시3: 누적합 구하기

SELECT
    date,
    value,
    SUM(value) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM
    your_table;

 

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 첫 번째 행부터 현재 행까지의 모든 행을 포함하여 누적합을 계산한다.
• ORDER BY date: 날짜순으로 데이터를 정렬하여 누적합을 계산한다.

 

# 예시4: 순위 계산하기

SELECT
    employee,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;

 

• RANK(): 급여 순으로 순위를 매기고, 동일 급여를 가진 사람들은 동일 순위를 받으며 그 다음 순위는 건너뛴다.
• ORDER BY salary DESC: 급여를 내림차순으로 정렬하여 순위를 매긴다.

 

 

# 예시5: 리더/후속 함수 사용하기

## LAG():

SELECT
    employee,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary
FROM
    employees;

 

• LAG(salary, 1): 현재 행을 기준으로 1개 이전 행의 salary 값을 가져온다.
• ORDER BY salary: 급여 순서대로 데이터를 정렬한다.

## LEAD():

SELECT
    employee,
    salary,
    LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM
    employees;

• LEAD(salary, 1): 현재 행을 기준으로 1개 이후 행의 salary 값을 가져온다.

 

# 예시6: 주문 데이터에서 각 주문의 누적 금액 구하기

SELECT
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM
    orders;

 

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 첫 번째 주문부터 현재 주문까지의 금액을 모두 합산하여 누적 금액을 계산한다.
• ORDER BY order_date: 주문 날짜 순으로 데이터를 정렬한다.