윈도우 함수 사용하기

윈도우 함수는 창에서 동작하며, 관련이 있는 행의 그룹(예: 날짜별 또는 위치별)입니다. 이 항목에서는 다음과 같이 Snowflake에서 지원하는 다양한 윈도우 함수 타입의 사용법에 대해 설명합니다.

  • 일반 윈도우 함수.

  • 순위(예: 가장 높음, 두 번째로 높음 등)를 계산하는 윈도우 함수.

  • 누적 윈도우 프레임 및 슬라이딩 윈도우 프레임을 지원하는 윈도우 기능.

이 설명서는 윈도우 함수에 아직 능숙하지 않은 사용자를 대상으로 합니다. 이러한 함수에 능숙한 사용자에게는 다음 참고 자료가 유용할 수 있습니다.

이 항목의 내용:

윈도우 개념

윈도우 는 행의 그룹입니다. 윈도우에는 0개, 1개 또는 여러 개의 행이 포함될 수 있습니다. 그렇지만 단순한 설명을 위해 윈도우에는 《여러 개의 행》이 포함되는 것으로 생각하도록 하겠습니다.

윈도우의 모든 행은 위치(예: 모두 동일한 도시) 또는 시간(예: 모두 동일한 회계 연도)과 같이 어떤 방식으로든 관련되어 있습니다.

윈도우를 사용하는 함수를 윈도우 함수 라고 합니다.

윈도우 함수로 분류되는 함수는 다음과 같이 스칼라 함수와 다른 타입의 질문에 답변을 제공하는 데 도움이 됩니다.

  • 스칼라 함수를 사용하는 쿼리는 해당 행의 데이터만 사용하여 단일 행에 대한 질문에 답합니다.

  • 윈도우 함수를 사용하는 쿼리는 동일 윈도우에 있는 한 행과 다른 행 의 관계에 대한 질문에 답합니다.

예를 들어, 5개 매장 체인의 한 지점을 관리하는 상황을 가정해 보겠습니다. 내가 운영 중인 매장의 수익을 계산하려면 해당 매장의 수익 및 비용과 같이 특정 매장에 대한 정보만 계산하면 됩니다. 이러한 쿼리에서는 스칼라 함수를 사용할 수 있습니다.

내 매장의 수익을 다른 매장과 비교 하려면 계산할 때 내 매장뿐만 아니라 다른 매장에 대한 정보도 확인해야 합니다. 이러한 쿼리에서 윈도우 함수를 사용할 수 있습니다.

윈도우 함수에서는 다음과 같은 두 개의 인자가 사용되는 것으로 생각할 수 있습니다. 첫 번째 인자는 계산에 사용할 열 또는 식(예: 수익 또는 이익)입니다. 두 번째 인자는 윈도우(즉, 비교하기 위해 사용된 행 그룹)을 정의하며, 윈도우에는 현재 행(내 매장)과 비교할 다른 행(동일한 체인의 다른 매장)이 모두 포함됩니다.

전체 매장 체인 수익에서 내 매장이 차지하는 비율을 계산하려면 전체 매장의 총 수익(지점 수익/체인 수익)을 내 매장의 수익으로 나눕니다.

윈도우 기능의 설명에 대한 이해를 돕기 위해, 이 항목에서는 윈도우 함수가 있는 경우와 없는 경우에 회사 수익에 대한 지점의 수익 백분율을 계산하는 방법을 설명합니다.

다음 예는 윈도우 함수를 사용하지 않고 체인 수익에 대한 매장 수익의 백분율을 계산하는 한 가지 방법을 보여줍니다.

먼저 테이블을 생성하고 데이터를 로드하며 각 매장의 수익을 계산해 보겠습니다.

CREATE TRANSIENT TABLE store_sales (
    branch_ID    INTEGER,
    city        VARCHAR,
    gross_sales NUMERIC(9, 2),
    gross_costs NUMERIC(9, 2),
    net_profit  NUMERIC(9, 2)
    );

INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
    VALUES
    (1, 'Vancouver', 110000, 100000),
    (2, 'Vancouver', 140000, 125000),
    (3, 'Montreal', 150000, 140000),
    (4, 'Montreal', 155000, 146000);

UPDATE store_sales SET net_profit = gross_sales - gross_costs;

