윈도 함수

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

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

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

창 함수는 중첩할 수 없습니다. 예를 들어 SUM 집계 함수는 SUM 창 함수 내에 표시될 수 있지만 창 함수 SUM은 다른 창 함수 SUM 내에 표시될 수 없습니다. 창 함수가 다른 창 함수에 중첩되어 있으므로 다음 사항이 지원되지 않습니다.

SELECT SUM(SUM(selectcol) OVER (PARTITION BY ordercol)) OVER (Partition by ordercol) FROM t;

창 함수 구문 요약

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 }}

인수

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

윈도 함수의 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는 집계와 순위, 두 가지 형식의 윈도 함수를 지원합니다.

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

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

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

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

다음은 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);