구글 빅쿼리 완벽 가이드 - 3장(2) - 숫자형과 함수

▣ 숫자형과 함수

• 빅쿼리에는 하나의 정수형 타입(INT64)과 하나의 부동소수점 타입(FLOAT64)이 있다.
• 이 두 가지 타입 모두 일반적인 사칙 연산을 지원한다. 따라서 한 컬럼을 다른 컬럼으로 나눠서 편도 자전거 대여 횟수를 구할 수 있다.

WITH
  example AS (
  SELECT 'Sat' AS day, 1451 AS numrides, 1018 AS oneways
  UNION ALL SELECT 'Sun', 2376, 936 
)
SELECT
  *, (oneways/numrides) AS frac_oneway
FROM
  example

• 정수형 타입은 산술 연산자 외에 비트 연산자도 지원한다.
• 데이터 타입에 대한 연산은 함수를 이용하면 된다. 함수는 입력된 값에 대한 작업을 수행한다. 다른 프로그래밍 언어와 마찬가지로 SQL의 함수는 재사용 가능한 논리를 캡슐화하고 구현의 복잡성을 추상화한다.

함수의 유형	설명	예시
스칼라 함수	하나 이상의 입력 파라미터에서 작동해 단일 값을 반환하는 함수. 반환 데이터 타입이 허용되는 모든 곳에서 사용할 수 있다.	ROUND(3.14)는 FLOAT63인 3을 반환하므로 FLOAT64가 허용되는 모든 위치에서 ROUND 함수를 사용할 수 있다. SUBSTR("hello", 1, 2)는 'he'를 반환하며 3개의 입력 파라미터를 사용하는 스칼라 함수다.
집계 함수	값 모음에서 계산을 수행하고 단일 값을 반환하는 함수 종종 행 그룹을 계산하기 위해 GROUP BY 절과 함께 사용된다.	MAX (Tripduration)는 tripduration열의 최대 값을 계산한다. 다른 집계 함수로는 SUM( ), COUNT( ), AVG( ) 등이 있다.
분석 함수	값 모음에서 작동하지만 모음의 각 값에 대한 결과를 반환하는 함수. 윈도우 프레임을 이용하면 분석 함수가 적용될 행의 집합을 지정할 수 있다.	row_number( ), rank( ) 등이 있다.
테이블 반환 함수	FROM 절에서 사용할 수 있는 결과 집합을 반환하는 함수	배열에서 UNNEST를 호출한 다음 선택할 수 있다
사용자 정의 함수	내장 함수가 아니라 사용자가 직접 구현한 함수. SQL로 작성할 수 있으며 앞에서 언급한 타입을 반환할 수 있다	CREATE TEMP FUNCTION lastElement(arr ANY TYPE) AS (arr[ORDINAL(ARRAY_LENGTH(arr)) ]);

---

■ 수학 함수

• 편도 자전거 대여 비율을 계산했던 쿼리에서 최종 결과를 반올림하려면, 정수 및 부동소수점 타입을 지원하는 여러 내장 수학 함수 중 하나인 ROUND 함수를 사용하면 된다.

WITH
  example AS (
  SELECT
    'Sat' AS day, 1451 AS numrides, 1018 AS oneways
  UNION ALL SELECT 'Sun', 2376, 936 
  )
SELECT
  *, ROUND(oneways/numrides, 2) AS frac_oneway FROM example

• ROUND(값, 소수점이하 개수)를 했을 때, 2번 째 자리가 '0'일 경우, 노출되지 않는다.

---

■ 표준 규격 부동소수점 분할

• 분모가 0이거나 결과가 오버플로우면 나누기 연산이 실패한다. 따라서 사전에 값이 0인지 확인해서 나눗셈 연산을 보호하는 것보다는 분모가 0일 가능성이 있다면 앞의 예제처럼 특수 함수를 사용하는 것이 좋다. 즉 다음과 같이 쿼리를 개선할 수 있다.

WITH
  example AS (
  SELECT
    'Sat' AS day, 1451 AS numrides, 1018 AS oneways
  UNION ALL SELECT 'Sun', 2376, 936
  UNION ALL SELECT 'Wed',0, 0
  )
SELECT
  *, ROUND(IEEE_DIVIDE(oneways, numrides), 2) AS frac_oneway FROM example

• IEEE_DIVIDE 함수는 IEEE (Institute of Eletrical and Electronics Engineers)에서 설정한 표준을 따르며 0으로 나누려고 하면 Not-a-Number(NaN)라는 특수 부동소수점 숫자를 반환한다.
• 또한 개선하기 전의 쿼리는 표준 나누기 연산자와 SAFE_DIVIDE을 사용한다.

---

■ SAFE 함수

• 스칼라 함수에 SAFE 접두사를 사용하면 오류를 발생시키지 않고 NULL을 반환한다. 예를 들어 음수의 로그는 정의되지 않으므로 다음 쿼리를 실행하면 오류가 발생한다.

SELECT LOG(10, -3), LOG(10, 3)

 

• LOG 함수 앞에 SAFE를 덧붙여보면 LOG(10 -3)의 결과가 NULL로 반환된다.

SELECT SAFE.LOG(10, -3), LOG(10, 3)

