표준 SQL의 분석 함수 개념

분석 함수 개념

이 항목에서는 BigQuery의 분석 함수 작동 방식에 대해 설명합니다. BigQuery가 지원하는 다양한 분석 함수에 대한 설명은 탐색 함수, 번호 지정 함수, 집계 분석 함수의 참조 주제를 참조하세요.

데이터베이스에서 분석 함수는 행 그룹을 대상으로 집계 값을 계산하는 함수입니다. 행 그룹에 대해 단일 집계 값을 반환하는 집계 함수와 달리 분석 함수는 입력 행 그룹을 대상으로 함수를 계산함으로써 각 행마다 단일 값을 반환합니다.

분석 함수는 복잡한 분석 작업을 간결하게 나타내기 위한 강력한 메커니즘이며, 효율적인 계산을 가능하게 하여 값비싼 셀프-JOIN 또는 SQL 쿼리 외부의 계산이 필요성을 없애줍니다.

또한 분석 함수는 SQL 표준과 일부 상업 데이터베이스에서 '(분석) 기간 함수'라고도 합니다. 분석 함수는 window 또는 window frame이라고 하는 행 그룹을 대상으로 평가되기 때문입니다. 일부 다른 데이터베이스에서는 온라인 분석 처리(OLAP) 함수로 지칭되기도 합니다.

간소화된 구문:

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

분석 함수에는 분석 함수의 평가 대상이 되는 window frame을 정의하는 OVER 절이 필요합니다. OVER 절에는 다음 세 가지 선택적 절이 포함됩니다. BigQuery는 OVER 절의 하위 절을 작성된 순서에 따라 평가합니다.

  • PARTITION BY 절은 입력 행을 파티션으로 분할합니다. GROUP BY와 비슷하지만 동일한 키로 실제로 행을 결합하지는 않습니다.
  • ORDER BY 절은 각 파티션 내의 정렬을 지정합니다.
  • window_frame_clause는 현재 파티션 내에서 window frame을 정의합니다.

OVER 절은 비워 둘 수도 있으며(OVER()) 이 경우 window frame에 모든 입력 행이 포함됩니다.

분석 함수는 집계(GROUP BY 및 비분석 집계 함수) 이후 평가됩니다.

예: 회사에서 각 부서의 '선임 순위', 즉 가장 오래 근무한 직원을 보여주는 리더보드를 만들고자 하는 경우를 가정해 보겠습니다. Employees 테이블에는 Name, StartDate, Department 열이 포함됩니다.

다음 쿼리는 부서 내에서 각 직원의 순위를 계산합니다.

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

그림 1에서는 계산 프로세스의 개념을 보여줍니다.

마크다운 이미지 그림 1: 분석 함수 삽화

BigQuery는 OVER 절의 하위 절을 나타나는 순서에 따라 평가합니다.

  1. PARTITION BY: 테이블은 먼저 department별로 두 파티션으로 분할됩니다.
  2. ORDER BY: 각 파티션의 직원 행이 startdate별로 정렬됩니다.
  3. 프레이밍: 없음. 기간 프레임 절은 모든 번호 지정 함수용이므로 RANK()에서 허용되지 않습니다.
  4. RANK(): window frame에 걸쳐 각 행의 선임 순위가 계산됩니다.

분석 함수 구문

analytic_function_name ( [ argument_list ] )
  OVER { window_name | ( [ window_specification ] ) }

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

window_frame_clause:
{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

분석 함수는 쿼리의 두 위치에서만 스칼라 표현식 또는 스칼라 표현식 피연산자로 나타날 수 있습니다.

  • SELECT 목록. 분석 함수가 SELECT 목록에 표시되면 argument_list는 동일한 SELECT 목록에 있는 별칭을 참조할 수 없습니다.
  • ORDER BY. 분석 함수가 쿼리의 ORDER BY 절에 표시되면 argument_list는 SELECT 목록 별칭을 참조할 수 있습니다.

또한 분석 함수는 argument_list 또는 OVER 절의 다른 분석 함수를 참조할 수 없으며, 이는 별칭을 통한 간접적 참조라 해도 마찬가지입니다.

잘못됨:

SELECT ROW_NUMBER() OVER () AS alias1
FROM Singers
ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

위 쿼리에서 분석 함수 alias1은 분석 함수 ROW_NUMBER() OVER()로 확인됩니다.

OVER 절

구문:

OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY sort_specification_list ]
  [ window_frame_clause ]

