현직 게임 기획자 블로그

▣ 문자열 함수

• 데이터를 여러 형태로 바꾸다 보면 문자열을 조작해야 하는 경우가 많으므로 빅쿼리는 내장 문자열 함수 라이브러리를 제공한다.

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 파이프라인에서 일반적으로 필요하므로, 다음 편의 함수를 잘 기억해 두면 유용한다.

SELECT
  ENDS_WITH('hello', 'o') --TRUE
 , ENDS_WITH('hello','h') --FALSE
 , STARTS_WITH('hello','h') --FALSE
 , STRPOS('hello','e') --2
 , STRPOS('hello','f') --0 FOR NOT-found
 , SUBSTR('hello',2,4) --1-based
 , CONCAT('hello','World')

• SUBSTR( )의 작동 방식을 주목.
• 첫 번째 파라미터는 시작 위치이며, 두 번째 파라미터는 하위 문자열에서 원하는 문자 수다.

■ 변환 함수

SELECT 
LPAD('Hello', 10, '*') AS LPAD --왼쪽에 *가 추가된다.
, RPAD('Hello', 10, '*') AS RPAD--오른쪽에 *가 추가된다.
, LPAD('Hello',10) AS LPAD  -- 왼쪽에 공백이 추가된다.
, LTRIM('Hello') AS LTRIM-- 왼쪽의 공백이 제거된다.
, RTRIM('Hello') AS RTRIM-- 오른쪽의 공백이 제거된다.
, TRIM('Hello') AS TRIM-- 양쪽의 공백이 제거된다.
, TRIM('***Hello***', '*') AS TRIM_2-- 양쪽의 *이 제거된다.
, REVERSE('Hello') AS REVERSE-- 문자열이 뒤바뀐다.

■ 정규 표현식

• 정규 표현식은 편의 함수들보다 강력하다. 예를 들어 STRPOS 함수 등은 특정 문자만 찾을 수 있지만, REGEXP_CONTAINS를 사용하면 강력한 검색을 수행할 수 있다.
• 예를 들어 컬럼에 미국 우편번호가 포함되어 있는지를 판별하려면 다음 쿼리를 사용하면 된다.(짧은 형식은 5자리 숫자이며, 긴 형식은 추가 4자리 숫자를 하이픈 또는 공백으로 구분한다.)

SELECT
  COLUMN,
  REGEXP_CONTAINS(COLUMN,r'\d{5}(?:[-\s]\d{4})?') has_zipcode,
  REGEXP_CONTAINS(COLUMN,r'^\d{5}(?:[-\s]\d{4})?$') is_zipcode,
  REGEXP_EXTRACT(COLUMN,r'\d{5}(?:[-\s]\d{4})?') the_zipcode,
  REGEXP_EXTRACT_ALL(COLUMN,r'\d{5}(?:[-\s]\d{4})?') all_zipcode,
  REGEXP_REPLACE(COLUMN,r'\d{5}(?:[-\s]\d{4})?', '*****') masked
FROM (
  SELECT
    *
  FROM
    UNNEST([ '12345', '1234','12345-9876', 'abc 12345 def', 'abcde-fghi', '12345 ab 34567', '12345 9876' ]) AS COLUMN )

• 정규 표현식 \d{5}는 5개의 10진수로 구성된 문자열과 일치한다.
• 괄호로 묶인 표현식의 두 번째 부분은 하이픈 또는 공백(\s)으로 분리되는 첫 번째 5개 10진수와 분리된 4개의 10진수(\d{4})의 선택적(괄호 끝에 ? 표시함) 그룹(?:)을 찾는다.
• 문자열에 \d, \s 등이 있으면 문제가 발생할 수 있으므로 문자열 앞에 r(원시)을 붙여 문자열 리터럴로 만들었다.
• 두 번째 표현식은 정확히 일치하는 경우를 보여준다. 지정된 문자열로 시작(^)하고 끝나야 ($) 한다고 보면 된다.
• 정규식과 일치하는 문자열 부분을 추출하려면 REGEXP_EXTRACT를 사용한다. 식이 일치하지 않으면 NULL을 반환하고, 일치하는 항목이 여러 개 있으면 첫 번째로 일치하는 값만 반환한다.
• REGEXP_EXTRACT_ALL은 모두 일치하는 항목을 반환한다. 일치하는 것이 없으면 빈 배열을 반환한다.
• REGEXP_REPLACE는 모든 일치 항목을 대체 문자열로 바꾼다.
• 빅쿼리의 정규 표현식은 구글의 오픈 소스인 RE2 라이브러리의 규칙을 따른다.

■ 문자열 함수 정리

• 데이터 분석에서 문자열 데이터는 매우 흔하므로 사용 가능한 함수를 폭넓게 학습하는 것이 좋다. 정확한 문법에 대해서는 빅쿼리 공식문서를 참조한다.