윈도우 함수로 데이터 분석하기

이 항목의 내용:

이 항목에는 창 함수에 대한 기본 개념 정보가 포함되어 있습니다. 윈도우 함수 사용법에 이미 익숙하다면 다음 참고 정보로 충분할 수 있습니다.

소개

윈도우 함수는 파티션 이라고 하는 관련 행 그룹에서 작업하는 분석 SQL 함수입니다. 파티션은 일반적으로 제품 카테고리, 위치, 기간 또는 사업 단위와 같은 익숙한 차원에 따라 논리적으로 그룹화된 행입니다. 함수 결과는 암시적 또는 명시적 윈도우 프레임 을 기준으로 각 파티션에 대해 계산됩니다. 윈도우 프레임은 현재 행 을 기준으로 고정되거나 가변적인 행 집합입니다. 현재 행은 현재 함수 결과가 계산되고 있는 단일 입력 행입니다. 함수 결과는 각 파티션 내에서 행별로 계산되고, 윈도우 프레임의 각 행이 현재 행이 됩니다.

이 동작을 정의하는 구문은 함수에 대한 OVER 절입니다. 많은 경우 OVER 절은 윈도우 함수를 이름(예: AVG 또는 SUM)이 동일한 일반 SQL 함수와 구분합니다. OVER 절은 다음의 세 주요 구성 요소로 이루어져 있습니다.

  • PARTITION BY 절

  • ORDER BY 절

  • 윈도우 프레임 사양

해당 함수 또는 쿼리에 따라 이러한 구성 요소는 모두 선택 사항일 수 있으며, OVER 절이 비어 있는 윈도우 함수는 유효한 OVER() 입니다. 그러나 대부분의 분석 쿼리에서 윈도우 함수는 하나 이상의 명시적 OVER 절 구성 요소를 필요로 합니다. 다른 SQL 함수를 지원하는 모든 컨텍스트에서 윈도우 함수를 호출할 수 있습니다. 다음 섹션에서는 윈도우 함수의 개념을 더 자세히 설명하고 몇 가지 소개 예제를 제시합니다. 전체 구문 정보는 윈도우 함수 구문 및 사용법 섹션을 참조하십시오.

윈도우 함수 vs 집계 함수

윈도우 함수에 대해 배우는 좋은 방법은 일반 집계 함수와 그에 대응하는 윈도우 함수를 비교하는 것입니다. 여러 표준 집계 함수 (예: SUM, COUNT, AVG)에는 같은 이름의 해당 윈도우 함수가 있습니다. 둘을 구별하려면 다음 사항에 유의하십시오.

  • 집계 함수의 경우 입력은 행 그룹이고 출력은 한 행입니다.

  • 윈도우 함수의 경우 입력은 파티션 내의 각 행이고, 출력은 입력 행당 1개 행입니다.

예를 들어 SUM 집계 함수는 모든 입력 행에 대한 단일 합계 값을 반환하는 반면, 윈도우 함수는 파티션의 다른 모든 행을 기준으로 각 행(현재 행)에 대해 하나씩 여러 개의 합계를 반환합니다.

작동 방식을 확인하려면 먼저 푸드트럭 메뉴 품목의 판매 원가와 가격이 포함된 menu_items 테이블을 생성하고 로드 합니다. 일반 AVG 함수를 사용하여 다양한 카테고리의 메뉴 항목에 대한 평균 상품 단가를 찾습니다.

SELECT menu_category,
    AVG(menu_cogs_usd) avg_cogs
  FROM menu_items
  GROUP BY 1
  ORDER BY menu_category;
Copy
+---------------+------------+
| MENU_CATEGORY |   AVG_COGS |
|---------------+------------|
| Beverage      | 0.60000000 |
| Dessert       | 1.79166667 |
| Main          | 6.11046512 |
| Snack         | 3.10000000 |
+---------------+------------+

이 함수는 avg_cogs 에 대해 그룹화된 결과 하나를 반환합니다.

또는 OVER 절을 지정하고 AVG를 윈도우 함수로 사용할 수 있습니다. (60개 행 테이블에서 결과는 15개 행으로 제한됩니다.)

SELECT menu_category,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category) avg_cogs
  FROM menu_items
  ORDER BY menu_category
  LIMIT 15;
Copy
+---------------+----------+
| MENU_CATEGORY | AVG_COGS |
|---------------+----------|
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
+---------------+----------+

이 함수는 각 파티션의 각 행에 대한 평균을 반환하고 파티션 열 값이 변경되면 계산을 재설정합니다. 윈도우 함수의 값을 더 명확하게 표시하려면 함수 정의에 ORDER BY 절과 윈도우 프레임을 추가합니다. 또한 평균값 외에 원시 menu_cogs_usd 값도 반환하므로 특정 계산이 어떻게 작동하는지 확인할 수 있습니다. 이 쿼리는 명시적 윈도우 프레임에 따라 달라지는 롤링 계산인 “이동 평균”의 간단한 예입니다. 이와 같은 예를 더 보려면 시계열 데이터 분석하기 섹션을 참조하십시오.

SELECT menu_category, menu_price_usd, menu_cogs_usd,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category ORDER BY menu_price_usd ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
  FROM menu_items
  ORDER BY menu_category, menu_price_usd
  LIMIT 15;
