분석 함수(Analytic Functions)

분석 함수(Analytic Functions)는 SQL에서 사용되며, OVER 절과 함께 사용됩니다. OVER 절은 분석 함수가 적용되는 데이터의 범위를 지정하는 데 사용됩니다. 분석 함수는 SELECT 문에서 사용되며, 결과 집합의 각 행에 대해 계산된 값을 반환합니다.

기본 구문

분석 함수의 기본 구문은 다음과 같습니다.

<analytic_function> OVER (
    [PARTITION BY partition_expression, ... ]
    [ORDER BY order_expression [ASC | DESC], ... ]
    [{ROWS | RANGE} window_frame_specification]
)

 

위의 구문에서 각 부분의 역할은 다음과 같습니다:

<분석 함수>: COUNT, SUM, AVG, MAX, MIN 등과 같은 분석 함수를 지정합니다.
PARTITION BY partition_expression, ...: 이 부분은 분석 함수를 계산할 때 파티션을 나누는데 사용됩니다. 즉, 지정된 열의 값에 따라 데이터가 분할되고, 각 파티션 내에서 개별 행에 대해 분석 함수가 계산됩니다.
ORDER BY order_expression [ASC | DESC], ...: 이 부분은 파티션 내에서 행의 순서를 지정하는데 사용됩니다. 지정된 열의 값에 따라 행이 정렬되며, 정렬 순서는 ASC (오름차순) 또는 DESC (내림차순)로 지정할 수 있습니다.

{ROWS | RANGE} window_frame_specification: 이 부분은 윈도우 프레임(분석 함수의 계산 범위)을 지정하는데 사용됩니다. 윈도우 프레임은 현재 행을 중심으로 계산 범위를 제한하는데 사용됩니다.

ROWS: 특정 개수의 행으로 윈도우 프레임을 지정합니다.

• ROWS BETWEEN <시작 오프셋> AND <끝 오프셋>: 시작 오프셋과 끝 오프셋으로 지정된 범위의 행들을 포함합니다. 예를 들어, 1 PRECEDING부터 1 FOLLOWING은 현재 행을 포함하여 앞뒤로 1개의 행을 선택합니다.
• ROWS <n> PRECEDING: 현재 행에서 앞으로 n개의 행을 선택합니다.
• ROWS <n> FOLLOWING: 현재 행에서 뒤로 n개의 행을 선택합니다.
• ROWS UNBOUNDED PRECEDING: 현재 행을 포함하여 앞으로 모든 행을 선택합니다.
• ROWS UNBOUNDED FOLLOWING: 현재 행을 포함하여 뒤로 모든 행을 선택합니다.
• ROWS CURRENT ROW: 현재 행만 선택합니다.

RANGE: 특정 값의 범위로 윈도우 프레임을 지정합니다. (예: 날짜 기준으로 7일 범위)

일반적으로 RANGE는 숫자 데이터가 아닌 연속적인 순서를 가진 데이터에 대해 사용됩니다. 가장 일반적으로 RANGE는 날짜, 시간 또는 문자열과 같은 데이터 유형에서 사용됩니다.

기본적인 RANGE 문법은 다음과 같습니다.

RANGE BETWEEN <range_start> AND <range_end>

<range_start>: 윈도우 프레임의 시작을 지정합니다. 이는 현재 행을 중심으로 상대적인 위치로 표현할 수 있습니다. 예를 들어, 2 PRECEDING은 현재 행으로부터 2개 행 앞의 행을 시작으로 지정합니다.

<range_end>: 윈도우 프레임의 끝을 지정합니다. 마찬가지로 상대적인 위치를 사용하여 표현할 수 있습니다.

일반적으로 RANGE는 BETWEEN <range_start> AND <range_end> 형태로 사용되며, 특정 범위 내의 데이터를 포함하여 분석 함수를 계산하는 데 사용됩니다.

예를 들어, 다음은 RANGE를 사용하여 날짜 기준으로 3일 범위 내의 데이터를 포함하여 SUM 분석 함수를 계산하는 예제입니다.

SELECT
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM
    orders;

 

위의 쿼리에서 RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW는 현재 행의 주문일로부터 2일 전까지의 데이터를 포함하여 누적 합계를 계산합니다.

RANGE BETWEEN과 ROWS BETWEEN 비교

