[BigQuery] 데이터 타입별 함수 정리 (문자열과 시간/날짜)

 

 

🎯 데이터를 그대로 가져오지 않고 변환이 필요한 경우에 쓸 수 있는 함수를 알아보자

• SELECT 문에서 데이터를 변환시킬 수 있으며, WHERE의 조건문에서도 사용 가능
• 데이터의 타입에 따라 다양한 함수가 존재

 

1. 자료 타입을 변형하는 함수 : CAST

SELECT
 CAST(1 AS STRING) # 숫자 1을 문자 1로 변경

 

 

👉 더 안전하게 데이터 타입 변경하기 : SAFE_CAST() 

→ 변환이 실패할 경우 NULL을 반환하며, 다음과 같은 경우에 에러 대신  NULL이 됨

SELECT
 SAFE_CAST("문자열" AS INT64)

 

 

(유사 사례) 안전하게 나누기 연산하기 : SAFE_DIVIDE()

SAFE_DIVIDE(x, y) # zero error 대신 null 발생

 

---

2. 문자열을 다루는 함수

(1) CONCAT : 문자열 붙이기

→ CONCAT(컬럼1, 컬럼2, ...)

SELECT
  CONCAT('안녕', '하세요', '!!') AS result;

 

 

(2) SPLIT : 문자열 분리하기

→ SPLIT(문자열 원본, 나눌 기준이 되는 문자)

SELECT
 SPLIT('가, 나, 다, 라', ", ") AS result;

 

(3) REPLACE : 특정 단어 수정하기

→ REPLACE(문자열 원본, 찾을 단어, 바꿀 단어)

SELECT
 REPLACE('안녕하세요', '안녕', '실천') AS result;

 

(3) TRIM : 문자열 자르기

→ TRIM(문자열 원본, 자를 단어)

SELECT
 TRIM("안녕하세요", "하세요") AS trim_example

 

 

(4) UPPER : 영어 대문자 변환하기

→ UPPER(문자열 원본)

SELECT
 UPPER("ab") AS upper_example

---

3. 날짜 및 시간 데이터 (기본)

📜 시간 관련 데이터 타입의 종류

• DATE : DATE만 표시하는 데이터 → 예: 2024-03-01
• DATETIME : DATE와 TIME까지 표시하는 데이터(Timezone 정보 없음) → 예: 2024-03-03 14:00:00
• TIME : 날짜와 무관하게 시간만 표시하는 데이터 → 예: 23:59:59.00

 

📜 타임존 개념 짚고 가기

• UTC(Universal Time Coordinated : 한국시간 = UTC + 9)
  • 국제적인 표준 시간으로, 협정 세계시
  • 타임존이 존재함 → 특정 지역의 표준 시간대
• TIMESTAMP
  • UTC부터 경과한 시간을 나타내는 값
  • 타임존 정보 있음
  • 표시 예: 2024-03-03 14:00:00 UTC

 

📜 millisecond & microsecond 알고 가기

• millisecond(ms) : 천 분의 1초(1,000 ms = 1 sec)
  • 빠른 반응이 필요한 분야에서 사용(초보다 더 정확하게 ⏰)
  • Millisecond → TIMESTAMP →  DATETIME으로 변경해서 사용하는 경우 많음
• microsecond(μs) : 1 / 1,000 ms
• 아래 예시와 같이 시간 타입을 전환하며, DATETIME으로 전환 시 '타임존' 명시해야 함!
• 많은 회사들의 Table에 시간이 Timestamp로 저장된 경우가 많음(또는 Datetime) : 따라서 변환이 필요한 경우 많음

SELECT
 TIMESTAMP_MILLIS(1704176819711) AS milli_to_timestamp_value,
 TIMESTAMP_MICROS(1704176819711000) AS micro_to_timestamp_value,
 DATETIME(TIMESTAMP_MICROS(1704176819711000), 'Asia/Seoul') AS datetime_value

 

 

📜 정리 : TIMESTAMP vs. DATETIME

	TIMESTAMP	DATETIME