이제 체인의 총 수익에 대한 각 매장의 백분율을 표시합니다.

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales) AS chain_profit,
       store_profit / chain_profit * 100 AS store_percentage_of_chain_profit
     FROM store_sales
     ORDER BY branch_ID;
+-----------+--------------+--------------+----------------------------------+
| BRANCH_ID | STORE_PROFIT | CHAIN_PROFIT | STORE_PERCENTAGE_OF_CHAIN_PROFIT |
|-----------+--------------+--------------+----------------------------------|
|         1 |     10000.00 |     44000.00 |                      22.72727300 |
|         2 |     15000.00 |     44000.00 |                      34.09090900 |
|         3 |     10000.00 |     44000.00 |                      22.72727300 |
|         4 |      9000.00 |     44000.00 |                      20.45454500 |
+-----------+--------------+--------------+----------------------------------+

해당 도시에 속한 모든 매장에 대한 백분율로 각 매장의 수익을 표시하는 세부 보고서를 확인하려면 아래 쿼리를 사용합니다.

SELECT branch_ID,
       net_profit AS store_profit,
       (SELECT SUM(net_profit) FROM store_sales AS s2 WHERE s2.city = s1.city) AS city_profit,
       store_profit / city_profit * 100 AS store_percentage_of_city_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+--------------+-------------+---------------------------------+
| BRANCH_ID | STORE_PROFIT | CITY_PROFIT | STORE_PERCENTAGE_OF_CITY_PROFIT |
|-----------+--------------+-------------+---------------------------------|
|         1 |     10000.00 |    25000.00 |                     40.00000000 |
|         2 |     15000.00 |    25000.00 |                     60.00000000 |
|         3 |     10000.00 |    19000.00 |                     52.63157900 |
|         4 |      9000.00 |    19000.00 |                     47.36842100 |
+-----------+--------------+-------------+---------------------------------+

내 매장의 수익을 전체 매장 수익의 합계로 나누는(또는 예를 들어 같은 도시의 모든 매장과 같이 지정된 매장 그룹의 수익 합계로 나누기) 함수가 있다면 유용할 것입니다. 그러한 함수는 2개의 인자를 가지며, 하나는 계산을 수행하기 위한 열이고 다른 하나는 비교 대상이 되는 행을 지정하는 열입니다. 두 번째 열은 WHERE 절과 유사할 수 있습니다. 그러한 함수는 다음과 유사한 방식(예시는 유사 코드이며, 유효한 SQL이 아님)으로 사용할 수 있습니다.

SELECT branch_ID,
       PERCENTAGE(net_profit, <where_condition>)
    FROM store_sales;

이 함수는 현재 행(현재 매장)의 수익을 <where_조건> 조건을 충족하는 모든 매장 수익의 합계로 나눕니다.

예를 들어, 각 도시에 있는 각 매장에 대한 수익 비율을 계산하기 위한 의사 코드는 다음과 유사할 수 있습니다.

SELECT branch_ID,
       PERCENTAGE(net_profit, 'city')
     FROM store_sales;

SQL은 위와 같은 구문을 지원하지 않지만, 현재 행과 정의된 행 그룹을 기반으로 결과를 반환하는 윈도우 함수의 개념을 설명합니다.

Snowflake에는 이름이 PERCENTAGE 인 함수가 없지만, 현재 행의 값을 윈도우에 있는 모든 행 값의 합계로 나누는 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 |
+-----------+-----------+------------------------------------------------------------+

OVER() 절은 계산에서 사용되는 행 그룹을 정의합니다. 그리고 이전 PERCENTAGE 함수에서 가상의 두 번째 인자(<where_조건>)와 동일한 역할을 합니다.

PARTITION BY 하위 절을 통해 해당 윈도우를 하위 윈도우로 나눌 수 있으며, 이 경우에는 도시당 하나로 나눕니다. (문에서 PARTITION BY 절을 명시적으로 사용하지 않으면, 윈도우 함수는 전체 입력에 대한 단일 윈도우로 동작합니다.)

특정 도시 내의 매장만이 아니라 전체 체인 대비 수익 비율을 살펴보려면, 다음과 같이 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 |
+-----------+-------------------------------------------+

순서가 중요한 윈도우 함수

