본문으로 건너뛰기
최종 수정: 에

개요

설명​

윈도우 함수(분석 함수라고도 함)는 원래 행을 유지하면서 계산을 수행하는 특수 내장 함수입니다. 집계 함수와 달리 윈도우 함수는 다음과 같은 특징이 있습니다.

  • GROUP BY로 그룹화하는 대신 특정 윈도우 범위 내의 데이터를 처리합니다.
  • 결과 집합의 각 행에 대해 값을 계산합니다.
  • SELECT 목록에 열을 추가할 수 있습니다.
  • 쿼리 처리의 마지막 단계(JOIN, WHERE, GROUP BY 이후)에 실행됩니다.

윈도우 함수는 재무 및 과학 계산에서 추세 분석, 이상값 계산, 데이터 버킷화에 흔히 사용됩니다.

구문​

<FUNCTION> ( [ <ARGUMENTS> ] ) OVER ( [ <windowDefinition> ] )

각 구성 요소는 다음과 같이 정의됩니다.

windowDefinition ::=

[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]
windowFrameClause ::=
{
  | { ROWS } <n> PRECEDING
  | { ROWS } CURRENT ROW
  | { ROWS } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } UNBOUNDED PRECEDING
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  | { ROWS } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
  | { ROWS } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
  | { ROWS } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}

매개변수​

<FUNCTION>

윈도우 함수의 이름입니다. 모든 집계 함수와 다음 특수 윈도우 함수가 포함됩니다: DENSE_RANK(), FIRST_VALUE(), LAG(), LAST_VALUE(), LEAD(), RANK(), ROW_NUMBER(), NTH_VALUE(), PERCENT_RANK(), CUME_DIST(), NTILE().

<ARGUMENTS>

선택 사항입니다. 윈도우 함수의 입력 인수입니다. 인수의 타입과 개수는 사용하는 함수에 따라 달라집니다.

<PARTITION_BY>

선택 사항입니다. GROUP BY와 유사하게 지정된 열을 기준으로 데이터를 그룹화한 다음 각 파티션 내에서 계산을 수행합니다.

<ORDER_BY>

선택 사항입니다. 각 파티션 내의 데이터를 정렬하는 데 사용됩니다. 파티션을 지정하지 않으면 전체 데이터셋을 정렬합니다. 다만 이 ORDER BY는 SQL 문 끝에 오는 일반적인 ORDER BY와 다릅니다. OVER 절에 지정한 정렬은 해당 파티션 내의 데이터에만 적용되며, SQL 문 끝의 ORDER BY는 최종 쿼리 결과에 포함된 모든 행의 순서를 제어합니다. 이 두 가지는 함께 사용할 수 있습니다. 또한 ORDER BY를 OVER 절에 명시적으로 지정하지 않으면 파티션 내 데이터의 순서가 임의로 정해져 최종 결과를 예측하기 어려울 수 있습니다. 정렬 열을 명시적으로 지정하더라도 중복 값이 포함되어 있으면 결과가 여전히 불안정할 수 있습니다. 구체적인 예는 아래 사례를 참조하세요.

<windowFrameClause>

선택 사항입니다. 윈도우 프레임을 정의하는 데 사용됩니다. 현재 RANGE와 ROWS, 두 가지 타입을 지원합니다. N PRECEDING/FOLLOWING에서 N은 양의 정수이며, 현재 행을 기준으로 슬라이딩 윈도우의 범위를 나타냅니다. 현재는 ROWS 윈도우에서만 지원되므로 현재 행을 기준으로 한 물리적 오프셋을 나타냅니다. RANGE 타입에는 현재 몇 가지 제한이 있습니다. 반드시 BOTH UNBOUNDED BOUNDARY 또는 ONE UNBOUNDED BOUNDARY AND ONE CURRENT ROW여야 합니다. 프레임을 지정하지 않으면 기본적으로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 암묵적으로 적용됩니다.

반환 값​

입력 표현식과 동일한 데이터 타입을 반환합니다.

분석 함수 데이터의 고유한 정렬 순서​

1. 반환 결과의 비일관성 문제

윈도우 함수의 ORDER BY 절이 데이터의 정렬 순서를 유일하게 결정하지 못하면 행의 순서가 불확정적이 됩니다. 예를 들어 ORDER BY 표현식의 결과에 중복 값이 있는 경우가 이에 해당합니다. 이 경우 쿼리를 여러 번 실행할 때마다 해당 행의 반환 순서가 달라져 윈도우 함수의 결과가 일관되지 않을 수 있습니다.

다음 예는 쿼리를 연속으로 실행했을 때 서로 다른 결과가 반환되는 상황을 보여줍니다. 이러한 비일관성은 주로 ORDER BY date_time이 SUM 윈도우 함수에 대해 정렬 순서를 유일하게 결정하지 못하기 때문에 발생합니다.

CREATE TABLE test_window_order 
    (item_id int,
    date_time date,
    sales double)
distributed BY hash(item_id)
properties("replication_num" = 1);

INSERT INTO test_window_order VALUES
(1, '2024-07-01', 100),
(2, '2024-07-01', 100),
(3, '2024-07-01', 140);

SELECT
    item_id, date_time, sales,
    sum(sales) OVER (ORDER BY date_time ROWS BETWEEN 
        UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM
    test_window_order;

정렬 열인 date_time에 중복 값이 있으므로 다음 두 가지 쿼리 결과가 나타날 수 있습니다.

+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       1 | 2024-07-01 |   100 |  100 |
|       3 | 2024-07-01 |   140 |  240 |
|       2 | 2024-07-01 |   100 |  340 |
+---------+------------+-------+------+
3 rows in set (0.03 sec)
+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       2 | 2024-07-01 |   100 |  100 |
|       1 | 2024-07-01 |   100 |  200 |
|       3 | 2024-07-01 |   140 |  340 |
+---------+------------+-------+------+
3 rows in set (0.02 sec)

2. 해결 방법

이 문제를 해결하려면 item_id처럼 고유한 값을 가진 열을 ORDER BY 절에 추가하여 정렬 순서의 유일성을 보장할 수 있습니다.

SELECT
        item_id,
        date_time,
        sales,
        sum(sales) OVER (
        ORDER BY item_id,
        date_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
FROM
        test_window_order;

이렇게 하면 일관된 쿼리 결과를 얻을 수 있습니다.

+---------+------------+-------+------+
| item_id | date_time  | sales | sum  |
+---------+------------+-------+------+
|       1 | 2024-07-01 |   100 |  100 |
|       2 | 2024-07-01 |   100 |  200 |
|       3 | 2024-07-01 |   140 |  340 |
+---------+------------+-------+------+
3 rows in set (0.03 sec)
Report issue