표준 SQL의 분석 함수 개념

분석 함수는 행 그룹의 값을 계산하고 행에 대해 하나의 결과를 반환합니다. 행 전체 그룹에 대해 하나의 결과를 반환하는 집계 함수와는 다릅니다.

평가 대상인 행 주변의 행 윈도우를 정의하는 OVER 절이 포함됩니다. 각 행에 대한 분석 함수 결과는 선택된 행 윈도우를 입력으로 사용하여 집계 방식으로 계산됩니다.

분석 함수를 사용하면 이동 평균, 항목의 순위, 누적 합계를 계산하고, 기타 분석을 수행할 수 있습니다.

분석 함수에는 탐색 함수, 번호 지정 함수, 집계 분석 함수 카테고리가 포함됩니다.

분석 함수 구문

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

표기법:

  • 대괄호 '[ ]'는 절(선택사항)을 의미합니다.
  • 괄호 '( )'는 리터럴 괄호를 의미합니다.
  • 세로 막대 '|'는 논리 OR을 의미합니다.
  • 중괄호 '{ }'는 옵션 조합을 묶는 데 사용됩니다.
  • 꺾쇠 괄호 '[, ...]' 안에 있는 쉼표 다음에 오는 줄임표는 앞의 항목이 쉼표로 구분된 목록으로 반복될 수 있음을 의미합니다.

설명

분석 함수는 행 그룹에 대한 결과를 계산합니다. 이러한 함수는 탐색 함수, 번호 지정 함수, 집계 분석 함수와 같은 분석 함수로 사용할 수 있습니다.

  • analytic_function_name: 분석 작업을 수행하는 함수입니다. 예를 들어 여기에 번호 지정 함수 RANK()를 사용할 수 있습니다.
  • argument_list: 분석 함수와 관련된 인수입니다. 인수가 포함된 함수도 있고, 그렇지 않은 함수도 있습니다.
  • OVER: OVER 앞의 분석 함수 구문에 필요한 키워드입니다.

참고

  • 분석 함수는 쿼리의 두 위치에 스칼라 표현식 피연산자로 표시될 수 있습니다.
    • SELECT 목록: 분석 함수가 SELECT 목록에 표시되면 해당 인수 목록과 OVER 절은 동일한 SELECT 목록에 도입된 별칭을 참조할 수 없습니다.
    • ORDER BY 절: 분석 함수가 쿼리의 ORDER BY 절에 표시되면 인수 목록이 SELECT 목록 별칭을 참조할 수 있습니다.
  • 분석 함수는 해당 인수 목록 또는 OVER 절의 다른 분석 함수를 참조할 수 없으며, 이는 별칭을 통한 간접적 참조라 해도 마찬가지입니다.
  • 분석 함수는 집계 후 평가됩니다. 예를 들어 GROUP BY 절과 비분석 집계 함수가 먼저 평가됩니다. 집계 함수는 분석 함수보다 먼저 평가되므로 분석 함수의 입력 피연산자로 사용할 수 있습니다.

반환

입력의 각 행에 대한 단일 결과입니다.

OVER 절 정의

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

설명

OVER 절은 분석 함수를 사용할 테이블의 행 그룹을 정의하는 윈도우를 참조합니다. 쿼리에 정의된 named_window를 제공하거나 새 윈도우의 사양을 정의할 수 있습니다.

참고

명명된 윈도우나 윈도우 사양이 제공되지 않으면 모든 행의 윈도우에 모든 입력 행이 포함됩니다.

OVER 절을 사용하는 예시

다음 쿼리는 윈도우 사양을 사용합니다.

이러한 쿼리는 명명된 윈도우를 사용합니다.

윈도우 사양 정의

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
  [ window_frame_clause ]

설명

윈도우의 사양을 정의합니다.

  • named_window: WINDOW로 정의된 기존 윈도우의 이름입니다.

  • PARTITION BY: 입력 행을 개별 파티션으로 분할하며 분석 함수는 이러한 개별 파티션을 대상으로 독립적으로 평가됩니다.

    • PARTITION BY 절에는 여러 개의 파티션 표현식이 허용됩니다.
    • 표현식에는 부동 소수점 유형, 그룹화할 수 없는 유형, 상수 또는 분석 함수가 포함될 수 없습니다.
    • 이 선택적 절을 사용하지 않으면 입력 테이블의 모든 행이 단일 파티션으로 구성됩니다.
  • ORDER BY: 파티션 내에서 행이 정렬되는 방식을 정의합니다. 이 절은 대부분의 경우에 선택사항이지만 탐색 함수 같은 일부 경우에는 필수입니다.

  • window_frame_clause: 집계 분석 함수에서 현재 파티션 내 윈도우 프레임을 정의합니다. 윈도우 프레임은 윈도우에 포함할 항목을 지정합니다. 이 절을 사용할 경우 완전히 경계가 없는 윈도우를 제외하고 ORDER BY가 필수입니다.