Copy
+---------------+----------------+---------------+----------+
| MENU_CATEGORY | MENU_PRICE_USD | MENU_COGS_USD | AVG_COGS |
|---------------+----------------+---------------+----------|
| Beverage      |           2.00 |          0.50 |  0.58333 |
| Beverage      |           3.00 |          0.50 |  0.57500 |
| Beverage      |           3.00 |          0.75 |  0.63333 |
| Beverage      |           3.50 |          0.65 |  0.65000 |
| Dessert       |           3.00 |          0.50 |  0.91666 |
| Dessert       |           4.00 |          1.00 |  1.58333 |
| Dessert       |           5.00 |          1.25 |  2.08333 |
| Dessert       |           6.00 |          2.50 |  2.66666 |
| Dessert       |           6.00 |          2.50 |  2.75000 |
| Dessert       |           7.00 |          3.00 |  3.00000 |
| Main          |           5.00 |          1.50 |  2.03333 |
| Main          |           6.00 |          2.60 |  3.00000 |
| Main          |           6.00 |          2.00 |  2.33333 |
| Main          |           6.00 |          2.40 |  3.13333 |
| Main          |           8.00 |          4.00 |  3.66666 |
+---------------+----------------+---------------+----------+

윈도우 프레임은 현재 행과 그 뒤에 오는 두 행(파티션 내)만 고려되도록 평균 계산을 조정합니다. 파티션의 마지막 행에는 다음 행이 없으므로, 예를 들어 마지막 Beverage 행의 평균은 해당 menu_cogs_usd 값(0.65)과 동일합니다. 윈도우 함수의 출력은 함수에 전달된 개별 행과 윈도우 프레임에 적합한 다른 행의 값에 따라 달라집니다.

윈도우 함수의 행 정렬하기

앞의 AVG 윈도우 함수 예제에서는 함수 정의 내에 menu_price_usd 절을 사용하여 윈도우 프레임이 정렬된 데이터(이 경우 ORDER BY 기준)에 적용되도록 합니다.

두 가지 유형의 윈도우 함수에는 ORDER BY 절이 필요합니다.

  • 명시적 윈도우 프레임이 있는 윈도우 함수는 각 파티션의 행 하위 집합에 대해 누적 합계나 이동 평균을 계산하는 등 롤링 작업을 수행합니다. ORDER BY 절이 없으면 윈도우 프레임은 의미가 없으며, “선행”과 “후행”의 집합은 결정론적이어야 합니다.

  • 행의 “순위”에 따라 정보를 반환하는 순위 윈도우 함수(예: CUME_DIST, RANK, DENSE_RANK)를 사용할 수 있습니다. 예를 들어, 월별 수익에 따라 내림차순으로 매장의 순위를 매기면 수익이 가장 높은 매장이 1순위, 두 번째로 수익이 높은 매장이 2순위가 되는 식으로 순위를 매길 수 있습니다.

윈도우 함수에 대한 ORDER BY 절은 쿼리의 최종 결과를 정렬하는 기본 ORDER BY 절과 동일한 구문을 지원합니다. 이 두 ORDER BY 절은 별개의 절입니다. ORDER BY 절 내의 OVER 절은 윈도우 함수가 행을 처리하는 순서만 제어하며, 전체 쿼리의 출력은 제어하지 않습니다. 많은 경우 윈도우 함수 쿼리에는 두 가지 유형의 ORDER BY 절이 모두 포함됩니다.

OVER 절 내의 PARTITION BY 및 ORDER BY 절도 독립적입니다. ORDER BY 절은 PARTITION BY 절 없이도 사용할 수 있고, 그 반대도 마찬가지입니다.

쿼리를 작성하기 전에 개별 윈도우 함수의 구문을 확인합니다. ORDER BY 절의 구문 요구 사항은 함수에 따라 다릅니다.

  • 일부 윈도우 함수에는 ORDER BY 절이 필요합니다.

  • 일부 윈도우 함수에서는 ORDER BY 절이 있으면 사용하지만 필수가 아닙니다.

  • 일부 윈도우 함수는 ORDER BY 절을 허용하지 않습니다.

  • 일부 윈도우 함수는 ORDER BY 절을 암시적 윈도우 프레임으로 해석합니다.

조심

일반적으로 SQL은 암시적 조항이 거의 없는 명시적 언어입니다. 그러나 일부 윈도우 함수의 경우 ORDER BY 절은 윈도우 프레임을 의미합니다. 자세한 내용은 윈도우 프레임 사용법 노트 섹션을 참조하십시오.

명시적이지 않고 암시적인 동작은 이해하기 어려운 결과를 초래할 수 있으므로 명시적으로 윈도우 프레임을 선언하는 것이 좋습니다.

다양한 유형의 윈도우 프레임 사용하기

윈도우 프레임은 명시적이거나 암시적으로 정의됩니다. 이는 OVER 절 내에 ORDER BY 절이 있는지 여부에 따라 달라집니다.

  • 명시적인 프레임 구문은 구문 의 windowFrameClause 섹션을 참조하십시오. 개방형 경계를 정의할 수 있습니다. 즉, 파티션 시작부터 현재 행까지, 현재 행부터 파티션 끝까지 또는 완전히 “제한 없는” 경계를 정의할 수 있습니다. 또는 파티션의 현재 행을 기준으로 하는 명시적 오프셋(포함)을 사용할 수 있습니다.

  • 암시적 프레임은 OVER 절에 windowFrameClause 가 포함되지 않은 경우 기본적으로 사용됩니다. 기본 프레임은 해당 함수에 따라 달라집니다. 윈도우 프레임 사용법 노트 도 참조하십시오.

