윈도우 함수 사용하기¶

순위 관련 윈도우 함수¶

순서가 중요한 함수 중 가장 단순한 하위 카테고리는 순위 관련 함수 하위 카테고리입니다.

가장 단순한 순위 관련 함수는 RANK 함수입니다. 순위 함수는 단순하게 순위를 나열하며, 이는 행의 순서가 정해진 윈도우에서 행의 순서입니다. 윈도우에서 첫 번째 행은 순위가 1이며, 윈도우에서 두 번째 행은 순위가 2인 방식입니다. 아래 예는 매출을 기준으로 한 판매 인력의 순위 순서를 보여줍니다.

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

Copy

RANK 함수는 행을 정렬하지 않으며, RANK 를 호출하기 전 윈도우에서 행이 이미 순서대로 정렬되어야 합니다. 행을 정렬하려면 ORDER BY 절의 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

순위 관련 함수의 출력은 다음에 따라 다릅니다.

• 함수로 전달된 개별 행.

• 함수로 전달된 윈도우에서 다른 행의 값.

• 윈도우에서 모든 행의 순서.

Snowflake는 다양한 순위 관련 함수를 제공합니다. 이러한 함수의 이름 및 순위 관련 윈도우 함수의 구문에 대한 자세한 내용은 윈도우 함수 에서 확인할 수 있습니다.

윈도우 프레임 함수¶

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

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

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

• 누적.

• 슬라이딩.

슬라이딩 윈도우 프레임¶

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

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

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

예를 들어, 주식 시장 애널리스트는 주가의 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일 매출 데이터의 합계가 포함됩니다. 예를 들어, 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 |
|    ... |   ... |           ... |
+--------+-------+---------------+

Copy

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

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

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 절을 암시적 누적 윈도우 프레임 절로 취급합니다. (암시적 윈도우 프레임에 대한 자세한 내용은 윈도우 프레임 사용법 노트 에서 확인할 수 있습니다.)

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

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

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 절이 포함된 윈도우¶

윈도우 절의 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 |
+-----------+----------+

Copy

여기서 볼 수 있듯이 첫 번째 행 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 |
+-----------+-----------------------------------------------------+

Copy

특히 이 경우에는 윈도우 절이 아닌 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 |
+-----------+---------------+

Copy

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

ORDER BY 절이 포함된 윈도우¶

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

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

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

누적 및 슬라이딩 윈도우에 대한 설명은 아래에서 제공됩니다.

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