참고

  • ORDER BY 절이나 윈도우 프레임 절이 없는 경우 윈도우 프레임에 해당 파티션의 모든 행이 포함됩니다.
  • 집계 분석 함수의 경우 ORDER BY 절이 있지만 윈도우 프레임 절이 없으면 기본적으로 다음과 같은 윈도우 프레임 절이 사용됩니다.

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    

    예를 들어 다음 쿼리는 동일합니다.

    SELECT book, LAST_VALUE(item)
      OVER (ORDER BY year)
    FROM Library
    
    SELECT book, LAST_VALUE(item)
      OVER (
        ORDER BY year
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM Library
    

윈도우 사양에서 명명된 윈도우를 사용하기 위한 규칙

윈도우 사양에 명명된 윈도우를 사용하는 경우 다음 규칙이 적용됩니다.

  • 명명된 윈도우의 사양은 윈도우 사양 절에 정의된 새 사양으로 확장할 수 있습니다.
  • 중복된 정의를 가질 수 없습니다. 명명된 윈도우와 윈도우 사양 절에 ORDER BY 절이 있으면 오류가 발생합니다.
  • 절의 순서가 중요합니다. PARTITION BY가 먼저 오고 그 뒤에 ORDER BYwindow_frame_clause가 와야 합니다. 명명된 윈도우를 추가하면 윈도우 사양이 먼저 처리됩니다.

    --this works:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
    FROM Produce
    WINDOW item_window AS (ORDER BY purchases)
    
    --this does not work:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ORDER BY purchases) AS most_popular
    FROM Produce
    WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
    
  • 명명된 윈도우와 PARTITION BY는 윈도우 사양에 함께 표시될 수 없습니다. PARTITION BY가 필요한 경우 명명된 윈도우에 추가하세요.

  • ORDER BY 절, 외부 쿼리 또는 하위 쿼리에서 명명된 윈도우를 참조할 수 없습니다.

윈도우 사양을 사용하는 예시

다음 쿼리는 분석 함수에 파티션을 정의합니다.

이러한 쿼리는 윈도우 사양에 명명된 윈도우를 포함합니다.

다음 쿼리는 파티션에서 행이 정렬되는 방식을 정의합니다.

윈도우 프레임 절 정의

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

