BigQuery Official #2_표현식

1. 함수 호출

1. SAFE.

• 오류 대신 NULL이 반환
• STRING 함수, 수학 함수, DATE 함수, DATETIME 함수, TIMESTAMP 함수, JSON 함수 등의 Scalar 함수에서 사용 가능
• 집계, 기간, 사용자 정의 함수에서 사용 불가
• 나누기 연산의 오류를 방지하려면 SAFE_DIVIDE
• Cast 변환 오류를 방지하려면 SAFE_CAST

2. UDF (User Defined Function)

CREATE FUNCTION [`project_name`].dataset_name.function_name([parameter_value[, ...]])

• 동일 프로젝트 내에서 생성 후 호출

CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);

SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15

• 다른 프로젝트에서 생성한 함수 호출

CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
  AS (x * y * 2);

SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24

2. 집계함수 호출

• 집계함수란 그룹의 행을 단일 값으로 요약하는 함수
• 집계함수에 OVER를 함께 사용할 경우 행 그룹에 대해 값을 계산 한 후, 각 행별로 결과를 반환하는 WINDOW 함수로 됨

3. Window 함수 호출

• 분석 함수라고 일컫기도 함
• 행 그룹의 값을 계산하고 각 행마다 하나의 결과를 반환
• 행 그룹에 대해 하나의 결과를 반환하는 집계 함수와는 다름
• Window함수는 OVER절을 포함함
• Window함수를 사용하면 이동 평균, 순위, 누적 합계를 계산하고, 기타 분석을 수행할 수 있음
• 윈도우 함수는 집계함수가 계산된 후 계산됨
• 예를 들어 GROUP BY 절과 비윈도우 집계 함수가 먼저 계산됨. 따라서 집계 함수는 Window 함수에 입력 대상이 될 수 있음

• 윈도우함수명(인수들) OVER ([PARTITION BY 컬럼] [ORDER BY 절 ASC|DESC] [윈도우절])
• Window 함수명 : SUM, RANK, MAX, FIRST_VALUE 등
• OVER : Window 함수 바로 뒤에 오는 값 (Default로 들어가야 하는 값)
• PARTITION BY : 소그룹 지정 (Default 값 아님)
• ORDER BY : 정렬 (Default 값 아님)
• Window 절 : ROWS or RANGE 중 하나를 선택, BETWEEN도 선택적으로 사용하여 결과 범위 지정 가능
  1. ROWS & RANGE
  - ROWS : 조회된 행이 기준. 값의 동일 여부를 떠나 하나하나 연산

  • RANGE ; 조회된 행의 값이 기준. 같다면 묶어서 합산 후 연산

  • 즉, WINDOW 함수에 사용한 기준(ex. SUM(A)를 했으면 A가 기준)의 값이 동일하다면 그 값을 가진 모든 행의 값을 더해서 보여줌

  • SUM(A)로 했을 때, A값이 300인 행이 5개 있다면 결과값은 1500이 되는것

    
    
  2. BETWEEN
  • BETWEEN이 있다면
    ROWS|RANGE
    BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | 상수값 PRECEDING 또는 FOLLOWING
    AND UNBOUNDED FOLLOWING| CURRENT ROW | 상수값 PRECEDING 또는 FOLLOWING
    
  • BETWEEN이 없다면
    ROWS|RANGE UNBOUNDED PRECEDING | CURRENT ROW | 상수값 PRECEDING

SELECT item, purchases, category, 
	SUM(purchases) 
  	OVER (
    PARTITION BY category -- category값을 기준으로 Sum 할 것이고 
    ORDER BY purchases -- purchases값을 기준으로 listing 할 것이고
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  		) AS total_purchases
FROM Produce