범위 기반 윈도우 프레임 대 행 기반 윈도우 프레임

Snowflake에서 지원하는 두 가지 기본 타입의 윈도우 프레임은 다음과 같습니다.

행 기반:

현재 행으로부터의 물리적 오프셋을 기준으로 행의 정확한 순서가 프레임에 속합니다. 예를 들어, 5 PRECEDING 은 현재 행 앞에 있는 5개의 행을 의미합니다. 오프셋은 숫자여야 합니다. ROWS 모드가 포함되고 항상 현재 행을 기준으로 합니다. 지정된 앞 또는 뒤 행의 개수가 파티션의 한계를 초과하는 경우 Snowflake는 해당 값을 NULL로 처리합니다.

프레임에 명확하게 번호가 매겨진 경계가 아니라 개방형 경계가 있는 경우에도 비슷한 물리적 오프셋이 적용됩니다. 예를 들어, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 프레임이 현재 행과 현재 행 자체에 물리적으로 선행하는 전체 행 세트(0개 이상)로 구성됨을 의미합니다.

범위 기반:

현재 행의 ORDER BY 값에서 오프셋이 주어진 논리적 범위의 행이 프레임에 속합니다. 예를 들어, 5 PRECEDING 은 현재 행의 ORDER BY 값에 최대 5를 더하거나 뺀 ORDER BY 값이 있는 행을 의미합니다(DESC 순서의 경우 더하기, ASC 순서의 경우 빼기). 오프셋 값은 숫자나 간격일 수 있습니다.

프레임에 번호가 매겨진 경계가 아닌 개방형 경계가 있는 경우에도 비슷한 논리적 오프셋이 적용됩니다. 예를 들어, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 는 프레임이 현재 행의 물리적으로 앞에 있는 모든 행, 현재 행 자체 그리고 현재 행과 동일한 ORDER BY 값을 가진 인접 행으로 구성됨을 의미합니다. RANGE 윈도우 프레임의 경우 CURRENT ROW는 물리적으로 현재 행을 의미하는 것이 아니라, 현재 물리적 행과 동일한 ORDER BY 값을 갖는 모든 행을 의미합니다.

윈도우 함수 쿼리는 ORDER BY 식, 테이블의 데이터, 프레임의 정확한 정의에 따라 매우 다른 결과를 반환할 수 있기 때문에 ROWS BETWEEN과 RANGE BETWEEN 윈도우 프레임의 구분은 중요합니다. 다음 예제에서는 동작의 차이점을 보여줍니다.

명시적 오프셋이 있는 RANGE BETWEEN과 ROWS BETWEEN 비교하기

범위 기반 윈도우 프레임에는 ORDER BY 열 또는 식과 RANGE BETWEEN 사양이 필요합니다. 윈도우 프레임의 논리적인 경계는 현재 행의 ORDER BY 값(숫자 상수 또는 간격 리터럴)에 따라 달라집니다.

예를 들어, heavy_weather 시계열 테이블은 다음과 같이 정의됩니다.