타임존	UTC라고 나옴	T가 나옴(Time 의미)
시간 차이	한국 시간 -9시간	한국 Zone 사용시 한국 시간과 동일

 

---

 

3. Datetime 가공 함수

(1) CURRENT_DATETIME : 현재 Datetime 출력

 

→ CURRENT_DATETIME([time_zone])

CURRENT_DATE('Asia/Seoul')
CURRENT_DATETIME('Asia/Seoul')

 

(2) EXTRACT : Datetime에서 특정 부분만 추출

 

→ EXTRACT(part FROM [col])

EXTRACT(DATE FROM [datetime_col])
EXTRACT(YEAR FROM [datetime_col])
EXTRACT(MONTH FROM [datetime_col])
EXTRACT(DAY FROM [datetime_col])
EXTRACT(HOUR FROM [datetime_col])
EXTRACT(MINUTE FROM [datetime_col])

 

👉 요일 추출 : EXTRACT(DAYOFWEEK FROM [datetime_col])

→ 한 주의 첫 날인 일요일부터 1로 반환됨(토요일이 마지막 7) 

 

 

(3) DATETIME_TRUNC : Datetime 시간 자르기

 

→ DATETIME_TRUNC([datetime_col], [HOUR:자를 부분 명시])

DATETIME_TRUNC([datetime_col], DAY)
DATETIME_TRUNC([datetime_col], YEAR)
DATETIME_TRUNC([datetime_col], MONTH)
DATETIME_TRUNC([datetime_col], HOUR)

datetime_trunc 실행 예시

 

 

(4) PARSE_DATETIME : 문자열로 저장된 Datetime을 Datetime 타입으로 바꾸기

 

→ PARSE_DATETIME('문자열의 형태', 'DATETIME 문자열')

→ Format Elements 문서를 참고하여 문자열 형태의 의미를 체크(필요할 때 확인) : https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time

PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2024-01-11 12:35:35') AS parse_datetime;

 

 

 

(5) FORMAT_DATETIME : Datetime 타입 데이터를 특정 형태의 문자열 데이터로 변환하기

 

→ FORMAT_DATETIME('문자열의 형태', 'DATETIME 문자열')

SELECT
 FORMAT_DATETIME("%c", DATETIME "2024-01-11 12:35:35") AS formatted;

 

(6) LAST_DAY : 자동으로 월의 마지막 값을 계산해서 특정 연산을 할 경우

 

→ LAST_DAY([DATETIME], 마지막날 기준)

→ 아래 예시를 보고 차이를 확인

SELECT
    LAST_DAY(DATETIME '2024-01-03 15:30:00') AS last_day,
    LAST_DAY(DATETIME '2024-01-03 15:30:00', MONTH) AS last_day_month,
    LAST_DAY(DATETIME '2024-01-03 15:30:00', WEEK) AS last_day_week,
    LAST_DAY(DATETIME '2024-01-03 15:30:00', WEEK(SUNDAY)) AS last_day_week_sun,
    LAST_DAY(DATETIME '2024-01-03 15:30:00', WEEK(MONDAY)) AS last_day_week_mon

 

 

(7) DATETIME_DIFF : 자동으로 월의 마지막 값을 계산해서 특정 연산을 할 경우

→ DATETIME_DIFF([첫 DATETIME], [다음 DATETIME], 차이 환산 기준)

→ 아래 예시를 보고 차이를 확인

SELECT
    DATETIME_DIFF(first_datetime, second_datetime, DAY) AS day_diff1,
    DATETIME_DIFF(second_datetime, first_datetime, DAY) AS day_diff2,
    DATETIME_DIFF(first_datetime, second_datetime, MONTH) AS month_diff,
    DATETIME_DIFF(first_datetime, second_datetime, WEEK) AS week_diff,
FROM ( SELECT
    DATETIME "2024-04-02 10:20:00" AS first_datetime,
    DATETIME "2021-01-01 15:30:00" AS second_datetime, )