• SAFE 접두사는 수학, 함수, 문자열 함수 (예를 들어 SUBSTR 함수는 시작 인덱스가 음수이면 일반적으로 오류를 발생시키지만 SAFE, SUBSTR로 호출되면 NULL을 반환한다) 및 시간 함수에 사용할 수 있다. 그러나 SAFE 접두사는 스칼라 함수에만 사용할 수 있으며, 집계 함수, 분석 함수 또는 사용자 정의 함수에는 사용할 수 없다.

---

■ 비교

• 비교 연산은 < , <= , > , >= , != (또는 <>) 등의 연산자를 이용해서 그 결과를 얻는다. NULL과 NaN은 정렬을 위해 유효한 숫자 (-inf 포함)보다 작은 값으로 ㅂ평가되며, NaN은 NULL보다 큰 값으로 평가된다. 그러나 NaN과의 비교는 항상 false를 반환하며, NULL과의 비교는 항상 NULL을 반환한다. 이는 역설적인 결과로 이어질 수 있다.

WITH  example AS(
    SELECT 'Sat' AS day, 1451 AS numrides, 1018 AS oneways
    UNION ALL SELECT 'Sun', 2376, 936
    UNION ALL SELECT 'Mon', NULL, NULL
    UNION ALL SELECT 'Tue', IEEE_DIVIDE(-3, 0), 0 -- 이 값은 -inf와 0이다.
)
SELECT * FROM example
ORDER BY numrides

• 하지만 다음과 같이 대여 건수가 2,000회 미만인 행을 필터링해 보자.

WITH  example AS(
    SELECT 'Sat' AS day, 1451 AS numrides, 1018 AS oneways
    UNION ALL SELECT 'Sun', 2376, 936
    UNION ALL SELECT 'Mon', NULL, NULL
    UNION ALL SELECT 'Tue', IEEE_DIVIDE(-3, 0), 0 -- 이 값은 -inf와 0이다.
)
SELECT * FROM example
WHERE numrides < 2000

• 그러면 3개가 아닌 2개의 결과만 나타난다.
• 이유는 WHERE 절은 결과가 참인 행만 반환하는데, NULL을 2000과 비교하면 결과는 NULL이므로 참으로 평가되지 않기 때문이다.
• 빅쿼리의 &와 | 연산자는 비트 연산에만 사용함에 주의한다.
• ! 기호는 != 처럼 NOT을 의미하지만 독립적으로는 사용할 수 없다. 즉 다른 언어처럼 성별에 대한 논리적 부정을 계산하기 위해 !gender를 사용할 수 없다. 같지 않음은 <> 연산자로도 표현할 수 있지만 일관성을 위해 != 또는 <> 중 하나만 사용하는 것이 좋다.

---

■ NUMERIC을 사용한 정밀 소수 계산

• INT64와 FLOAT64는 유연하고 빠르게 설계되었지만 계산에 사용될 때는 63비트 영역의 컴퓨터 메모리에 2진수 형태로 저장되므로 표현할 수 있는 값의 범위에 제한이 있다. 대부분의 애플리케이션에서는 문제없이 사용할 수 있지만, 10진법으로 표현하는 숫자를 정확하게 계산해야 하는 재무 및 회계 프로그램에서는 문제가 된다.
• 빅쿼리의 NUMERIC 데이터 타입은 숫자를 나타내는 38자리 숫자를 제공하며 그 숫자 중 9자리는 소수점 아래의 숫자를 표시한다. 또한 16바이트를 스토리지로 사용하므로 소수점 이하 자릿수를 정확하게 표현할 수 있어 재무 계산에 적합하다.
• 예를 들어 3번의 결제 기록의 합을 계산하는 경우를 가정해 보자. 당연히 계산 결과가 정확해야 한다. 그러나 FLOAT63 값을 사용하면 숫자가 메모리에 표시되는 방식과 숫자가 10진수로 표시되는 방식 간의 차이로 인해 작은 차이가 생길 수 았다.

WITH example AS (
    SELECT 1.23 AS payment
    UNION ALL SELECT 7.89
    UNION ALL SELECT 12.43
)
SELECT
 SUM(payment) AS total_paid,
 AVG(payment) AS average_paid
FROM example

• 재무 및 회계 응용 프로그램에서 이런 부정확한 값 때문에 장부상의 최종 잔액이 달라질 수도 있다. 결제 데이터 타입을 NUMERIC으로 변경하면 어떻게 되는지 확인한다.

WITH example AS (
    SELECT NUMERIC '1.23' AS payment
    UNION ALL SELECT NUMERIC '7.89'
    UNION ALL SELECT NUMERIC '12.43'
)
SELECT
 SUM(payment) AS total_paid,
 AVG(payment) AS average_paid
FROM example

• 지불의 합계가 이제 정확하게 나온다. (평균은 반복되는 소수점이므로 NUMERIC 타입으로도 정확하게 나타낼 수 없다.)
• 한 가지 주의할 점은 NUMERIC 타입은 NUMERIC '1.23'처럼 문자열 형태로 빅쿼리에서 직접 수집해야 한다. 그렇지 않으면 부동소수점 표현은 정밀한 계산을 하지 않는다.