이전 쿼리의 결과에는 PARTITION BY 하위 절에서 선택한 행의 순서가 적용되지 않습니다. 내 매장이 회사 이익에서 차지하는 비율은 쿼리가 다른 매장의 데이터를 읽는 순서에 따라 달라지지 않습니다.

그러나 일부 쿼리의 경우 순서가 중요합니다. 순서가 중요한 윈도우 함수의 타입은 다음의 2가지입니다.

  • 순위 관련 윈도우 함수

  • 윈도우 프레임 함수

일부 함수는 순위 관련 함수 및 윈도우 프레임 함수 모두에 해당합니다.

순서가 중요한 함수에서는 OVER 절의 ORDER BY 하위 절이 사용되며, 그러한 예는 다음과 같습니다.

select order_sensitive_function(column_name) over ( [partition by <col2>] order by <col3> )
...

ORDER BY 절은 ASC (오름차순) 또는 DESC (내림차순)와 함께 사용할 수 있습니다. 기본값은 오름차순입니다.

(OVER 절의 ORDER BY 하위 절은 쿼리의 최종 결과를 정렬하는 ORDER BY 절과 구분됩니다.)

윈도우 프레임 함수

윈도우 프레임 은 윈도우에서 행의 하위 세트입니다.

윈도우 프레임 함수에서는 윈도우 프레임을 사용하여 이동 평균과 같은 항목을 계산합니다.

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

  • 누적.

  • 슬라이딩.

누적 윈도우 프레임

누적 윈도우는 고정점에서 시작하여 각 새 행에 따라 증가하는 가변 너비 윈도우입니다. 예를 들어, 《이번 달의 현재까지 매출》은 월의 1일에 시작하여 새 날짜의 데이터가 추가될 때마다 증가하는 누적 윈도우를 사용하여 계산됩니다.

+--------+-------+--------------+
| Day of | Sales | Sales So Far |
| Month  | Today | This Month   |
|--------+-------+--------------+
|      1 |    10 |           10 |
|      2 |    10 |           20 |
|      3 |    10 |           30 |
|    ... |   ... |          ... |
+--------+-------+--------------+

누적 윈도우는 일정한 간격으로 0에서 시작하는 경우가 많습니다. 예를 들어, 위의 테이블이 2월의 매출이라면 3월 1일에는 윈도우 프레임의 시작점이 3월 1일이 되고 해당 월의 현재까지 매출이 0으로 재설정된 후 3월 1일부터 계산이 시작됩니다.

슬라이딩 윈도우 프레임

슬라이딩 윈도우 프레임은 자동차가 펜스를 따라 주행할 때 자동차의 사이드 윈도우를 볼 때를 생각하면 이해하기가 쉽습니다. 펜스 기둥의 너비가 균등하고 윈도우의 너비가 기둥 사이 거리의 정수배이면, 눈에 보이는 펜스 기둥의 수는 일정하게 유지됩니다. 그러나 계속 운전을 하여 《기존》 펜스 기둥이 시야에서 사라지고 《새》 펜스 기둥이 시야에 나타나게 되면, 항상 보이는 펜스 기둥의 수는 동일하지만 시간에 지남에 따라 동일한 펜스 기둥이 보이지 않게 됩니다.

슬라이딩 윈도우 프레임은 윈도우의 행을 따라 《슬라이딩》하는 고정 너비 프레임으로 매번 윈도우의 다른 조각이 표시됩니다. 자동차를 운전하여 펜스 기둥을 지날 때와 마찬가지로 윈도우 프레임은 데이터를 따라 이동하며, 기존 행이 프레임에서 사라지고 새 행이 나타나므로 프레임의 너비(프레임에서 행의 수)는 항상 동일합니다.

슬라이딩 윈도우는 이동 평균을 계산하기 위해 사용되는 경우가 많습니다. 이동 평균은 고정 크기 간격(예: 일수)을 기준으로 계산되는 평균입니다. 간격의 크기가 일정하지만, 윈도우가 슬라이딩함에 따라 시간이 지나면서(또는 다른 요인에 따라) 간격의 실제 값이 변합니다.

예를 들어, 주식 시장 애널리스트는 주가의 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일(포함)까지의 평균 주가를 반환합니다.

참고