CREATE OR REPLACE TABLE heavy_weather
  (start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
Copy

이 테이블의 샘플 행은 다음과 같습니다.

+-------------------------+--------+-------+-------------+
| START_TIME              | PRECIP | CITY  | COUNTY      |
|-------------------------+--------+-------+-------------|
| 2021-12-30 11:23:00.000 |   0.12 | Lebec | Los Angeles |
| 2021-12-30 11:43:00.000 |   0.98 | Lebec | Los Angeles |
| 2021-12-30 13:53:00.000 |   0.23 | Lebec | Los Angeles |
| 2021-12-30 14:53:00.000 |   0.13 | Lebec | Los Angeles |
| 2021-12-30 15:15:00.000 |   0.29 | Lebec | Los Angeles |
| 2021-12-30 17:53:00.000 |   0.10 | Lebec | Los Angeles |
| 2021-12-30 18:53:00.000 |   0.09 | Lebec | Los Angeles |
| 2021-12-30 19:53:00.000 |   0.07 | Lebec | Los Angeles |
| 2021-12-30 20:53:00.000 |   0.07 | Lebec | Los Angeles |
+-------------------------+--------+-------+-------------+

쿼리가 start_time 로 정렬된 윈도우 프레임을 사용하여 precip (강수량) 열에 대한 3시간 이동 평균(AVG)을 계산한다고 가정해 보겠습니다.

AVG(precip)
  OVER(ORDER BY start_time
    RANGE BETWEEN CURRENT ROW AND INTERVAL '3 hours' FOLLOWING)
Copy

위의 샘플 행이 주어졌을 때 현재 행이 2021-12-30 11:23:00.000 (첫 번째 샘플 행)인 경우 다음 두 행(2021-12-30 11:43:00.000 및 2021-12-30 13:53:00.000)만 프레임 안에 들어갑니다. 이후 타임스탬프는 3시간 이상 후입니다.

그러나 윈도우 프레임을 1일 간격으로 변경하면 현재 행 뒤에 오는 모든 샘플 행은 모두 같은 날짜(2021-12-30)의 타임스탬프가 있기 때문에 프레임 내부에 포함됩니다.

RANGE BETWEEN CURRENT ROW AND INTERVAL '1 day' FOLLOWING
Copy

이 구문을 RANGE BETWEEN에서 ROWS BETWEEN으로 변경하려면 프레임에 고정 경계를 지정해야 하는데, 고정 경계는 현재 행에 1, 3 또는 10 행과 같이 ORDER BY 식에서 반환되는 값에 관계없이 정확히 정렬된 행의 수(예: 현재 행과 다음의 정확한 행 수)를 나타내는 것입니다.

명시적 숫자 오프셋이 있는 RANGE BETWEEN 예제 도 참조하십시오.

개방형 경계로 RANGE BETWEEN와 ROWS BETWEEN 비교하기

다음 예제에서는 다음 윈도우 프레임이 동일한 행 집합에 대해 계산될 때의 결과를 비교합니다.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Copy

이 예제에서는 이름이 menu_items 인 작은 테이블에서 선택합니다. menu_items 테이블 생성 및 로드하기 섹션을 참조하십시오.

SUM 윈도우 함수는 각 menu_category 파티션의 menu_price_usd 값을 집계합니다. ROWS BETWEEN 구문을 사용하면 각 파티션 내에서 실행 합계가 어떻게 누적되는지 쉽게 확인할 수 있습니다.

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;
Copy
+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      5.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     18.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |      6.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

RANGE BETWEEN 구문을 다른 동일한 쿼리와 함께 사용하면 계산이 처음에는 명확하지 않으며, 현재 행 의 다른 해석(현재 행 자체와 해당 행과 동일한 ORDER BY 값이 있는 인접 행)에 따라 계산이 달라집니다.

예를 들어, 결과에서 두 번째 및 세 번째 행의 sum_price 값은 해당 행의 ORDER BY 값이 동일하므로 모두 8.00 입니다. 이 동작은 결과 세트의 다른 두 곳에서 발생하며, 여기서 sum_price 는 24.00 및 12.00 로 연속적으로 계산됩니다.

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;
Copy
+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

누적 및 슬라이딩 계산을 위한 윈도우 프레임

윈도우 프레임은 누적 계산과 이동 계산을 포함한 다양한 유형의 분석 쿼리를 실행하는 데 매우 유연한 메커니즘입니다. 예를 들어, 누적 합계를 반환하려면 고정된 지점에서 시작하여 전체 파티션을 행 단위로 이동하는 윈도우 프레임을 지정할 수 있습니다.

OVER(PARTITION BY col1 ORDER BY col2 ROWS UNBOUNDED PRECEDING)
Copy

이 유형의 프레임에 대한 또 다른 예는 다음과 같습니다.

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Copy

이러한 프레임에 적합한 행의 수는 가변적이지만, 프레임의 시작점과 끝점은 숫자나 간격 경계가 아닌 명명된 경계를 사용하여 고정됩니다.

윈도우 함수 계산을 특정 개수(또는 범위)의 행에 걸쳐 앞으로 진행하려면 명시적 오프셋을 사용할 수 있습니다.

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
Copy

이 경우, 최대 7개 행(3 + 현재 행 + 3)으로 구성된 슬라이딩 프레임이 생성됩니다. 이 유형의 프레임에 대한 또 다른 예는 다음과 같습니다.

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)
Copy

윈도우 프레임에는 명명된 경계와 명시적 오프셋이 혼합되어 포함될 수 있습니다.

슬라이딩 윈도우 프레임

슬라이딩 윈도우 프레임은 파티션의 행을 따라 “밀어가며” 움직이는 고정 너비의 프레임으로, 매번 파티션의 다른 부분을 덮습니다. 프레임의 행 수는 파티션의 시작 또는 끝 부분을 제외하고는 동일하게 유지되며, 이 부분에서는 행 수가 줄어들 수 있습니다.

슬라이딩 윈도우는 종종 이동 평균을 계산하는 데 사용되는데, 이동 평균은 고정된 크기 간격(예: 일 수)을 기반으로 합니다. 간격의 크기가 일정하지만, 시간이 지나면서(또는 다른 차원에 따라) 간격의 실제 값이 변합니다.

예를 들어, 주식 시장 애널리스트는 주가의 13주 이동 평균 중 일부를 기준으로 주가를 분석합니다. 오늘의 이동 평균 가격은 오늘 장 마감 시간의 주가와 최근 13주 동안 각 날짜 마감 시간 주가의 평균입니다. 주 5일 주식을 거래하고 최근 13주 동안 공휴일이 없는 경우, 이동 평균은 최근 65 거래일(오늘 포함)의 평균 주가입니다.

다음 예제는 6월 말일 및 7월 초 며칠 동안 주가의 13주(91일) 이동 평균을 보여줍니다.

  • 6월 30일에 함수는 4월 1일에서 6월 30일(포함)까지의 평균 주가를 반환합니다.

  • 7월 1일에 함수는 4월 2일에서 7월 1일(각 날짜 포함)까지의 평균 가격을 반환합니다.

  • 7월 2일에 함수는 4월 3일에서 7월 2일(각 날짜 포함)까지의 평균 가격을 반환합니다.

