데이터를 여러 형태로 바꾸다 보면 문자열을 조작해야 하는 경우가 많으므로 빅쿼리는 내장 문자열 함수 라이브러리를 제공한다.
WITH
example AS(
SELECT
*
FROM
UNNEST ( ['Seatle','New York', 'Singapore'] ) AS city )
SELECT
city,
LENGTH(city) AS len,
LOWER(city) AS lower,
STRPOS(city, 'or') AS orpos
FROM
example
이 쿼리는 문자열의 길이를 계산해 문자열을 소문자로 만든 후 'city' 컬럼에서 부분 문자열의 위치를 찾는다.
부분 문자열 'or'은 'new york' 및 'Singapore'에서 찾을 수 있지만 'Seattle'에서는 찾을 수 없다.
문자열 조작에 특히 유용한 두 가지 기능은 SUBSTR 및 CONCAT 이다. SUBSTR은 부분 문자열을 추출하고 CONCAT는 입력값을 연결한다.
WITH example AS (
SELECT 'armin@abc.com' AS email, 'Annapolis, MD' AS city
UNION ALL SELECT 'boyan@bca.com', 'Boulder, CD'
UNION ALL SELECT 'carrie@cab.com', 'Chicago, IL'
)
SELECT
CONCAT(
SUBSTR(email, 1, STRPOS(email,'@') -1), --username
' from ', city) AS callers
FROM example
■ 국제화
빅쿼리의 문자열은 유니코드이므로 영어와 관계된 가정은 피해야 한다. 예를 들어 일본어에는 '대문자'라는 것이 없으며, 문자열 타입을 바이트 배열 타입으로 변환할 때 기본적으로 적용되는 UTF-8 인코딩은 타밀어 같은 언어를 제대로 표현하지 못한다.
WITH
example AS(
SELECT
*
FROM
UNNEST( [ 'Seattle', 'New York', '東京' ] ) AS city )
SELECT
city,
UPPER(city) AS allcaps,
CAST(city AS BYTES ) AS bytes
FROM
example
내 컴퓨터에 타밀어는 없으므로 패스.
빅쿼리는 유니코드 문자 배열, 바이트 배열 및 유니코드 코드 포인트 배열(int 64)로 문자열을 나타내는 3가지 방법을 지원한다.
동일한 문자열에서 CHAR_LENGTH 및 BYTE_LENGTH 결과와 코드 포인트 수가 문자 수와 어떻게 다른지 확인하자.
WITH
example AS(
SELECT *
FROM
UNNEST( [ 'Seattle','New York', '東京' ] ) AS city )
SELECT
city,
CHAR_LENGTH(city) AS char_len,
to_code_points(city)[ORDINAL(1)] AS first_codept ,
ARRAY_LENGTH(to_code_points(city)) AS num_codept,
CAST (city AS bytes) AS bytes,
BYTE_LENGTH(city) AS byte_len
FROM
example
따라서 어떤 열에 다른 언어로 된 텍스트가 포함되어 있는지 파악한 후 언어의 차이를 고려해서 문자열 조작 함수를 사용해야 한다.
■ 출력 및 파싱
문자열을 파싱할 때는 간단히 INT64나 FLOAT64 타입으로 변환하면 되지만, 어떤 값을 원하는 형태의 문자열로 표현하라면 FORMAT 함수를 사용해야 한다.
SELECT
CAST(42 AS STRING),
CAST ('42' AS INT64),
FORMAT ('%03d',42),
FORMAT('%6.3f', 32.457842),
FORMAT('%5.3f', 32.4),
FORMAT('**%s**','H'),
FORMAT('%s-%03d','Agent',7)
FORMAT은 C의 printf 함수와 유사하게 작동하며 같은 형식 지정자를 사용한다. 좀 더 유용한 지정자 중 몇몇은 앞의 예에서 설명했다.
FORMAT 함수는 날짜나 타임스탬프 값도 지원하지만, 사용자의 로케일을 감안해서 날짜를 형식화할 수 있는 FORMAT_DATE와 FORMAT_TIMESTAMP 함수를 사용하는 것이 더 좋다.
■ 문자열 조작 함수
문자열 조작은 ETL 파이프라인에서 일반적으로 필요하므로, 다음 편의 함수를 잘 기억해 두면 유용한다.
빅쿼리에는 하나의 정수형 타입(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'처럼 문자열 형태로 빅쿼리에서 직접 수집해야 한다. 그렇지 않으면 부동소수점 표현은 정밀한 계산을 하지 않는다.