슬라이딩 윈도우는 고정 너비이지만, 윈도우가 새 데이터 소스에 처음으로 적용되면 데이터 소스에 윈도우를 채우기 위한 충분한 데이터가 없을 수 있습니다. 예를 들어, 4월 1일에 레코드를 기록하기 시작하고 4월 1일부터 6월 29일까지 기록하면 슬라이딩 윈도우에 91일 미만의 데이터가 포함됩니다. 윈도우의 너비는 윈도우 채우기가 완료된 이후에만 일정하게 유지됩니다.

아래 예에서는 해당 월의 첫 7일 동안 작은(3일) 슬라이딩 윈도우를 사용합니다. 이는 윈도우 프레임이 윈도우 전체에서 슬라이딩하는 방법을 설명하며, 윈도우 내에서 합계를 계산하기 위해 최근 3개 값을 항상 유지합니다. 이 설명에서는 기간의 시작 시 윈도우가 가득 차지 않을 수 있음을 고려합니다.

3-day sliding window frame in 7-day window

그리고 해당 테이블에서 볼 수 있듯이 마지막 열에는 최근 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 |
|    ... |   ... |           ... |
+--------+-------+---------------+

순서가 중요한 윈도우 함수에 대한 자세한 정보

PARTITION BY 및 ORDER BY 절은 독립적입니다. ORDER BY 절은 PARTITION BY 절 없이도 사용할 수 있습니다. 도시 내의 다른 매장뿐만 아니라 체인 내 다른 모든 매장과 비교하여 매장의 순위를 매기려면 아래 쿼리를 사용합니다.

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

참고

OVER 절 내의 ORDER BY 하위절은 윈도우 함수가 행을 처리하는 순서만을 제어하며, 이 절은 전체 쿼리의 출력을 제어하지 않습니다. 쿼리의 출력 순서를 제어하기 위해서도 쿼리의 최상위 수준에서 외부 ORDER BY 절이 필요합니다. 이러한 ORDER BY 절은 독립적이며 상호 배타적이지 않습니다. 다음 쿼리에서는 첫 번째 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;

일부 윈도우 함수에서는 순서가 중요하며 그렇지 않은 함수도 있습니다.

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

  • 일부 윈도우 함수에서는 ORDER BY 절을 사용할 수 없습니다.

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

  • 일부 윈도우 함수에서는 ORDER BY 절을 암시적 누적 윈도우 프레임 절로 취급합니다. (암시적 윈도우 프레임에 대한 자세한 내용은 윈도우 프레임 사용법 노트 에서 확인할 수 있습니다.)

조심

대부분의 경우 SQL은 명시적 언어로, 사용자의 명령을 그대로 수행합니다. 암시적인 절, 즉, 한 절이 표시되지 않는 다른 절을 암시적으로 생성하는 절은 거의 없습니다. 일부 윈도우 함수의 경우, ORDER BY 절이 추가 절(누적 윈도우 프레임 절)을 암시적으로 추가합니다. 이를 통해 동작이 변경됩니다. 암시적인 윈도우 프레임 절에 대한 자세한 정보는 윈도우 프레임 사용법 노트 에서 확인할 수 있습니다.

명시적이 아닌 암시적인 동작으로 인해 이해하기 어려운 결과가 발생할 수 있으므로, Snowflake는 암시적 윈도우 프레임을 방지하는 것을 권장합니다. 대신, 모든 윈도우 프레임이 명시적이어야 합니다.

다음 다이어그램은 윈도우 함수, 윈도우 프레임 함수 및 순위 관련 함수 사이의 관계를 보여줍니다.

                      Window Functions

  Order-insensitive Functions      Order-sensitive Functions
+-----------------------------+--------------------------------------------------------------------+
|                             | Rank-related Functions                                             |
|                             +--------------------------------------------------------------------+
|                             | Functions that can be both Rank-related and Window Frame functions |
|                             +--------------------------------------------------------------------+
|                             | Window Frame Functions                                             |
+-----------------------------+--------------------------------------------------------------------+

위에서 볼 수 있는 바와 같이,

  • 일부 윈도 함수에서는 순서가 중요하지 않습니다.

  • 일부 윈도우 함수에서는 순서를 구분합니다.

  • 순서가 중요한 윈도우 프레임은 다음의 두 카테고리로 나뉩니다.

    • 순위 관련 함수

    • 윈도우 프레임 함수

  • 순위가 중요한 일부 함수는 순위 관련 함수 및 윈도우 프레임 함수 모두에 해당합니다.

