창 함수 구문 요약 창 함수 데이터에 대한 고유 순서 지정 지원되는 함수 창 함수 예제를 위한 샘플 테이블

윈도 함수

창 함수를 사용하면 사용자가 분석 비즈니스 쿼리를 보다 효율적으로 생성할 수 있습니다. 창 함수는 결과 집합의 파티션, 즉 "창"에서 실행되어 해당 창에 속하는 모든 행에 대한 값을 반환합니다. 이와는 반대로 창이 없는 함수는 결과 집합의 모든 행에 대해 계산을 실행합니다. 그 밖에도 결과 행을 집계하는 그룹 함수와 달리 창 함수에서는 테이블 표현식의 모든 행이 그대로 유지됩니다.

반환 값은 해당 창에 속한 행 집합의 값을 사용하여 계산됩니다. 창은 테이블의 각 행마다 추가 속성을 계산하는 데 사용되는 행 집합을 정의합니다. 창은 창 명세(OVER 절)를 사용하여 정의되며, 다음과 같이 세 가지 주요 개념을 근거로 합니다.

창 파티션 - 행 그룹을 형성합니다(PARTITION 절).
창 순서 지정 - 각 파티션의 행 순서 또는 시퀀스를 정의합니다(ORDER BY 절).
창 프레임 - 행 집합을 제한하기 위해 각 행마다 정의됩니다(ROWS 명세).

창 함수는 최종 ORDER BY 절을 제외하고 쿼리에서 실행되는 마지막 연산 집합입니다. 창 함수를 처리할 때는 그 전에 모든 조인을 비롯한 WHERE, GROUP BY 및 HAVING 절까지 모두 완료됩니다. 따라서 창 함수는 선택 목록 또는 ORDER BY 절에만 나타날 수 있습니다. 다른 프레임 절이 있는 단일 쿼리 내에서 여러 윈도 함수를 사용할 수 있습니다. CASE 등의 다른 스칼라 표현식에서 윈도 함수를 사용할 수도 있습니다.

창 함수 구문 요약

Window 함수는 다음과 같은 표준 구문을 따릅니다.


function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

여기서 함수는 이 섹션에서 설명하는 함수 중 하나입니다.

expr_list는 다음과 같습니다.


expression | column_name [, expr_list ]

order_list는 다음과 같습니다.


expression | column_name [ ASC | DESC ] 
[ NULLS FIRST | NULLS LAST ]
[, order_list ]

frame_clause는 다음과 같습니다.


ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |

{ BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}

인수

함수

창 함수 자세한 내용은 각 함수에 대한 설명을 참조하십시오.

OVER

창 명세를 정의하는 절입니다. OVER 절은 창 함수에서 필수 인수로서 창 함수와 다른 SQL 함수를 구분하는 역할을 합니다.

PARTITION BY expr_list

(옵션) PARTITION BY 절은 결과 집합을 여러 파티션으로 분할한다는 점에서 GROUP BY 절과 매우 유사합니다. 파티션 절이 존재하는 경우에는 함수가 각 파티션의 행에 대해 계산됩니다. 반대로 파티션 절을 지정하지 않으면 전체 테이블이 단일 파티션으로 구성되어 함수가 해당하는 전체 테이블에 대해서 계산됩니다.

DENSE_RANK, NTILE, RANK, ROW_NUMBER 같은 순위 함수에서는 결과 집합의 모든 행을 전역적으로 비교해야 합니다. 이때 PARTITION BY 절을 사용하면 쿼리 옵티마이저가 워크로드를 파티션에 따라 다수의 조각으로 분산시키기 때문에 각 집계를 병렬 방식으로 실행할 수 있습니다. PARTITION BY 절을 사용하지 않으면 단일 조각에서 직렬 방식으로 집계를 실행해야 하기 때문에 특히 대용량의 클러스터에서는 성능에 매우 부정적인 영향을 끼치게 됩니다.

Amazon Redshift는 PARTITION BY 절에서 문자열 리터럴을 지원하지 않습니다.

ORDER BY order_list

(옵션) 윈도 함수는 ORDER BY의 순서 명세에 따라 정렬된 각 파티션의 행에 적용됩니다. 이 ORDER BY 절은 frame_clause의 ORDER BY 절과 구분되어 전혀 관련이 없습니다. 이러한 ORDER BY 절은 PARTITION BY 절 없이도 사용할 수 있습니다.

순위 함수에서는 ORDER BY 절이 순위 값의 기준을 식별하는 역할을 합니다. 집계 함수에서는 각 프레임에 대한 집계 함수 계산 이전에 파티션 행의 순서를 지정해야 합니다. 윈도 함수 형식에 대한 자세한 내용은 윈도 함수 섹션을 참조하세요.

order list에는 열 식별자, 또는 열 식별자로 평가되는 표현식이 필요합니다. 열 이름 대신에 상수나 상수 표현식을 사용할 수도 없습니다.