다음 예에서는 해당 월의 첫 7일 동안 작은(3일) 슬라이딩 윈도우를 사용합니다. 이 예제에서는 기간의 시작 시 파티션이 가득 차지 않았을 수 있다는 사실을 고려합니다.

7일 윈도우의 3일 슬라이딩 윈도우 프레임

쿼리 결과의 해당 모형에서 볼 수 있듯이 마지막 열에는 가장 최근 3일간의 판매 데이터 합계가 포함되어 있습니다. 예를 들어, 4일차에 해당하는 열의 값은 36 이며, 이는 2, 3, 4일차 매출의 합계(11 + 12 + 13)입니다.

+--------+-------+---------------+
| Day of | Sales | Most Recent   |
| Month  | Today | 3 Days' Sales |
|--------+-------+---------------+
|      1 |    10 |            10 |
|      2 |    11 |            21 |
|      3 |    12 |            33 |
|      4 |    13 |            36 |
|      5 |    14 |            39 |
|    ... |   ... |           ... |
+--------+-------+---------------+

순위 윈도우 함수

순위 윈도우 함수의 구문은 다른 윈도우 함수의 구문과 기본적으로 같습니다. 예외는 다음과 같습니다.

  • 순위 윈도우 함수의 경우 OVER 절 내에 ORDER BY 절이 필요합니다.

  • RANK 자체와 같은 일부 순위 함수의 경우, 입력 인자가 필요하지 않습니다. RANK 함수의 경우 반환되는 값은 OVER 절 안의 ORDER BY 절에 의해 결정되는 숫자 순위에만 기반합니다. 따라서 함수에 열 이름이나 식을 전달할 필요가 없습니다.

가장 단순한 순위 함수의 이름은 RANK입니다. 이 함수를 사용하면 다음을 수행할 수 있습니다.

  • 매출(판매)을 기준으로 영업사원의 순위를 가장 높은 순위부터 가장 낮은 순위까지 지정합니다.

  • 1인당 GDP(1인당 국민 소득)를 기준으로 국가를 가장 높은 순위부터 가장 낮은 순위까지 지정합니다.

  • 대기 오염에 대해 국가의 순위를 가장 낮은 순위부터 가장 높은 순위까지 지정합니다.

이 함수는 정렬된 행 집합에서 행의 숫자 순위 위치를 간단히 식별합니다. 첫 번째 행은 순위 1이고, 두 번째 행은 순위 2이며, 이런 식으로 계속됩니다. 다음 예제는 Amount Sold 를 기준으로 영업 사원의 순위 순서를 보여줍니다.

+-------------+-------------+------+
| Salesperson | Amount Sold | Rank |
|-------------+-------------+------|
| Smith       |        2000 |    1 |
| Jones       |        1500 |    2 |
| Torkelson   |        1200 |    3 |
| Dolenz      |        1100 |    4 |
+-------------+-------------+------+

순위를 지정하려면 먼저 행을 정렬해야 합니다. 따라서 OVER 절 내에 ORDER BY 절을 사용해야 합니다.

다음 예제를 생각해 보십시오. 다음 예제에서는 매장 순위 1, 2, 3 등과 같이 매장 체인의 지점 중 매장 수익 순위를 확인합니다. 이 예제에서는 도시 내 각 매장의 수익성을 기준으로 순위를 매겼습니다. 행은 내림차순 순서(최고 수익 먼저)로 배치되므로 가장 수익이 높은 매장의 순위가 1입니다.

SELECT city, branch_ID, net_profit,
       RANK() OVER (PARTITION BY city ORDER BY net_profit DESC) AS rank
    FROM store_sales
    ORDER BY city, rank;
+-----------+-----------+------------+------+
| CITY      | BRANCH_ID | NET_PROFIT | RANK |
|-----------+-----------+------------+------|
| Montreal  |         3 |   10000.00 |    1 |
| Montreal  |         4 |    9000.00 |    2 |
| Vancouver |         2 |   15000.00 |    1 |
| Vancouver |         1 |   10000.00 |    2 |
+-----------+-----------+------------+------+
Copy

참고

net_profit 열은 RANK 함수에 인자로 전달할 필요가 없습니다. 대신, 입력 행은 net_profit 을 기준으로 정렬됩니다. RANK 함수는 파티션 내에서 행의 위치(1, 2, 3 등)만 반환해야 합니다.

순위 함수의 출력은 다음에 따라 달라집니다.

  • 함수로 전달된 개별 행.

  • 파티션에 있는 다른 행의 값.

  • 파티션에서 모든 행의 순서.

Snowflake는 다양한 순위 함수를 제공합니다. 이러한 함수의 목록과 구문에 대한 자세한 내용은 윈도우 함수 섹션을 참조하십시오.

도시 내의 다른 매장뿐만 아니라 체인 내 다른 모든 매장과 비교하여 매장의 순위를 매기려면 아래 쿼리를 사용합니다.

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
Copy

다음 쿼리에서는 첫 번째 ORDER BY 절을 사용하여 윈도우에 의한 처리를 제어하며 두 번째 ORDER BY 절은 전체 쿼리 출력의 순서를 제어합니다.

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
  ORDER BY branch_ID;
Copy

설명된 예

이 예에서는 매출 시나리오를 사용하여 이 항목의 앞에서 설명한 여러 개념을 보여줍니다.