RANGE BETWEEN과 ROWS BETWEEN은 SQL에서 윈도우 함수(Window Functions)와 함께 사용되는 구문입니다. 이들은 윈도우 함수가 데이터의 일부 범위를 처리할 때 어떤 행을 고려할지를 지정하는데 사용됩니다. 다음으로 그들의 주요 차이점을 설명하겠습니다.

RANGE BETWEEN

RANGE BETWEEN은 윈도우 함수가 정렬된 결과 집합(ORDER BY로 정렬된)에서 값의 범위를 기반으로 행을 선택합니다.
값의 범위를 기준으로 하기 때문에 값이 동일한 여러 행이 있다면 이러한 행들은 하나로 간주됩니다.
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW와 같은 구문을 사용하여 현재 행을 포함하여 현재 행 이전의 모든 행을 선택할 수 있습니다.
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING과 같은 구문을 사용하여 현재 행의 앞뒤로 한 행씩 선택할 수도 있습니다.

ROWS BETWEEN

ROWS BETWEEN은 윈도우 함수가 정렬된 결과 집합에서 행의 물리적 위치(행 번호)를 기반으로 행을 선택합니다.
물리적 위치를 기준으로 하기 때문에 값이 동일한 여러 행이 있더라도 개별적으로 처리됩니다.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW와 같은 구문을 사용하여 현재 행을 포함하여 현재 행 이전의 모든 행을 선택할 수 있습니다.

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING과 같은 구문을 사용하여 현재 행의 앞뒤로 한 행씩 선택할 수도 있습니다.

간단히 말하면, RANGE BETWEEN은 값의 범위를 고려하여 행을 선택하는 데 사용되고, ROWS BETWEEN은 행의 물리적 위치를 기반으로 행을 선택하는 데 사용됩니다. 때에 따라서 데이터의 특성에 따라 둘 중 하나를 선택하여 사용해야 할 수 있습니다.

현재 행(Current Row)

"현재 행(Current Row)"은 윈도우 함수에서 현재 처리 중인 행을 가리키는 특별한 개념입니다. 윈도우 함수를 사용하여 결과 집합의 각 행에 대해 계산을 수행할 때, 현재 행은 현재 계산이 진행되고 있는 해당 행을 의미합니다.

윈도우 함수는 OVER 절과 함께 사용되며, OVER 절에는 PARTITION BY와 ORDER BY 구문이 사용됩니다. PARTITION BY는 데이터를 그룹화할 기준 열들을 지정하고, ORDER BY는 데이터를 정렬할 열들을 지정합니다. 이렇게 지정된 PARTITION BY와 ORDER BY에 따라서 윈도우가 정의되며, 윈도우 함수는 이 윈도우 내에서 계산이 이루어집니다.

예를 들어, 다음과 같은 쿼리에서 윈도우 함수를 사용하여 각 부서별로 급여를 기준으로 순위를 매기고 있습니다.

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

위의 쿼리에서 윈도우 함수 RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)는 각 부서별로 급여를 기준으로 정렬한 후, 현재 행(해당 직원의 급여)에 대해 순위를 매기는 것입니다. 즉, 윈도우 함수가 실행되는 각 행에 대해 해당 행의 급여를 기준으로 순위가 계산되는 것이고, 이때의 "현재 행"은 각 행에 대한 개별적인 데이터를 의미합니다.

윈도우 함수를 사용하여 행 단위로 계산을 수행할 때는 항상 "현재 행"이라는 개념을 고려하여 결과를 계산하게 됩니다. 이렇게 "현재 행" 개념을 이해하면 윈도우 함수를 효과적으로 활용하여 원하는 결과를 얻을 수 있습니다.

분석 함수의 사용은 특정 행과 그 행을 기준으로 정렬된 데이터의 부분 집합에 동시에 접근해야 할 때 유용합니다. 예를 들어, RANK(), DENSE_RANK(), ROW_NUMBER()와 같은 순위를 매기는 함수나 SUM(), AVG(), COUNT()와 같은 집계 함수를 사용할 때 OVER 절을 이용하여 특정 그룹에 대한 순위 또는 집계를 계산할 수 있습니다. 이렇게 분석 함수를 사용하면 그룹별로 혹은 정렬된 순서에 따라 각 행에 대한 결과를 계산하고 조회할 수 있습니다.

해당 게시글은 ChatGPT의 도움을 받아 작성되었습니다.