NULLS 값은 자체 그룹으로 처리되어 NULLS FIRST 또는 NULLS LAST 옵션에 따라 정렬 후 순위가 결정됩니다. 기본적으로 NULL 값은 ASC 순서에서는 마지막에 정렬 후 순위가 결정되며, DESC 순서에서는 처음에 정렬 후 순위가 결정됩니다.

Amazon Redshift는 ORDER BY 절에서 문자열 리터럴을 지원하지 않습니다.

ORDER BY 절을 생략하면 행의 순서는 비확정적입니다.

참고

Amazon Redshift와 같은 병렬 시스템에서는 ORDER BY 절이 데이터의 전체 순서를 고유하게 지정하지 않으면 행의 순서는 비확정적입니다. 다시 말해 ORDER BY 표현식에서 중복 값이 산출되면(부분 순서 지정) Amazon Redshift를 실행할 때마다 해당하는 행의 반환 순서가 달라질 수 있습니다. 그러면 창 함수 역시 예상하지 못하거나 일관적이지 못한 결과를 반환하게 됩니다. 자세한 내용은 창 함수 데이터에 대한 고유 순서 지정 단원을 참조하십시오.

column_name

파티션으로 분할하거나 순서를 지정할 때 기준이 되는 열의 이름입니다.

ASC | DESC

표현식의 정렬 순서를 정의하는 옵션으로서 각각 다음과 같은 의미를 갖습니다.