지난주 매출을 기준으로 하는 값을 보여주는 재무 보고서를 생성해야 하는 상황을 가정해 보겠습니다.

  • 일일 매출

  • 주간 순위(즉, 주간 매출 최고에서 최저 순위)

  • 이번 주 현재까지 매출(즉, 오늘을 포함하여 현재까지 이번 주 초부터 모든 날짜에 대한 “누적 합계”)

  • 이번 주 매출 총계

  • 3일 이동 평균(즉, 현재 날짜와 이전 2일의 평균)

보고서는 다음과 같습니다.

+--------+-------+------+--------------+-------------+--------------+
| Day of | Sales | Rank | Sales So Far | Total Sales | 3-Day Moving |
| Week   | Today |      | This Week    | This Week   | Average      |
|--------+-------+------+--------------+-------------|--------------+
|      1 |    10 |    4 |           10 |          84 |         10.0 |
|      2 |    14 |    3 |           24 |          84 |         12.0 |
|      3 |     6 |    5 |           30 |          84 |         10.0 |
|      4 |     6 |    5 |           36 |          84 |          9.0 |
|      5 |    14 |    3 |           50 |          84 |         10.0 |
|      6 |    16 |    2 |           66 |          84 |         11.0 |
|      7 |    18 |    1 |           84 |          84 |         12.0 |
+--------+-------+------+--------------+-------------+--------------+

이 쿼리에 대한 SQL은 약간 복잡합니다. 이 예제에서의 단순 쿼리와 달리, 이 설명에서는 개별 열에 대한 SQL을 살펴보겠습니다.

실제 시나리오에서는 수년 간의 데이터가 있으므로 특정 주간의 데이터에 대한 합계와 평균을 계산하려면 1주 윈도우를 사용하거나 다음과 유사한 필터를 사용해야 합니다.

... WHERE date >= start_of_relevant_week and date <= end_of_relevant_week ...
Copy

그러나 이 예에서는 테이블에 가장 최근 주에 해당하는 데이터만 포함된 것으로 가정하겠습니다.

CREATE TABLE store_sales_2 (
    day INTEGER,
    sales_today INTEGER
    );
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table STORE_SALES_2 successfully created. |
+-------------------------------------------+
INSERT INTO store_sales_2 (day, sales_today) VALUES
    (1, 10),
    (2, 14),
    (3,  6),
    (4,  6),
    (5, 14),
    (6, 16),
    (7, 18);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       7 |
+-------------------------+
Copy

매출 순위 계산

Rank 열은 RANK 함수를 사용하여 계산됩니다.

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+
| DAY | SALES_TODAY | RANK |
|-----+-------------+------|
|   1 |          10 |    5 |
|   2 |          14 |    3 |
|   3 |           6 |    6 |
|   4 |           6 |    6 |
|   5 |          14 |    3 |
|   6 |          16 |    2 |
|   7 |          18 |    1 |
+-----+-------------+------+
Copy

기간에는 7일이 있지만, 순위는 5개(1, 2, 3, 5, 6)만 있습니다. 2개의 공동 순위(3등 및 6등)가 있으므로, 4등 및 7등이 없습니다.

이번주 현재까지 매출 계산

Sales So Far This Week 열은 SUM 을 윈도우 프레임이 포함된 윈도우 함수로 사용하여 계산됩니다.

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------------------------+
| DAY | SALES_TODAY | SALES SO FAR THIS WEEK |
|-----+-------------+------------------------|
|   1 |          10 |                     10 |
|   2 |          14 |                     24 |
|   3 |           6 |                     30 |
|   4 |           6 |                     36 |
|   5 |          14 |                     50 |
|   6 |          16 |                     66 |
|   7 |          18 |                     84 |
+-----+-------------+------------------------+
Copy

이 쿼리는 날짜별로 행을 정렬한 후 각 날짜에 대해 윈도우 시작부터 현재 날짜(포함)까지의 매출 합계를 계산합니다.

이번주 총 매출 계산

Total Sales This Week 열은 SUM 을 사용하여 계산됩니다.

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER ()
               AS total_sales
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+-------------+
| DAY | SALES_TODAY | TOTAL_SALES |
|-----+-------------+-------------|
|   1 |          10 |          84 |
|   2 |          14 |          84 |
|   3 |           6 |          84 |
|   4 |           6 |          84 |
|   5 |          14 |          84 |
|   6 |          16 |          84 |
|   7 |          18 |          84 |
+-----+-------------+-------------+
Copy

3일 이동평균 계산하기

3-Day Moving Average 열은 AVG 을 윈도우 프레임이 포함된 윈도우 함수로 사용하여 계산됩니다.

SELECT day, 
       sales_today, 
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+----------------------+
| DAY | SALES_TODAY | 3-DAY MOVING AVERAGE |
|-----+-------------+----------------------|
|   1 |          10 |               10.000 |
|   2 |          14 |               12.000 |
|   3 |           6 |               10.000 |
|   4 |           6 |                8.666 |
|   5 |          14 |                8.666 |
|   6 |          16 |               12.000 |
|   7 |          18 |               16.000 |
+-----+-------------+----------------------+
Copy

이 윈도우 프레임과 앞서 설명한 윈도우 프레임의 차이점은 시작점, 즉 고정된 경계와 명시적 오프셋입니다.