윈도우 함수 vs 집계 함수

여러 윈도우 함수 및 집계 함수 는 이름이 같습니다. 예를 들어, SUM() 윈도우 함수와 SUM() 집계 함수가 있습니다.

이러한 두 함수의 사용법을 구분하려면 다음을 기억해야 합니다.

  • 집계 함수의 경우 입력은 여러 행이고 출력은 1개 행입니다.

  • 윈도우 함수의 경우, 입력은 행의 윈도우 및 해당 윈도우 내의 단일 행으로 2개이며 출력은 입력 행당 1개 행입니다.

집계 함수는 SUM 과 같이 연결된 모든 행에 대한 정보를 제공하지만, 개별 행에 대한 정보를 제공하지 않습니다.

윈도우 함수는 윈도우 내의 모든 다른 행과 비교하여 현재 행에 대한 정보를 제공합니다.

다음 SQL 문은 SUM() 집계 함수를 사용하는 경우(입력 행의 전체 그룹에 대하여 1개 행을 반환)와 SUM() 윈도우 함수를 사용하는 경우(입력 행 그룹에서 각 행에 대하여 1개 행 반환) 사이의 차이점을 보여줍니다.

SELECT SUM(net_profit) 
    FROM store_sales;
+-----------------+
| SUM(NET_PROFIT) |
|-----------------|
|        44000.00 |
+-----------------+
SELECT branch_ID, SUM(net_profit) OVER ()
    FROM store_sales
    ORDER BY branch_id;
+-----------+-------------------------+
| BRANCH_ID | SUM(NET_PROFIT) OVER () |
|-----------+-------------------------|
|         1 |                44000.00 |
|         2 |                44000.00 |
|         3 |                44000.00 |
|         4 |                44000.00 |
+-----------+-------------------------+

윈도우 함수 호출하기

이 섹션에서는 윈도우 함수 호출 구문에 대한 자세한 정보를 제공합니다.

윈도우 및 윈도우 프레임은 OVER 절을 사용하여 지정합니다.

... OVER ( [ PARTITION BY <expr1> ]
           [ ORDER BY <expr2> [ { cumulativeFrame | slidingFrame } ] ]
         ) ...

윈도우 프레임 구문에 대한 자세한 내용은 윈도우 함수 에서 제공됩니다. 이 항목에서는 윈도우 함수에 필요한 구문의 하위 세트, 특히 다음에 대해 집중적으로 설명합니다.

  • PARTITION BY 하위 세트는 데이터를 윈도우로 분할합니다. 예를 들어, 1개월 이상에 대한 월간 매출 누적 합계를 계산하려면, 데이터를 월별로 분할할 수 있습니다. 이를 통해 1월의 누적 매출 합계, 2월의 누적 매출 합계 등을 계산할 수 있습니다.

  • ORDER BY 하위 절은 주로 순위 관련 윈도우 함수 및 슬라이딩과 누적 윈도우 프레임 함수에서 사용되며, 각 윈도우 내에서 행의 순서를 결정합니다.

설명된 예

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

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

  • 일일 매출

  • 주간 순위 (예: 주간 매출 최고에서 최저 순위)

    여기에서는 순위 관련 윈도우 함수 (RANK)를 사용합니다.

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

    여기서는 윈도우 함수 (SUM)와 함께 누적 윈도우 프레임 을 사용합니다.

  • 이번 주 매출 총계

    여기서는 SUM 을 단순 윈도우 함수로 사용합니다.

  • 3일 이동 평균 (예: 오늘 및 이전 2일의 평균)

    여기서는 (AVG)를 슬라이딩 윈도우 프레임 이 포함된 윈도우 함수 로 사용합니다.

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

+--------+-------+------+--------------+-------------+--------------+
| 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 ...

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

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

매출 순위 계산

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

기간에는 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 |
+-----+-------------+------------------------+

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

이번주 총 매출 계산

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

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

이 슬라이딩 윈도우 프레임과 앞에서 설명한 누적 윈도우 프레임의 차이점은 시작점입니다.

  • 슬라이딩 윈도우 프레임에서 시작점은 오른쪽으로 슬라이딩됩니다.

  • 누적 윈도우 프레임에서 시작점은 고정되며 프레임이 진행되면 윈도우 내의 각 추가 행에 따라 누적됩니다.

요약