ASC: 오름차순(예: 숫자 값의 경우 낮은 값에서 높은 값 순, 문자열의 경우 'A'에서 'Z'의 순. 지정된 옵션이 없는 경우에는 데이터가 기본적으로 오름차순으로 정렬됩니다.
DESC: 내림차순(숫자 값의 경우 높은 값에서 낮은 값 순, 문자열의 경우 'Z'에서 'A'의 순).

NULLS FIRST | NULLS LAST

NULLS의 순서를 NULL 값 이외의 값 이전에 결정할지, 혹은 이후에 결정할지 지정하는 옵션입니다. 기본적으로 ASC 순서에서는 마지막에 정렬 후 순위가 결정되며, DESC 순서에서는 처음에 정렬 후 순위가 결정됩니다.

frame_clause

집계 함수에서 프레임 절은 ORDER BY를 사용하여 함수의 창에 포함되는 행 집합을 추가적으로 정제하는 역할을 합니다. 이를 통해 순서가 지정된 결과 내에 행 집합을 추가하거나 제거할 수 있습니다. ROWS 키워드와 관련 지정자로 구성됩니다.

프레임 절은 순위 함수에 적용되지 않습니다. 또한 집계 함수의 OVER 절에 ORDER BY 절이 사용되지 않는 경우 프레임 절이 필요하지 않습니다. 집계 함수에서 ORDER BY 절이 사용되면 명시적인 프레임 절이 필요합니다.

ORDER BY 절을 지정하지 않으면 묵시적 프레임이 무제한이기 때문에 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING과 다름 없습니다.

ROWS

이 절은 현재 행에서 물리적 오프셋을 지정하여 창 프레임을 정의합니다.

이 절은 현재 창 또는 파티션에서 현재 행의 값이 결합되는 행을 지정합니다. 행의 위치는 인수를 사용하여 지정하며, 현재 행 앞 또는 뒤가 될 수 있습니다. 모든 창 프레임에서 기준점은 현재 행입니다. 각 행은 창 프레임이 파티션에서 밀려 앞으로 이동하면서 번갈아 현재 행이 됩니다.

프레임은 다음과 같이 현재 행까지 포함하여 단일 행 집합이 되거나,


{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}

혹은 다음과 같이 두 경계 사이의 행 집합이 될 수도 있습니다.


BETWEEN
{ UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }

UNBOUNDED PRECEDING은 파티션의 첫 행에서 창이 시작된다는 것을 나타내고, offset PRECEDING은 오프셋 값에 해당하는 행의 수만큼 현재 행 앞에서 창이 시작된다는 것을 나타냅니다. 기본값은 UNBOUNDED PRECEDING입니다.

CURRENT ROW는 창이 현재 행에서 시작하거나 끝난다는 것을 나타냅니다.

UNBOUNDED FOLLOWING은 파티션의 마지막 행에서 창이 끝나는 것을 나타내고, offset FOLLOWING은 오프셋 값에 해당하는 행의 수만큼 현재 행 뒤에서 창이 끝난다는 것을 나타냅니다.

offset은 현재 행 앞 또는 뒤로 물리적인 행의 수를 의미합니다. 이 경우에는 offset이 양의 숫자 값으로 평가되는 상수여야 합니다. 예를 들어 5 FOLLOWING일 때는 현재 행 뒤로 5개 행을 지나 프레임이 종료됩니다.

BETWEEN을 지정하지 않으면 묵시적이지만 프레임 경계가 현재 행으로 결정됩니다. 예를 들어 ROWS 5 PRECEDING은 ROWS BETWEEN 5 PRECEDING AND CURRENT ROW와 같습니다. 또한 ROWS UNBOUNDED FOLLOWING은 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING과 같습니다.

참고

시작 경계가 종료 경계보다 크게 프레임을 지정할 수는 없습니다. 예를 들어 다음과 같은 프레임은 지정할 수 없습니다.


between 5 following and 5 preceding
between current row and 2 preceding
between 3 following and current row

창 함수 데이터에 대한 고유 순서 지정

윈도 함수의 ORDER BY 절이 데이터의 전체 순서를 고유하게 지정하지 않으면 행의 순서는 비확정적입니다. 다시 말해 ORDER BY 표현식에서 중복 값이 산출되면(부분 순서 지정) 여러 차례 실행할 때마다 해당 행의 반환 순서가 달라질 수 있습니다. 이 경우 윈도 함수 역시 예상하지 못하거나 일관적이지 못한 결과를 반환하게 됩니다.

예를 들어 다음 쿼리는 여러 실행에 대해 다른 결과를 반환합니다. 이러한 다른 결과는 order by dateid가 SUM 윈도 함수 데이터의 고유한 순서를 생성하지 않기 때문에 발생합니다.


select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |   1730.00 |     1730.00
1827 |    708.00 |     2438.00
1827 |    234.00 |     2672.00
...

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |    234.00 |      234.00
1827 |    472.00 |      706.00
1827 |    347.00 |     1053.00
...

이 경우에는 두 번째 ORDER BY 열을 윈도 함수에 추가하여 문제를 해결할 수 있습니다.


select dateid, pricepaid,
sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid | sumpaid
--------+-----------+---------
1827 |    234.00 |  234.00
1827 |    337.00 |  571.00
1827 |    347.00 |  918.00
...

지원되는 함수

Amazon Redshift는 집계와 순위, 두 가지 형식의 윈도 함수를 지원합니다.

다음은 지원되는 집계 함수입니다.

AVG 창 함수
COUNT 창 함수
CUME_DIST 창 함수
DENSE_RANK 창 함수
FIRST_VALUE 창 함수
LAG 창 함수
LAST_VALUE 창 함수
LEAD 창 함수
LISTAGG 창 함수
MAX 창 함수
MEDIAN 창 함수
MIN 창 함수
NTH_VALUE 창 함수
PERCENTILE_CONT 창 함수
PERCENTILE_DISC 창 함수
RATIO_TO_REPORT 창 함수
STDDEV_SAMP 및 STDDEV_POP 창 함수(STDDEV_SAMP 및 STDDEV는 동의어)
SUM 창 함수
VAR_SAMP 및 VAR_POP 창 함수(VAR_SAMP와 VARIANCE는 동의어)

다음은 지원되는 순위 함수입니다.

창 함수 예제를 위한 샘플 테이블

각 함수 설명과 함께 특정 창 함수 예제를 찾을 수도 있습니다. 일부 예는 다음과 같은 11개의 행이 포함된 WINSALES라는 테이블을 사용합니다.

SALESID	DATEID	SELLERID	BUYERID	QTY	QTY_SHIPPED
30001	30001	3	B	10	10
10001	10001	1	C	10	10
10005	10005	1	A	30
40001	40001	4	A	40
10006	10006	1	C	10
20001	20001	2	B	20	20
40005	40005	4	A	10	10
20002	20002	2	C	20	20
30003	30003	3	B	15
30004	30004	3	B	20
30007	30007	3	C	30

다음은 WINSALES 샘플 테이블을 생성하여 채우는 스크립트입니다.


CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid char(10),
  qty int,
  qty_shipped int);

INSERT INTO winsales VALUES
  (30001, '8/2/2003', 3, 'b', 10, 10),
  (10001, '12/24/2003', 1, 'c', 10, 10),
  (10005, '12/24/2003', 1, 'a', 30, null),	
  (40001, '1/9/2004', 4, 'a', 40, null),	
  (10006, '1/18/2004', 1, 'c', 10, null),	
  (20001, '2/12/2004', 2, 'b', 20, 20),
  (40005, '2/12/2004', 4, 'a', 10, 10),
  (20002, '2/16/2004', 2, 'c', 20, 20),
  (30003, '4/18/2004', 3, 'b', 15, null),
  (30004, '4/18/2004', 3, 'b', 20, null),	
  (30007, '9/7/2004', 3, 'c', 30, null);

javascript가 브라우저에서 비활성화되거나 사용이 불가합니다.

AWS 설명서를 사용하려면 Javascript가 활성화되어야 합니다. 지침을 보려면 브라우저의 도움말 페이지를 참조하십시오.

문서 규칙

TO_VARBYTE

AVG