요약

모든 열을 보여주는 최종 버전의 쿼리는 다음과 같습니다.

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank,
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK",
       SUM(sales_today)
           OVER ()
               AS total_sales,
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+------------------------+-------------+----------------------+
| DAY | SALES_TODAY | RANK | SALES SO FAR THIS WEEK | TOTAL_SALES | 3-DAY MOVING AVERAGE |
|-----+-------------+------+------------------------+-------------+----------------------|
|   1 |          10 |    5 |                     10 |          84 |               10.000 |
|   2 |          14 |    3 |                     24 |          84 |               12.000 |
|   3 |           6 |    6 |                     30 |          84 |               10.000 |
|   4 |           6 |    6 |                     36 |          84 |                8.666 |
|   5 |          14 |    3 |                     50 |          84 |                8.666 |
|   6 |          16 |    2 |                     66 |          84 |               12.000 |
|   7 |          18 |    1 |                     84 |          84 |               16.000 |
+-----+-------------+------+------------------------+-------------+----------------------+
Copy

추가 예제

이 섹션에서는 윈도우 함수에 대한 더 많은 예제를 제공하고 PARTITION BY 절과 ORDER BY 절이 함께 작동하는 방식을 설명합니다.

이러한 예에서는 다음 테이블과 데이터가 사용됩니다.

CREATE TABLE sales (sales_date DATE, quantity INTEGER);

INSERT INTO sales (sales_date, quantity) VALUES
    ('2018-01-01', 1),
    ('2018-01-02', 3),
    ('2018-01-03', 5),
    ('2018-02-01', 2)
    ;
Copy

ORDER BY 절이 있는 윈도우 함수

ORDER BY 절은 각 윈도우 내의 데이터 순서(및 파티션이 2개 이상인 경우 각 파티션도 제어)를 제어합니다. 이 절은 새 행이 추가될 때 시간 경과에 따른 “누적 합계”를 표시하기에 유용합니다.

누적 합계는 윈도우의 시작부터 현재 행(포함)까지 또는 현재 행부터 윈도우의 끝까지 계산됩니다.

쿼리에서는 “슬라이딩” 윈도우를 사용할 수 있으며, 이 윈도우는 현재 행을 기준으로 지정된 n 개 행(예: 현재 행을 포함한 최근 10개 행)을 처리하는 고정 너비 윈도우입니다.

고정 경계가 있는 윈도우 프레임

윈도우 프레임에 고정 경계가 있는 경우, 윈도우의 시작부터 현재 행까지(또는 현재 행에서 윈도우의 끝까지) 값을 계산할 수 있습니다.

SELECT MONTH(sales_date) AS MONTH_NUM, 
       quantity, 
       SUM(quantity) OVER (ORDER BY MONTH(sales_date)
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

쿼리 결과에는 CUMULATIVE_SUM_QUANTITY 열의 계산 방법을 보여주는 추가 설명이 포함됩니다.

+-----------+----------+-------------------------+
| MONTH_NUM | QUANTITY | CUMULATIVE_SUM_QUANTITY |
|-----------+----------+-------------------------|
|         1 |        1 |                       1 |  -- sum = 1
|         1 |        3 |                       4 |  -- sum = 1 + 3
|         1 |        5 |                       9 |  -- sum = 1 + 3 + 5
|         2 |        2 |                      11 |  -- sum = 1 + 3 + 5 + 2
+-----------+----------+-------------------------+
Copy

명시적 오프셋이 있는 윈도우 프레임

재무 분야에서 분석가들은 “이동 평균”을 살펴보는 경우가 많습니다.

예를 들어, X축은 시간이고 Y축은 지난 13주 동안의 주식 평균 가격(즉, 13주 이동 평균)을 표시하는 그래프가 있을 수 있습니다. 13주 평균 주가 그래프에서, 6월 30일에 표시된 주가는 6월 30일의 평균 주가가 아니라, 6월 30일을 포함하여 13주 동안의 평균 주가(즉, 4월 1일부터 6월 30일까지)입니다. 7월 1일의 값은 4월 2일부터 7월 1일까지의 평균 주가이고, 7월 2일의 값은 4월 3일부터 7월 2일까지의 평균 주가이며 이외의 날짜도 마찬가지입니다. 매일 윈도우는 가장 최근 날짜의 값을 이동 평균에 추가하고 가장 오래된 날짜의 값을 제거합니다. 이를 통해 일일 변동이 완화되고 추세를 보다 쉽게 이해할 수 있습니다.

이동 평균은 슬라이딩 윈도우 프레임을 사용하여 계산할 수 있습니다. 프레임의 행은 특정 너비를 갖습니다. 위의 주가 예에서 13주는 91일이므로 슬라이딩 윈도우는 91일이 됩니다. 하루에 1회(예: 장마감 시간) 측정하는 경우, 윈도우는 91개 행 “너비”가 됩니다.

91개 행 너비의 윈도우를 정의하려면,

SELECT AVG(price) OVER(ORDER BY timestamp1 ROWS BETWEEN 90 PRECEDING AND CURRENT ROW)
  FROM sales;
Copy

참고

최초 윈도우 프레임은 91일보다 짧을 수 있습니다. 예를 들어, 13주 이동 평균 주가를 살펴보는 경우를 가정해 보겠습니다. 주가가 4월 1일에 처음으로 생성된 경우, 4월 3일에는 단 3일의 주가 정보만 있으므로 윈도우의 너비는 3개 행에 불과합니다.

다음 예제는 샘플 2개를 포함하기에 충분한 슬라이딩 윈도우 프레임에 대한 합계의 결과를 보여줍니다.

SELECT MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
           AS SLIDING_SUM_QUANTITY
  FROM sales
  ORDER BY sales_date;
Copy

쿼리 결과에는 SLIDING_SUM_QUANTITY 열의 계산 방법을 보여주는 추가 설명이 포함됩니다.

+-----------+----------+----------------------+
| MONTH_NUM | QUANTITY | SLIDING_SUM_QUANTITY |
|-----------+----------+----------------------+
|         1 |        1 |                   1  |  -- sum = 1
|         1 |        3 |                   4  |  -- sum = 1 + 3
|         1 |        5 |                   8  |  -- sum = 3 + 5 (1 is no longer in the window)
|         2 |        2 |                   7  |  -- sum = 5 + 2 (3 is no longer in the window)
+-----------+----------+----------------------+
Copy

“슬라이딩 윈도우” 기능은 윈도우 프레임에 들어오고 나가는 행의 순서에 따라 함수가 달라지므로 ORDER BY 절이 필요합니다.

PARTITION BY 및 ORDER BY 절을 사용하여 합계 실행하기

PARTITION BY 및 ORDER BY 절을 함께 사용하면 파티션 내의 누적 합계를 얻을 수 있습니다. 이 예에서 파티션은 1개월이며, 합계는 파티션 내에만 적용되므로 새로운 달이 시작되면 합계가 0 으로 재설정됩니다.

SELECT MONTH(sales_date) AS MONTH_NUM,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) ORDER BY sales_date)
          AS MONTHLY_CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