frame_between:
  {
    BETWEEN  unbounded_preceding AND frame_end_a
    | BETWEEN numeric_preceding AND frame_end_a
    | BETWEEN current_row AND frame_end_b
    | BETWEEN numeric_following AND frame_end_c

frame_start:
  { unbounded_preceding | numeric_preceding | [ current_row ] }

frame_end_a:
  { numeric_preceding | current_row | numeric_following | unbounded_following }

frame_end_b:
  { current_row | numeric_following | unbounded_following }

frame_end_c:
  { numeric_following | unbounded_following }

unbounded_preceding:
  UNBOUNDED PRECEDING

numeric_preceding:
  numeric_expression PRECEDING

unbounded_following:
  UNBOUNDED FOLLOWING

numeric_following:
  numeric_expression FOLLOWING

current_row:
  CURRENT ROW

윈도우 프레임 절은 분석 함수가 평가되는 파티션 내의 현재 행을 중심으로 윈도우 프레임을 정의합니다. 집계 분석 함수만 윈도우 프레임 절을 사용할 수 있습니다.

  • rows_range: 실제 행 또는 논리적 범위로 윈도우 프레임을 정의하는 절입니다.

    • ROWS: 현재 행의 실제 오프셋을 기준으로 윈도우 프레임을 계산합니다. 예를 들어 현재 행 앞뒤에 두 행을 포함할 수 있습니다.
    • RANGE: 현재 행의 ORDER BY 키 값에 따라 현재 행 주변의 논리적 행 범위를 기준으로 윈도우 프레임을 계산합니다. 제공된 범위 값은 현재 행의 키 값에 더하거나 빼서 윈도우 프레임의 시작 또는 끝 범위 경계를 정의합니다. 범위 기반 윈도우 프레임에서는 ORDER BY 절에 정확히 하나의 표현식이 있어야 하며, 표현식에 숫자 유형이 있어야 합니다.

      팁: 날짜가 있는 범위를 사용하려면 UNIX_DATE() 함수와 함께 ORDER BY를 사용합니다. 타임스탬프가 있는 범위를 사용하려면 UNIX_SECONDS(), UNIX_MILLIS() 또는 UNIX_MICROS() 함수를 사용합니다.

  • frame_between: 하한 및 상한 경계가 있는 윈도우 프레임을 만듭니다. 첫 번째 경계는 하한 경계를 나타냅니다. 두 번째 경계는 상한 경계를 나타냅니다. 위 구문에 표시된 것처럼 특정 경계 조합 만 사용할 수 있습니다.

    • 다음 경계를 사용하여 윈도우 프레임의 시작을 정의할 수 있습니다.
      • unbounded_preceding: 파티션의 시작 부분에서 윈도우 프레임이 시작됩니다.
      • numeric_preceding 또는 numeric_following: 윈도우 프레임의 시작은 현재 행을 기준으로 합니다.
      • current_row: 현재 프레임에서 윈도우 프레임이 시작됩니다.
    • frame_end_a ... frame_end_c: 윈도우 프레임의 끝을 정의합니다.
      • numeric_preceding 또는 numeric_following: 윈도우 프레임의 끝은 현재 행을 기준으로 합니다.
      • current_row: 현재 프레임에서 윈도우 프레임이 끝납니다.
      • unbounded_following: 파티션의 끝 부분에서 윈도우 프레임이 끝납니다.
  • frame_start: 하한 경계가 있는 윈도우 프레임을 만듭니다. 현재 행에서 윈도우 프레임이 끝납니다.

    • unbounded_preceding: 파티션의 시작 부분에서 윈도우 프레임이 시작됩니다.
    • numeric_preceding: 윈도우 프레임의 시작은 현재 행을 기준으로 합니다.
    • current_row: 현재 프레임에서 윈도우 프레임이 시작됩니다.
  • numeric_expression: 숫자 유형을 나타내는 표현식입니다. 숫자 표현식은 상수, 음수가 아닌 정수 또는 매개 변수여야 합니다.

참고

  • 경계가 파티션의 시작 또는 끝을 넘는 경우 윈도우 프레임에는 해당 파티션 내의 행만 포함됩니다.
  • 탐색 함수번호 지정 함수(예: RANK())에는 윈도우 프레임 절을 사용할 수 없습니다.

윈도우 프레임 절을 사용하는 예시

다음 쿼리는 ROWS가 있는 값을 계산합니다.

다음 쿼리는 RANGE가 있는 값을 계산합니다.

다음 쿼리는 부분적으로 또는 완전히 경계가 없는 윈도우가 있는 값을 계산합니다.

다음 쿼리는 숫자 경계가 있는 값을 계산합니다.

다음 쿼리는 현재 행이 경계인 값을 계산합니다.

명명된 윈도우 참조

SELECT query_expr,
  analytic_function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ ASC | DESC [, ...] ]
  [ window_frame_clause ]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

명명된 윈도우는 분석 함수를 사용할 테이블의 행 그룹을 나타냅니다. 명명된 기간은 WINDOW에 정의되며 분석 함수의 OVER에서 참조됩니다. OVER 절에서 명명된 윈도우는 단독으로 또는 윈도우 사양 내에 삽입된 상태로 표시될 수 있습니다.

예시

탐색 함수는 일반적으로 현재 행의 윈도우 프레임에 있는 다른 행을 대상으로 몇 가지 value_expression을 계산합니다. OVER 절 구문은 탐색 함수별로 다릅니다.

OVER 절의 요구사항:

  • PARTITION BY: 선택사항.
  • ORDER BY:
    1. PERCENTILE_CONTPERCENTILE_DISC에서 허용되지 않습니다.
    2. FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD, LAG에서 필수입니다.
  • window_frame_clause:
    1. PERCENTILE_CONT, PERCENTILE_DISC, LEAD, LAG에서 허용되지 않습니다.
    2. FIRST_VALUE, LAST_VALUE, NTH_VALUE에서 선택사항입니다.

모든 탐색 함수에서 결과 데이터 유형은 value_expression과 동일한 유형입니다.

번호 지정 함수 개념