다음을 보여주는 쿼리의 최종 버전은 아래와 같습니다.

  • 순위.

  • 누적 윈도우 프레임.

  • 단순 윈도우 함수.

  • 슬라이딩 윈도우 함수.

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

추가 예

이 섹션에서는 윈도우 함수에서 윈도우 하위 절을 사용하는 더 자세한 예를 제공하며 이러한 하위 절이 함께 기능하는 방법을 보여줍니다.

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

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)
    ;

참고

이러한 여러 예에서는 2개 ORDER BY 절을 사용하며, 하나는 윈도우 절이고 다른 하나는 결과 세트를 순서대로 제공합니다. 이 항목의 목적을 위해 ORDER BY 절에 대한 참조는 일반적으로 윈도우 내의 절을 참조합니다.

PARTITION BY 절이 포함된 윈도우

윈도우 절의 PARTITION BY 하위 절은 입력 식의 값에 따라 데이터를 별도의 하위 세트으로 나누며, SELECT 문이 각 하위 세트에 적용되고 각 하위 세트에 대한 행이 출력됩니다.

이는 GROUP BY 절의 작동 방법과 유사하지만 같지는 않습니다.

다음 예는 각 월간 판매 수량을 보여주며, PARTITION BY 절을 사용하여 데이터를 1개월 하위 세트으로 나눕니다.

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date)) AS QUANTITY
    FROM sales
    ORDER BY sales_date;
+-----------+----------+
| MONTH_NUM | QUANTITY |
|-----------+----------|
|         1 |        9 |
|         1 |        9 |
|         1 |        9 |
|         2 |        2 |
+-----------+----------+

여기서 볼 수 있듯이 첫 번째 행 3개가 중복됩니다. 번호가 1인 달에는 입력 행이 3개이며 윈도우 함수는 각 입력 행에 대하여 1개의 출력 행을 생성하므로 출력에는 번호가 1인 달에 대한 결과 행이 3개입니다. SUM 함수는 누적 또는 슬라이딩 함수로 사용되지 않으며, 전체 윈도우에 적용되고 매번 전체 윈도우에 대한 동일한 값을 반환하므로, 위와 같이 중복 값을 반환합니다.

DISTINCT 키워드를 사용하면 중복을 제거할 수 있습니다.

SELECT DISTINCT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date))
    FROM sales
    ORDER BY MONTH_NUM;
+-----------+-----------------------------------------------------+
| MONTH_NUM | SUM(QUANTITY) OVER (PARTITION BY MONTH(SALES_DATE)) |
|-----------+-----------------------------------------------------|
|         1 |                                                   9 |
|         2 |                                                   2 |
+-----------+-----------------------------------------------------+

특히 이 경우에는 윈도우 절이 아닌 GROUP BY 절을 사용할 수 있습니다. 예:

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity)
    FROM sales
    GROUP BY MONTH_NUM
    ORDER BY MONTH_NUM;
+-----------+---------------+
| MONTH_NUM | SUM(QUANTITY) |
|-----------+---------------|
|         1 |             9 |
|         2 |             2 |
+-----------+---------------+

그러나 GROUP BY 는 윈도우만큼 유연하지 않습니다. 또한, 복잡한 쿼리에서는 윈도우 절 대신 GROUP BY 를 사용할 수 없는 경우가 있습니다.

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;

쿼리 결과에는 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
+-----------+----------+-------------------------+

슬라이딩 윈도우 프레임 예

재무 분야에서 애널리스트들은 《이동 평균》을 살펴보는 경우가 많습니다.

예를 들어, 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;

참고

윈도우가 시작할 때는 너비가 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;

쿼리 결과에는 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)
+-----------+----------+----------------------+

《슬라이딩 윈도우》 기능에는 ORDER BY 절이 필요하다는 점에 유의하십시오. 슬라이딩 윈도우는 반드시 행이 슬라이딩 윈도우에 들어오고 나가는 순서를 알아야 합니다.

PARTITION BY 및 ORDER BY 절이 포함된 윈도우

PARTITION BYORDER 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;

쿼리 결과에는 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)
+-----------+---------------------------------+

파티션과 슬라이딩 윈도우를 함께 사용할 수도 있습니다. 아래 예에서 슬라이딩 윈도우는 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;

쿼리 결과에는 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)
+-----------+----------+------------------------------+