쿼리 결과에는 MONTHLY_CUMULATIVE_SUM_QUANTITY 열의 계산 방법을 보여주는 추가 설명이 포함됩니다.

+-----------+---------------------------------+
| MONTH_NUM | MONTHLY_CUMULATIVE_SUM_QUANTITY |
|-----------+---------------------------------+
|         1 |                               1 |  -- sum = 1
|         1 |                               4 |  -- sum = 1 + 3
|         1 |                               9 |  -- sum = 1 + 3 + 5
|         2 |                               2 |  -- sum = 0 + 2 (new month)
+-----------+---------------------------------+
Copy

파티션과 슬라이딩 윈도우 프레임을 함께 사용할 수도 있습니다. 아래 예에서 슬라이딩 윈도우는 2개 행 너비이지만, 새 파티션(즉, 새로운 달)에 도달할 때마다 슬라이딩 윈도우는 해당 파티션의 첫 번째 행에서만 시작됩니다.

SELECT
       MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) 
                           ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
         AS MONTHLY_SLIDING_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;
Copy

쿼리 결과에는 MONTHLY_SLIDING_SUM_QUANTITY 열의 계산 방법을 보여주는 추가 설명이 포함됩니다.

+-----------+----------+------------------------------+
| MONTH_NUM | QUANTITY | MONTHLY_SLIDING_SUM_QUANTITY |
|-----------+----------+------------------------------+
|         1 |        1 |                           1  |  -- sum = 1
|         1 |        3 |                           4  |  -- sum = 1 + 3
|         1 |        5 |                           8  |  -- sum = 3 + 5
|         2 |        2 |                           2  |  -- sum = 0 + 2 (new month)
+-----------+----------+------------------------------+
Copy

값의 합에 대한 값의 비율 계산

RATIO_TO_REPORT 함수를 사용하여 파티션의 값 합계에 대한 값의 비율을 계산한 다음 그 비율을 해당 합계의 백분율로 반환할 수 있습니다. 이 함수는 현재 행의 값을 파티션의 모든 행에 있는 값의 합으로 나눕니다.

SELECT branch_ID,
       city,
       100 * RATIO_TO_REPORT(net_profit) OVER (PARTITION BY city)
    FROM store_sales AS s1
    ORDER BY city, branch_ID;
+-----------+-----------+------------------------------------------------------------+
| BRANCH_ID | CITY      | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER (PARTITION BY CITY) |
|-----------+-----------+------------------------------------------------------------|
|         3 | Montreal  |                                                52.63157900 |
|         4 | Montreal  |                                                47.36842100 |
|         1 | Vancouver |                                                40.00000000 |
|         2 | Vancouver |                                                60.00000000 |
+-----------+-----------+------------------------------------------------------------+
Copy

PARTITION BY 절은 city 열의 파티션을 정의합니다. 특정 도시 내의 매장만이 아니라 전체 체인 대비 수익 비율을 살펴보려면, 다음과 같이 PARTITION BY 절을 생략합니다.

SELECT branch_ID,
       100 * RATIO_TO_REPORT(net_profit) OVER ()
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+-------------------------------------------+
| BRANCH_ID | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER () |
|-----------+-------------------------------------------|
|         1 |                               22.72727300 |
|         2 |                               34.09090900 |
|         3 |                               22.72727300 |
|         4 |                               20.45454500 |
+-----------+-------------------------------------------+
Copy
언어: 한국어