번호 지정 함수는 지정된 윈도우 내의 위치를 기준으로 각 행에 정수 값을 할당합니다.

RANK(), DENSE_RANK(), ROW_NUMBER()의 예시:

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

+---------------------------------------------------+
| x          | rank       | dense_rank | row_num    |
+---------------------------------------------------+
| 1          | 1          | 1          | 1          |
| 2          | 2          | 2          | 2          |
| 2          | 2          | 2          | 3          |
| 5          | 4          | 3          | 4          |
| 8          | 5          | 4          | 5          |
| 10         | 6          | 5          | 6          |
| 10         | 6          | 5          | 7          |
+---------------------------------------------------+
  • RANK():RANK()는 이전 윈도우 정렬 그룹의 피어 수만큼 증가하므로 x=5의 경우 rank는 4를 반환합니다.
  • DENSE_RANK(): DENSE_RANK()는 항상 1만큼 증가하고 값을 건너뛰지 않으므로 x=5의 경우 dense_rank는 3을 반환합니다.
  • ROW_NUMBER():x=5의 경우 row_num은 4를 반환합니다.

집계 분석 함수 개념

집계 함수는 값 집합에서 계산을 수행하는 함수입니다. 대부분의 집계 함수는 분석 함수에서 사용할 수 있습니다. 이러한 집계 함수를 집계 분석 함수라고 합니다.

집계 분석 함수에서 OVER 절은 단순히 집계 함수 호출에 추가됩니다. 그 외의 함수 호출 구문은 변경되지 않습니다. 이러한 분석 함수는 집계 함수와 마찬가지로 집계를 수행하지만 구체적으로 각 행의 관련 윈도우 프레임을 대상으로 집계합니다. 이러한 분석 함수의 결과 데이터 유형은 집계 함수와 동일합니다.

분석 함수 예시

이 예시에서 강조 표시된 항목은 현재 행입니다. 굵게 표시된 항목은 분석에 포함된 행입니다.

예시에 사용된 공통 테이블

후속 집계 분석 쿼리 예시에서는 Produce, Employees, Farm 테이블이 사용됩니다.

테이블 생성

일부 예시에서는 Produce라는 테이블을 참조합니다.

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce

+-------------------------------------+
| item      | category   | purchases  |
+-------------------------------------+
| kale      | vegetable  | 23         |
| orange    | fruit      | 2          |
| cabbage   | vegetable  | 9          |
| apple     | fruit      | 8          |
| leek      | vegetable  | 2          |
| lettuce   | vegetable  | 10         |
+-------------------------------------+

직원 테이블

일부 예시에서는 Employees라는 테이블을 참조합니다.