OVER 절에는 다음과 같은 3개의 구성요소가 포함될 수 있습니다.

  • PARTITION BY
  • ORDER BY
  • window_frame_clause 또는 window_name: WINDOW 절에 정의된 window_specification 절을 참조함.

OVER 절이 비어 있는 경우 분석 함수 OVER()는 모든 입력 행이 포함된 단일 파티션을 대상으로 계산되며, 이는 각 출력 행에 대해 동일한 결과를 생성하게 됨을 의미합니다.

PARTITION BY 절

구문:

PARTITION BY expression [, ... ]

PARTITION BY 절은 입력 행을 개별 파티션으로 분할하며 분석 함수는 이러한 개별 파티션을 대상으로 독립적으로 평가됩니다. PARTITION BY 절에는 여러 개의 expressions가 포함될 수 있습니다.

expression의 데이터 유형은 groupable이어야 하며 파티션 나누기를 지원해야 합니다. 즉, expression은 다음 데이터 유형이 될 수 없습니다.

  • 부동 소수점
  • 구조체
  • 배열

이 목록은 GROUP BY가 지원하지 않는 데이터 유형 목록과 거의 동일하며 부동 소수점 유형이 부가적으로 제외됩니다(BigQuery 데이터 유형 상단에 있는 데이터 유형 속성 표의 'Groupable' 참조).

PARTITION BY 절이 없는 경우 BigQuery는 전체 입력을 하나의 파티션으로 취급합니다.

ORDER BY 절

구문:

ORDER BY expression [ ASC | DESC ] [, ... ]

ORDER BY 절은 각 파티션 내의 정렬을 정의합니다. ORDER BY 절이 없으면 파티션의 행 정렬은 비확정적입니다. 일부 분석 함수에는 ORDER BY가 필요합니다. 이는 각 분석 함수 그룹 섹션에 명시되어 있습니다. ORDER BY 절이 있더라도 일부 함수는 window frame 내의 정렬을 구분하지 않습니다(예: COUNT).

OVER 절 내의 ORDER BY 절은 다음과 같은 측면에서 일반적인 ORDER BY과 일치합니다.

  • 여러 개의 expressions가 포함될 수 있습니다.
  • expression의 유형은 정렬을 지원해야 합니다.
  • 선택적인 ASC/DESC 사양이 각 expression에서 허용됩니다.
  • 가능한 최솟값으로 NULL 값 정렬(처음 ASC, 마지막 DESC)

다음 유형은 정렬을 지원하지 않으므로 데이터 유형 지원은 일반적인 ORDER BY과 동일합니다.

  • 배열
  • 구조체

OVER 절에 ORDER BY 절은 있지만 window_frame_clause는 없는 경우 ORDER BY는 암시적으로 window_frame_clause를 다음으로 정의합니다.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

window_frame_clauseORDER BY 절 모두 없는 경우 window frame의 기본값은 전체 파티션이 됩니다.

Window Frame 절

구문:

{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_clause는 분석 함수가 평가되는 파티션 내의 현재 행을 중심으로 window frame을 정의합니다. window_frame_clause는 실제 기간 프레임(ROWS로 정의됨)과 논리적 기간 프레임(RANGE로 정의됨)을 모두 허용합니다. OVER 절에 ORDER BY 절은 있지만 window_frame_clause는 없는 경우 ORDER BY는 암시적으로 window_frame_clause를 다음으로 정의합니다.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

window_frame_clauseORDER BY 절 모두 없는 경우 window frame의 기본값은 전체 파티션이 됩니다.

numeric_expression은 상수 또는 쿼리 매개변수만 될 수 있으며 두 가지 모두 값은 음수가 될 수 없습니다. 그렇지 않은 경우 BigQuery에서 오류가 발생합니다.

기간 프레임 절의 예:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • 전체 파티션을 포함합니다.
  • 사용 예: 파티션을 대상으로 총계를 계산합니다.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 현재 행 앞까지 또는 현재 행을 포함하여 파티션의 모든 행을 포함합니다.
  • 사용 예: 누적 합계를 계산합니다.
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  • 현재 행의 앞 두 행과 뒤 두 행 사이의 모든 행을 포함합니다.
  • 사용 예: 이동 평균을 계산합니다.

window_frame_specBETWEEN 절을 사용하는 경우:

  • window_frame_boundary_startwindow_frame_boundary_end 경계 또는 그 앞에서 시작하는 경계를 지정해야 합니다. 결과는 다음과 같습니다.
    1. window_frame_boundary_startCURRENT ROW가 포함되는 경우 window_frame_boundary_endPRECEDING을 포함할 수 없습니다.
    2. window_frame_boundary_startFOLLOWING이 포함되는 경우 window_frame_boundary_endCURRENT ROW 또는 PRECEDING을 포함할 수 없습니다.
  • window_frame_boundary_start에는 기본값이 없습니다.

그렇지 않은 경우 지정된 window_frame_spec 경계는 기본값이 'CURRENT ROW'인 기간 프레임의 시작과 기간 프레임의 끝 경계를 나타냅니다. 따라서

ROWS 10 PRECEDING

위 항목은 다음과 동일합니다.

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
ROWS

ROWS 기반 기간 프레임은 현재 행으로부터의 실제 오프셋을 기반으로 window frame을 계산합니다. 예를 들어 아래의 기간 프레임은 현재 행을 중심으로 크기가 5(최대)인 기간 프레임을 정의합니다.

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

window_frame_clausenumeric_expression은 현재 행으로부터의 행 수로 해석되며 상수, 음수가 아닌 정수여야 합니다. 쿼리 매개변수가 될 수도 있습니다.

지정된 행의 window frame이 파티션의 시작 또는 끝을 넘는 경우 window frame은 해당 파티션 내의 행만 포함합니다.

예: z, x, y 열이 있는 다음 테이블을 참조하세요.

z x y
1 5 AA
2 2 AA
3 11 AB
4 2 AA
5 8 AC
6 10 AB
7 1 AB

다음 분석 함수를 참조하세요.

SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING)

PARTITION BY 절은 y 값을 기준으로 테이블을 3개의 파티션으로 나누며 ORDER BY는 각 파티션 내의 행을 z 값에 따라 정렬합니다.

파티션 1/3:

z x y
1 5 AA
2 2 AA
4 2 AA

파티션 2/3:

z x y
3 11 AB
6 10 AB
7 1 AB

파티션 3/3:

z x y
5 8 AC

아래 테이블에서 굵은 글꼴은 현재 평가 중인 행을, 색상이 있는 셀은 이러한 행에 대한 window frame의 모든 행을 나타냅니다.

  • window_frame_spec에서 지정한 기간 크기는 3이지만 y = AA 파티션의 첫 행에서 window frame은 선행하는 행이 없으므로 두 개의 행만 포함합니다. 첫 번째 행에 대한 분석 함수의 결과는 7입니다.
z x y
1 5 AA
2 2 AA
4 2 AA
  • 파티션의 두 번째 행의 경우 window frame은 3개의 행 모두 포함합니다. 두 번째 행에 대한 분석 함수의 결과는 9입니다.
z x y
1 5 AA
2 2 AA
4 2 AA
  • 뒤에 오는 행이 없으므로 파티션의 마지막 행에서 window frame은 두 개의 행만 포함합니다. 세 번째 행에 대한 분석 함수의 결과는 4입니다.
z x y
1 5 AA
2 2 AA
4 2 AA
RANGE

RANGE 기반 기간 프레임은 현재 행의 ORDER BY 키 값을 기반으로 현재 행을 중심으로 하는 행의 논리적 범위를 기반으로 window frame을 계산합니다. 제공된 범위 값은 현재 행의 키 값에 더하거나 빼서 window frame의 시작 또는 끝 범위 경계를 정의합니다.

기간이 다음이 아닌 경우 ORDER BY 절을 지정해야 합니다.

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

window_frame_clausenumeric_expressionORDER BY의 현재 행 값에서의 오프셋으로 해석됩니다. numeric_expression은 숫자 유형이어야 합니다. 날짜 및 타임스탬프는 현재 지원되지 않습니다. 또한 numeric_expression은 상수, 음수가 아닌 정수 또는 매개변수여야 합니다.

RANGE 기반 기간 프레임의 ORDER BY 절에는 최대 하나의 expression이 포함될 수 있으며 expression은 숫자 유형이어야 합니다.

파티션이 하나인 RANGE 기반 기간 프레임의 예:

SELECT x, COUNT(*) OVER ( ORDER BY x
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS count_x
FROM T;

아래 테이블에서 굵은 글꼴은 현재 평가 중인 행을, 색상이 있는 셀은 이러한 행에 대한 window frame의 모든 행을 나타냅니다.

  • 행 1의 경우 x = 5이며 따라서 COUNT(*)는 3 <=x <= 7인 행만 포함합니다.
x count_x
5 1
2
11
2
8
10
1
  • 행 2의 경우 x = 2이며 따라서 COUNT(*)는 0 <= x <= 4인 행만 포함합니다.
x count_x
5 1
2 3
11
2
8
10
1
  • 행 3의 경우 x = 11이며 따라서 COUNT(*)는 9 <= x <= 13인 행만 포함합니다.
x count_x
5 1
2 3
11 2
2
8
10
1

WINDOW 절

구문:

WINDOW window_definition [, ...]
window_definition: window_name AS ( window_specification )

WINDOW 절은 window_name이 분석 함수의 SELECT 목록에서 참조 가능한 명명된 기간 목록을 정의합니다. 여러 분석 함수에서 동일한 window_frame_clause를 사용하려는 경우 유용합니다.

WINDOW 절은 SELECT 절의 끝에만 올 수 있습니다(쿼리 구문 참조).

명명된 기간

WINDOW 절의 정의하면 분석 함수에서 명명된 기간을 사용할 수 있지만 SELECT 목록에서만 사용할 수 있으며 ORDER BY 절에서는 명명된 기간을 사용할 수 없습니다. 명명된 기간은 자체적으로 표시되거나 OVER 절 내에 삽입될 수 있습니다. 명명된 기간은 SELECT 목록 별칭을 참조할 수 있습니다.

예:

SELECT SUM(x) OVER window_name FROM ...
SELECT SUM(x) OVER (
  window_name
  PARTITION BY...
  ORDER BY...
  window_frame_clause)
FROM ...

window_name과 연결된 window_specificationOVER 절에 삽입된 경우 동일한 OVER 절에 있는 PARTITION BY, ORDER BY, window_frame_clause와 호환되어야 합니다.

명명된 기간에는 다음 규칙이 적용됩니다.

  • SELECT 목록에서만 명명된 기간을 참조할 수 있습니다. ORDER BY 절, 외부 쿼리 또는 하위 쿼리에서는 참조할 수 없습니다.
  • 기간 W1(명명되거나 명명되지 않음)은 다음 규칙에 따라 명명된 기간 NW2를 참조할 수 있습니다.
    1. W1이 명명된 기간인 경우 참조된 명명된 기간 NW2는 동일한 WINDOW 절에서 W1보다 선행해야 합니다.
    2. W1에는 PARTITION BY 절이 포함될 수 없습니다.
    3. W1과 NW2에 모두 ORDER BY 절이 포함될 수는 없습니다.
    4. NW2에는 window_frame_clause가 포함될 수 없습니다.
  • (명명되거나 명명되지 않은) 기간 W1이 명명된 기간 NW2를 참조하는 경우 그 결과인 기간 사양은 다음을 사용하여 정의됩니다.
    1. NW2의 PARTITION BY(있는 경우)
    2. W1 또는 NW2의 ORDER BY(둘 중 하나에서 지정된 경우). 둘 모두 ORDER BY 절을 포함할 수는 없습니다.
    3. W1의 window_frame_clause(있는 경우)

이 항목에서는 분석 탐색 함수의 작동 방식에 대해 설명합니다. BigQuery가 지원하는 분석 탐색 함수에 대한 설명은 탐색 함수의 함수 참조에서 볼 수 있습니다.

탐색 함수는 일반적으로 현재 행의 기간 프레임에 있는 다른 행을 대상으로 몇몇 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과 동일한 유형입니다.

번호 지정 함수

이 항목에서는 번호 지정 함수의 작동 방식에 대해 설명합니다. BigQuery가 지원하는 분석 번호 지정 함수에 대한 설명은 번호 지정 함수 참조에서 볼 수 있습니다.

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

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

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...
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를 반환합니다.

집계 분석 함수

BigQuery는 특정 집계 함수를 분석 함수로 지원합니다.

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

BigQuery가 지원하는 집계 분석 함수에 대한 설명은 집계 분석 함수의 함수 참조를 확인하세요.

이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.