WITH Employees AS
 (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
  UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
  UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
  UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
  UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
  UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees

+-------------------------------------+
| name      | department | start_date |
+-------------------------------------+
| Isabella  | 2          | 1997-09-28 |
| Anthony   | 1          | 1995-11-29 |
| Daniel    | 2          | 2004-06-24 |
| Andrew    | 1          | 1999-01-23 |
| Jacob     | 1          | 1990-07-11 |
| Jose      | 2          | 2013-03-17 |
+-------------------------------------+

팜 테이블

일부 예시에서는 Farm이라는 테이블을 참조합니다.

WITH Farm AS
 (SELECT 'cat' as animal, 23 as population, 'mammal' as category
  UNION ALL SELECT 'duck', 3, 'bird'
  UNION ALL SELECT 'dog', 2, 'mammal'
  UNION ALL SELECT 'goose', 1, 'bird'
  UNION ALL SELECT 'ox', 2, 'mammal'
  UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm

+-------------------------------------+
| animal    | category   | population |
+-------------------------------------+
| cat       | mammal     | 23         |
| duck      | bird       | 3          |
| dog       | mammal     | 2          |
| goose     | bird       | 1          |
| ox        | mammal     | 2          |
| goat      | mammal     | 2          |
+-------------------------------------+

총 합계 계산

이는 Produce 테이블의 모든 항목에 대한 총 합계를 계산합니다.

  • (orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 54 total purchases
SELECT item, purchases, category, SUM(purchases)
  OVER () AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 54              |
| leek      | 2          | vegetable  | 54              |
| apple     | 8          | fruit      | 54              |
| cabbage   | 9          | vegetable  | 54              |
| lettuce   | 10         | vegetable  | 54              |
| kale      | 23         | vegetable  | 54              |
+-------------------------------------------------------+

소계 계산

이는 Produce 테이블의 각 카테고리에 대한 소계를 계산합니다.

  • fruit
    • (orange, apple) = 10 total purchases
    • (orange, apple) = 10 total purchases
  • vegetable
    • (leek, cabbage, lettuce, kale) = 44 total purchases
    • (leek, cabbage, lettuce, kale) = 44 total purchases
    • (leek, cabbage, lettuce, kale) = 44 total purchases
    • (leek, cabbage, lettuce, kale) = 44 total purchases
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 10              |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 44              |
| cabbage   | 9          | vegetable  | 44              |
| lettuce   | 10         | vegetable  | 44              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

누적 합계 계산

이는 Produce 테이블의 각 카테고리에 대한 누적 합계를 계산합니다. 합계는 ORDER BY 절을 사용하여 정의된 순서를 기준으로 계산됩니다.

  • (orange, apple, leek, cabbage, lettuce, kale) = 2 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 10 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 2 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 11 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 21 total purchases
  • (orange, apple, leek, cabbage, lettuce, kale) = 44 total purchases
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

다음은 위의 예시와 동일하게 작동합니다. 가독성을 높이려는 경우가 아니라면 CURRENT ROW를 경계로 추가할 필요가 없습니다.

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS UNBOUNDED PRECEDING
  ) AS total_purchases
FROM Produce

이 예시에서는 Produce 테이블의 모든 항목이 파티션에 포함되어 있습니다. 이전 행만 분석됩니다. 파티션의 현재 행 앞에 있는 두 행에서 분석이 시작됩니다.

  • (orange, leek, apple, cabbage, lettuce, kale) = NULL
  • (orange, leek, apple, cabbage, lettuce, kale) = NULL
  • (orange, leek, apple, cabbage, lettuce, kale) = 2
  • (orange, leek, apple, cabbage, lettuce, kale) = 4
  • (orange, leek, apple, cabbage, lettuce, kale) = 12
  • (orange, leek, apple, cabbage, lettuce, kale) = 21
SELECT item, purchases, category, SUM(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) AS total_purchases
FROM Produce;

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | NULL            |
| leek      | 2          | vegetable  | NULL            |
| apple     | 8          | fruit      | 2               |
| cabbage   | 9          | vegetable  | 4               |
| lettuce   | 10         | vegetable  | 12              |
| kale      | 23         | vegetable  | 21              |
+-------------------------------------------------------+

이동 평균 계산

이는 Produce 테이블의 이동 평균을 계산합니다. 하한 경계는 현재 행 앞 1개 행입니다. 상한 경계는 현재 행 뒤 1개 행입니다.

  • (orange, leek, apple, cabbage, lettuce, kale) = 2 average purchases
  • (orange, leek, apple, cabbage, lettuce, kale) = 4 average purchases
  • (orange, leek, apple, cabbage, lettuce, kale) = 6.3333 average purchases
  • (orange, leek, apple, cabbage, lettuce, kale) = 9 average purchases
  • (orange, leek, apple, cabbage, lettuce, kale) = 14 average purchases
  • (orange, leek, apple, cabbage, lettuce, kale) = 16.5 average purchases
SELECT item, purchases, category, AVG(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | avg_purchases   |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| leek      | 2          | vegetable  | 4               |
| apple     | 8          | fruit      | 6.33333         |
| cabbage   | 9          | vegetable  | 9               |
| lettuce   | 10         | vegetable  | 14              |
| kale      | 23         | vegetable  | 16.5            |
+-------------------------------------------------------+

범위 내의 항목 수 계산

이 예시에서는 Farm 테이블에서 개체수가 비슷한 동물의 수를 가져옵니다.

  • (goose, dog, ox, goat, duck, cat) = 4 animals between population range 0-2.
  • (goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
  • (goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
  • (goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
  • (goose, dog, ox, goat, duck, cat) = 4 animals between population range 2-4.
  • (goose, dog, ox, goat, duck, cat) = 1 animal between population range 22-24.
SELECT animal, population, category, COUNT(*)
  OVER (
    ORDER BY population
    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS similar_population
FROM Farm;

+----------------------------------------------------------+
| animal    | population | category   | similar_population |
+----------------------------------------------------------+
| goose     | 1          | bird       | 4                  |
| dog       | 2          | mammal     | 5                  |
| ox        | 2          | mammal     | 5                  |
| goat      | 2          | mammal     | 5                  |
| duck      | 3          | bird       | 4                  |
| cat       | 23         | mammal     | 1                  |
+----------------------------------------------------------+

이 예시에서는 각 카테고리에서 가장 인기 있는 항목을 가져옵니다. 윈도우에 있는 행의 파티션을 나누고 각 파티션에서 정렬하는 방식을 정의합니다. Produce 테이블이 참조됩니다.

  • fruit
    • (orange, apple) = apple is most popular
    • (orange, apple) = apple is most popular
  • vegetable
    • (leek, cabbage, lettuce, kale) = kale is most popular
    • (leek, cabbage, lettuce, kale) = kale is most popular
    • (leek, cabbage, lettuce, kale) = kale is most popular
    • (leek, cabbage, lettuce, kale) = kale is most popular
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | kale         |
| cabbage   | 9          | vegetable  | kale         |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

범위의 마지막 값 가져오기

이 예시에서는 Produce 테이블을 사용하여 특정 윈도우 프레임에서 가장 인기 있는 항목을 가져옵니다. 윈도우 프레임은 한 번에 최대 3개의 행을 분석합니다. 채소에 대한 most_popular 열을 자세히 살펴보세요. 특정 카테고리에서 가장 인기 있는 항목을 가져오는 대신 해당 카테고리의 특정 범위에서 가장 인기 있는 항목을 가져옵니다.

  • fruit
    • (orange, apple) = apple is most popular
    • (orange, apple) = apple is most popular
  • vegetable
    • (leek, cabbage, lettuce, kale) = leek is most popular
    • (leek, cabbage, lettuce, kale) = lettuce is most popular
    • (leek, cabbage, lettuce, kale) = kale is most popular
    • (leek, cabbage, lettuce, kale) = kale is most popular
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | cabbage      |
| cabbage   | 9          | vegetable  | lettuce      |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

이 예시는 위와 동일한 결과를 반환하지만 item_window라는 명명된 윈도우를 포함합니다. 일부 윈도우 사양은 OVER 절에 직접 정의되고 일부는 명명된 윈도우에 정의됩니다.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases)

순위 계산

이 예시에서는 시작일을 기준으로 부서 내 각 직원의 순위를 계산합니다. 윈도우 사양은 OVER 절에 직접 정의됩니다. Employees 테이블이 참조됩니다.

  • department 1
    • (Jacob, Anthony, Andrew) = Assign rank 1 to Jacob
    • (Jacob, Anthony, Andrew) = Assign rank 2 to Anthony
    • (Jacob, Anthony, Andrew) = Assign rank 3 to Andrew
  • department 2
    • (Isabella, Daniel, Jose) = Assign rank 1 to Isabella
    • (Isabella, Daniel, Jose) = Assign rank 2 to Daniel
    • (Isabella, Daniel, Jose) = Assign rank 3 to Jose
SELECT name, department, start_date,
  RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;

+--------------------------------------------+
| name      | department | start_date | rank |
+--------------------------------------------+
| Jacob     | 1          | 1990-07-11 | 1    |
| Anthony   | 1          | 1995-11-29 | 2    |
| Andrew    | 1          | 1999-01-23 | 3    |
| Isabella  | 2          | 1997-09-28 | 1    |
| Daniel    | 2          | 2004-06-24 | 2    |
| Jose      | 2          | 2013-03-17 | 3    |
+--------------------------------------------+

윈도우 프레임 절에 명명된 윈도우 사용

명명된 윈도우에 일부 로직을 정의할 수 있으며 일부는 윈도우 프레임 절에 정의할 수 있습니다. 이 로직은 결합되어 있습니다. 다음은 Produce 테이블을 사용하는 예시입니다.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

+-------------------------------------------------------+
| item      | purchases  | category   | most_popular    |
+-------------------------------------------------------+
| orange    | 2          | fruit      | apple           |
| apple     | 8          | fruit      | apple           |
| leek      | 2          | vegetable  | lettuce         |
| cabbage   | 9          | vegetable  | kale            |
| lettuce   | 10         | vegetable  | kale            |
| kale      | 23         | vegetable  | kale            |
+-------------------------------------------------------+

다음 예시를 사용하여 이전 결과를 가져올 수도 있습니다.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  item_window AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  item_window AS (b)

다음 예시는 윈도우 프레임 절이 두 번 정의되었기 때문에 오류를 생성합니다.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS most_popular
FROM Produce
WINDOW item_window AS (
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)