4. 함수

4.1. 수치형 함수

4.1.1. ROUND/ TRUNC/ CEIL/ FLOOR

• ROUND

SELECT 
		  ROUND(숫자, 자릿수)

SELECT
		  ROUND(1.2345, 2) # 소숫점 2번째자리까지 반올림
		, ROUND(456, -1); # 1의 자리부터는 마이너스(-)로 표현

• TRUNC

SELECT
		  TRUNC(숫자, 자릿수)

SELECT
		  TRUNC(1.2345, 2) # 소숫점 2번째자리까지 내림 
		, TRUNC(456, -1); # 1의 자리부터는 마이너스(-)로 표현

• CEIL

SELECT
		  CEIL(숫자)

SELECT
		  CEIL(1.2345) # 양수
		, CEIL(-456.789); # 음수

• FLOOR

SELECT 
		  FLOOR(숫자)

SELECT
		  FLOOR(1.2345) # 양수
		, FLOOR(-456.789); # 음수

4.1.2. SAFE_MULTIPLY/ SAFE_DIVIDE

• SAFE_MULTIPLY

SELECT
			SAFE_MULTIPLY(숫자, 숫자)

SELECT
			SAFE_MULTIPLY(10, 2)
	  , SAFE_MULTIPLY(10, null); # 결과는 NULL로 출력된다.

• SAFE_DIVIDE

SELECT
		  SAFE_DIVIDE(숫자, 나눌 숫자)

SELECT
		  SAFE_DIVIDE(10, 3)
		, SAFE_DIVIDE(10, 0); # 0으로 나눌 경우 NULL로 출력

4.1.3. MOD/ ABS

• MOD

SELECT 
		  MOD(숫자, 나눌 숫자)

SELECT 
		  MOD(17, 3); # 나머지 2 

• ABS

SELECT
		  ABS(숫자)

SELECT
		  ABS(-1);

4.1.4. POWER/ SQRT

• POWER

SELECT
		  POWER(숫자, 승수)

SELECT
		  POWER(10, 2)
		, POWER(2, 4);

• SQRT

SELECT
		  SQRT(숫자)

SELECT
		  SQRT(4)
		, SQRT(25);

4.1.5. EXP/ LOG

• EXP

SELECT
		  EXP(숫자)

SELECT
		  EXP(2);

• LOG

SELECT
		  LOG(진수, 밑)

SELECT
		  LOG(100, 10) # 밑 10
		, LOG(100); # 자연로그

4.1.6. RAND

SELECT
		  RAND(SEED)

SELECT
		  RAND();

4.2. 문자형 함수

4.2.1. SUBSTR/ LEFT/ RIGHT

• SUBSTR

SELECT
		  SUBSTR(문자열, 시작 인덱스, 길이)

SELECT
		  SUBSTR("BigQuery is fun", 13, 3); # 13번째 문자열부터 3개의 문자열 출력

SELECT  
	    phone_number
	  , SUBSTR(phone_number, 5, 3)
FROM `HR.employees`;

• LEFT

SELECT
		  LEFT(문자열, 길이)

SELECT
		  LEFT("BigQuery is fun", 8); # 왼쪽에서부터 8번째 문자열까지 출력 

SELECT  
	    phone_number
	  , LEFT(phone_number, 3) 
FROM `HR.employees`;

• RIGHT

SELECT
		  RIGHT(문자열, 길이)

SELECT
		  RIGHT("BigQuery is fun", 3); # 오른쪽에서부터 3번째 문자열까지 출력 

SELECT  
	    phone_number
	  , RIGHT(phone_number, 4) 
FROM `HR.employees`;

4.2.2. CONCAT

SELECT
		  CONCAT(문자열1, 문자열2, ...)

SELECT
		  CONCAT("Big", "Query", " ", "is", " ", "fun");

SELECT  
	    first_name
	  , last_name
	  , CONCAT(first_name, last_name) AS full_name
FROM `HR.employees`;

SELECT
		  "Big"||"Query"||" "||"is"||" "||"fun";

4.2.3. LOWER/ UPPER/ INITCAP

• LOWER

SELECT
		  LOWER(문자열)

SELECT 
		  LOWER("BigQuery is fun");

• UPPER

SELECT
		  UPPER(문자열)

SELECT 
		  UPPER("BigQuery is fun");

• INITCAP

SELECT
		  INITCAP(문자열)

SELECT 
		  INITCAP("bigQuery");

4.2.4. REPLACE

SELECT
		  REPLACE(문자열, 대체 대상 문자열, 대체 문자열)

SELECT
		  REPLACE("BigQuery is fun", "fun", "fantastic");

SELECT 
	    phone_number
	  , REPLACE(phone_number, ".", "-")
FROM `HR.employees`;

4.2.5. LENGTH

SELECT
		  LENGTH(문자열)

SELECT
		  LENGTH("BigQuery");

SELECT  
	    first_name
	  , LENGTH(first_name) AS first_name_length
FROM `HR.employees`;

4.2.6. INSTR

SELECT
		  INSTR(문자열, 위치를 찾을 문자)

SELECT
		  INSTR("BigQuery is fun", 'Q');

4.2.7. LPAD/ RPAD

• LPAD

SELECT
		  LPAD(문자열, 길이, 특정문자열)

SELECT
		  LPAD("12345", 10 , "0");

• RPAD

SELECT
		  RPAD(문자열, 길이, 특정문자열)

SELECT
		  RPAD("12345", 10, "0");

4.2.8. LTRIM/ RTRIM

• LTRIM

SELECT
		  LTRIM(문자열)

SELECT
		  LTRIM("    BigQuery is Fun    ");

• RTRIM

SELECT
		  RTRIM(문자열)

SELECT
		  RTRIM("    BigQuery is Fun    ");

4.2.9. STARTS_WITH/ ENDS_WITH

• STARTS_WITH

SELECT
		  STARTS_WITH(문자열, 특정문자열)

SELECT
		  STARTS_WITH("BigQuery is fun", "B")  # TRUE
		, STARTS_WITH("BigQuery is fun", "b"); # FALSE

SELECT
	    first_name
		, STARTS_WITH(first_name, "S") 
FROM `HR.employees`;

• ENDS_WITH

SELECT
		  ENDS_WITH(문자열, 특정문자열)

SELECT
		  ENDS_WITH("BigQuery is fun", "n")  # TRUE
		, ENDS_WITH("BigQuery is fun", "N"); # FALSE

SELECT
	    first_name
		, ENDS_WITH(first_name, "a") 
FROM `HR.employees`;

4.2.10. SPLIT

• SPLIT

SELECT
		  SPLIT(문자열, 구분자)

SELECT
		  SPLIT("BigQuery is fun", " ")
		, SPLIT("apple,banana,cherry", ",");

SELECT
	    job_id
	  , SPLIT(job_id, "_")
FROM `HR.employees`;

4.2.11. REPEAT

SELECT
		  REPEAT(문자열, 횟수)

SELECT
		  REPEAT("BiqQuery", 5);

4.2.12. ASCII

SELECT
		  ASCII(문자열)

SELECT
		  ASCII("A")   # A의 아스키코드번호
		, ASCII("H");  # B의 아스키코드번호 

4.2.13. 정규표현식 함수

• REGEXP_CONTAINS

SELECT
			REGEXP_CONTAINS(string, pattern) 

SELECT
      REGEXP_CONTAINS(job_id, r"IT|AD")
    , job_id
FROM `HR.employees`;

• REGEXP_SUBSTR

SELECT 
			REGEXP_SUBSTR(string, pattern)

SELECT
			first_name
    , REGEXP_SUBSTR(first_name, r"[A-Za-z]+en")
FROM `HR.employees`;

• REGEXP_EXTRACT

SELECT
			REGEXP_EXTRACT(string, pattern, group)

• REGEXP_SUBSTR()와 REGEXP_EXTRACT() 함수의 차이

SELECT
      street_address
    , REGEXP_EXTRACT(street_address, r"[A-Za-z ]+")
FROM `HR.locations`;

• REGEXP_EXTRACT_ALL

SELECT
			REGEXP_EXTRACT_ALL(string, pattern)

SELECT
      street_address
    , REGEXP_EXTRACT_ALL(street_address, r"[A-Za-z]+")
FROM `HR.locations`;

• REGEXP_INSTR

SELECT
			REGEXP_INSTR(string, pattern)

SELECT
      REGEXP_INSTR("BigQuery is wonderful", r"[a-z]+ful");

• REGEXP_REPLACE

SELECT
			REGEXP_REPLACE(string, pattern, replacement)

SELECT 
			email
    , REGEXP_REPLACE(email, r"^(...)", "***") AS masked_email
FROM `HR.employees`;

4.3. 날짜형 함수

4.3.1. DATE 함수

• CURRENT_DATE
CURRENT_DATE() 함수는 현재 날짜를 반환합니다.
이 함수는 다음과 같은 인수를 지원합니다.
• time_zone_expression(시간대 표현식): 시간대를 나타내는 STRING 표현식입니다. 시간대가 지정되지 않은 경우 국제 표준 시간대인 UTC가 사용됩니다.

CURRENT_DATE(time_zone_expression)

SELECT 
			CURRENT_DATE() as Today
    , CURRENT_DATE('Asia/Seoul') AS KOR
    , CURRENT_DATE('US/Pacific') AS US;

• DATE
DATE() 함수는 다양한 형태의 입력을 받아 표준 날짜 형식으로 변환하는 데 사용됩니다. 이 함수는 문자열, 정수, 또는 날짜 및 시간을 나타내는 다른 데이터 타입으로부터 날짜값을 생성할 수 있습니다.
함수는 다음과 같은 인수를 지원합니다.
• year: 연도를 나타내는 INT64 값입니다.
• month: 월을 나타내는 INT64 값입니다.
• day: 일을 나타내는 INT64 값입니다.
• datetime_expression: 날짜와 시간 정보를 포함하는 DATETIME 표현식입니다.
• timestamp_expression: 날짜와 시간 정보를 포함하는 TIMESTAMP 표현식입니다.
• time_zone (선택 사항): 시간대를 나타내는 STRING 표현식입니다. timestamp_expression과 함께 시간대가 지정되지 않은 경우, 국제 표준 시간대인 UTC가 사용됩니다.

DATE(year, month, day)
DATE(datetime_expression)
DATE(timestamp_expression [, time_zone])

SELECT
      DATE(2023, 01, 01) AS date_ymd
    , DATE(DATETIME '2023-01-01 23:59:59') AS date_dt
    , DATE(TIMESTAMP '2023-01-01 05:30:00+09', 'Asia/Seoul') AS date_tstz;

• FORMAT_DATE
FORMAT_DATE() 함수는 DATE형 날짜 값을 지정된 형식의 문자열로 변환합니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• format_string: 날짜 형식을 지정하는 문자열입니다. 예를 들면, "%Y-%m-%d"는 "년-월-일" 형식으로 날짜를 표시하라는 의미입니다.
• date_expression: 날짜 정보를 포함하는 DATETIME 표현식입니다.

FORMAT_DATE(format_string, date_expression)

SELECT
      FORMAT_DATE('%Y-%m-%d', DATE '2023-1-25') AS KR_format
    , FORMAT_DATE('%y/%m/%d', DATE '2023-1-25') AS KR_format
    , FORMAT_DATE('%x', DATE '2023-1-25') AS US_format
    , FORMAT_DATE('%b-%d-%Y', DATE '2023-1-25') AS US_format;

SELECT
      start_date AS origin_data
    , FORMAT_DATE('%Y-%m-%d', start_date) AS KR_format_1
    , FORMAT_DATE('%y/%m/%d', start_date) AS KR_format_2
    , FORMAT_DATE('%x', start_date) AS US_format_1
    , FORMAT_DATE('%b-%d-%Y', start_date) AS US_format_2
FROM `HR.job_history`
LIMIT 5;

4.3.2. TIME 함수

• CURRENT_TIME
CURRENT_TIME() 함수는 현재 시간을 TIME 객체로 반환합니다. 시간대를 설정하는 인자가 주어지지 않은 경우 현재의 UTC가 반환되며 괄호는 선택 사항입니다. 현재 시간은 해당 함수를 포함하는 쿼리 문이 시작될 때 기록됩니다.
💡
UTC : 국제적인 표준 시간, 한국은 UTC+9 hour(KST)입니다.
해당 함수는 다음과 같은 인수를 지원합니다:
• time_zone(시간대)를 설정하는 매개변수를 지원합니다.

CURRENT_TIME([time_zone])

SELECT 
			CURRENT_TIME() AS UTC # DEFAULT: UTC, or Universal Time Coordinated
		, CURRENT_TIME('Asia/Seoul') AS Seoul;

• TIME
TIME() 입력값을 TIME 객체로 반환합니다. 반환된 시간은 시, 분, 초 및 소수 초를 포함하며, BigQuery의 기본 시간대인 UTC를 기준으로 합니다. 인수 없이 호출될 때 괄호는 선택 사항이며 반환되는 현재 시간은 해당 함수가 포함된 쿼리 문이 실행될 때 기록됩니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• INT64 값을 사용하여 시간, 분, 초로 구성된 TIME 객체를 반환합니다.
• TIMESTAMP 객체를 사용하여 TIME 객체를 반환합니다. 시간대가 지정되지 않은 경우, 국제 표준 시간대인 UTC가 사용됩니다.
• DATETIME 객체를 사용하여 TIME 객체를 반환합니다.

TIME(hour, minute, second)
TIME(timestamp, [time_zone])
TIME(datetime)

SELECT
      TIME(12, 30, 00) AS time_hms
    , TIME(TIMESTAMP "2023-01-25 12:30:00", "Asia/Seoul") AS time_tstz
		, TIME(DATETIME "2023-01-25 12:30:00") AS time_dt

• FORMAT_TIME
FORMAT_TIME() 함수는 TIME형 데이터의 표현을 지정된 형식의 문자열로 변환하는 데 사용됩니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• format_string: 시간 형식을 지정하는 문자열입니다. 예를 들어, "%H:%M:%S"는 "시:분:초" 형식으로 시간을 표시하라는 의미입니다.
• time_expression: 시간 정보를 포함하는 TIME 표현식입니다.

FORMAT_TIME(format_string, time_expression)

SELECT 
			 FORMAT_TIME("%R", TIME "15:30:00") as formatted_time1
     , FORMAT_TIME("%X", TIME "15:30:00") as formatted_time2
     , FORMAT_TIME("%P", TIME "15:30:00") as formatted_time3

4.3.3. DATETIME 함수

• CURRENT_DATETIME
CURRENT_DATETIME() 현재 시간을 DATETIME 객체로 반환합니다. 인수 없이 호출될 때 괄호는 선택 사항입니다. 시간대(Time zone)를 지정하는 방법에 대한 정보는 시간대 정의(Time zone definitions)를 참조하십시오.
현재 날짜와 시간은 해당 함수가 포함된 쿼리 문이 실행될 때 기록됩니다.
이 함수는 다음과 같은 인수를 지원합니다.
• time_zone: 시간대를 설정하는 매개변수를 지원합니다(STRING 표현식입니다). 시간대가 지정되지 않은 경우 국제 표준 시간대인 UTC가 사용됩니다

CURRENT_DATETIME([time_zone])

SELECT 
			CURRENT_DATETIME() as now_UTC
    , CURRENT_DATETIME('Asia/Seoul') as now_Seoul;

• DATETIME
DATETIME() 입력값을 DATEIME 객체로 반환합니다. 시간대를 설정하지 않은 경우 BigQuery의 기본 시간대인 UTC를 기준으로 반환됩니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• year: 연도를 나타내는 INT64 값입니다.
• month: 월을 나타내는 INT64 값입니다.
• day: 일을 나타내는 INT64 값입니다.
• hour: 시간을 나타내는 INT64 값입니다.
• minute: 분을 나타내는 INT64 값입니다.
• second: 초를 나타내는 INT64 값입니다.
• DATE 및 TIME 객체를 통해 DATETIME 객체를 반환합니다. 시간대가 지정되지 않은 경우 기본 시간대인 UTC가 사용됩니다.
• TIMESTAMP 객체를 사용하여 DATETIME 객체를 반환합니다. 시간대가 지정되지 않은 경우 기본 시간대인 UTC가 사용됩니다.

DATETIME(year, month, day, hour, minute, second)
DATETIME(date_expression[, time_expression])
DATETIME(timestamp_expression [, time_zone])

SELECT
      DATETIME(2023, 01, 30, 06, 30, 00) as datetime_1
    , DATETIME(DATE(2023, 01, 30), TIME(06, 30, 00)) as datetime_2
    , DATETIME(TIMESTAMP "2023-01-30 06:30:00") as datetime_UTC
    , DATETIME(TIMESTAMP "2023-01-30 06:30:00", "Asia/Seoul") as datetime_Seoul

• FORMAT_DATETIME
FORMAT_DATETIME() 함수는 날짜 및 시간 값을 지정된 형식의 문자열로 변환하는 데 사용됩니다. BigQuery가 지원하는 형식 요소 목록은 공식 문서에서 지원하는 BigQuery의 ’format_string’ 자료에서 찾아볼 수 있습니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• format_string: 날짜 및 시간 형식을 지정하는 문자열입니다. 예를 들면, "%Y-%m-%d %H:%M:%S"는 "년-월-일 시:분:초" 형식으로 날짜와 시간을 표시하라는 의미입니다.
• datetime_expression: DATETIME 형의 데이터

FORMAT_DATETIME(format_string, datetime_expression)

SELECT
      FORMAT_DATETIME('%c', DATETIME '2023-01-25 15:30:00') AS format1
    , FORMAT_DATETIME('%Y-%m-%d', DATETIME '2023-1-25 15:30:00') AS KR_format1
    , FORMAT_DATETIME('%y/%m/%d', DATETIME '2023-1-25 15:30:00') AS KR_format2
    , FORMAT_DATETIME('%x', DATETIME '2023-1-25 15:30:00') AS US_format1
    , FORMAT_DATETIME('%b-%d-%Y', DATETIME '2023-1-25 15:30:00') AS US_format2;

4.3.4. TIMESTAMP 함수

• CURRENT_TIMESTAMP
CURRENT_TIMESTAMP() 현재의 UTC(세계 표준시) 타임스탬프 값을 반환하는 함수입니다. 해당 함수를 사용하면 SQL 쿼리 내에서 현재 시점의 날짜와 시간 정보를 얻을 수 있습니다. 현재 날짜와 시간은 해당 함수가 포함된 쿼리 문이 실행될 때 기록됩니다. 시간대 변환 또는 다른 날짜 및 시간 조작을 수행하려면 추가 함수나 연산을 함께 사용해야 합니다.

CURRENT_TIMESTAMP()

SELECT CURRENT_TIMESTAMP();

• TIMESTAMP
TIMESTAMP() 함수는 주어진 문자열이나 다른 날짜 및 시간 관련 값들을 TIMESTAMP 데이터 타입으로 변환하는데 사용됩니다.
 
문자열에서의 변환
주어진 문자열이 올바른 형식을 갖추고 있을 때, 해당 함수는 그 문자열을 TIMESTAMP값으로 변환합니다. 하지만 문자열을 TIMESTAMP로 변환할 때, 문자열 형식이 올바르게 맞춰져 있지 않으면 에러가 발생할 수 있습니다.
날짜/시간 데이터 타입에서의 변환
DATE 또는 DATETIME 값을 TIMESTAMP로 변환할 수 있습니다. TIMESTAMP는 UTC를 기반으로 하기 때문에, 특정 시간대를 반영하려면 추가적인 변환 작업이 필요할 수 있습니다.

TIMESTAMP(string_expression[, time_zone])
TIMESTAMP(date_expression[, time_zone])
TIMESTAMP(datetime_expression[, time_zone])

SELECT 
			TIMESTAMP("2023-01-25 15:30:00+09") AS timestamp_str1
		, TIMESTAMP("2023-01-25 15:30:00", "Asia/Seoul") AS timestamp_str2
		, TIMESTAMP("2023-01-25 15:30:00 UTC") AS timestamp_str3
		, TIMESTAMP(DATETIME "2023-01-25 15:30:00") AS timestamp_datetime
		, TIMESTAMP(DATE "2023-01-25") AS timestamp_date;

• FORMAT_TIMESTAMP
FORMAT_DATETIME() 함수는 주어진 포맷에 따라 TIMESTAMP 값을 문자열로 변환하는 데 사용됩니다. 이 함수는 데이터 분석과 보고를 위해 TIMESTAMP 값을 보다 읽기 쉬운 형태로 변환할 때 유용합니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• format_string: 변환된 문자열의 형식을 지정하는 문자열입니다. 예를 들어, '%Y-%m-%d %H:%M:%S'는 '2023-10-26 14:23:45'와 같은 형식으로 반환됩니다.
• timestamp_expression: 변환할 TIMESTAMP 값을 지정하는 표현식입니다.
• timezone: (선택 사항) 시간대를 지정하는 문자열입니다. 지정하지 않으면 UTC가 기본값으로 사용됩니다.

FORMAT_TIMESTAMP(format_string, timestamp_expression[, timezone])

SELECT 
			FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP "2023-01-25 15:30:00") AS UTC
		,	FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP "2023-01-25 15:30:00", 'Asia/Seoul') AS Seoul;

4.3.5. EXTRACT 함수

• EXTRACT
EXTRACT() 함수는 BigQuery에서 주어진 날짜, 시간, 또는 타임스탬프 값에서 특정 부분 (예: 연도, 월, 일)을 추출하는 데 사용되는 함수입니다. 반환된 값은 더 낮은 순서의 시간 단위를 잘라냅니다.
예를 들어, 초를 추출할 때, EXTRACT() 함수는 MICROSECOND, MILLISECOND 값을 반환합니다. 이 함수는 데이터 분석 중 특정 시간 요소에 기반한 질의나 집계를 수행할 때 유용하게 사용될 수 있습니다.
• part
• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAYOFWEEK: 일주일 중 첫 번째 날인 일요일을 시작으로 [1,7] 범위의 값을 반환합니다.
• DAY: 날짜를 반환합니다.
• DAYOFYEAR: 연중 날짜를 반환합니다.
• WEEK: 날짜의 주 번호를 반환하며, [0, 53] 범위에 있습니다. 주는 일요일부터 시작하며, 연초의 첫 일요일 이전의 날짜는 0주에 속합니다.
• WEEK(<WEEKDAY>): 날짜의 [0, 53] 범위 주 번호를 반환합니다. 주의 시작은 <WEEKDAY>이며, 연 초의 첫 <WEEKDAY> 이전의 날짜는 0주에 속합니다. <WEEKDAY>로 지정할 수 있는 유효한 값은 SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY입니다.
• MONTH: 월을 반환합니다.
• QUARTER: [1,4] 범위의 값을 반환합니다.
• YEAR: 연도를 반환합니다.
• date_expression: DATE, TIME, DATETIME 또는 TIMESTAMP 표현식입니다.

EXTRACT(part FROM date_or_time_expression)

SELECT 
			EXTRACT(DAY FROM DATE '2023-1-25') AS the_day;

SELECT 
      start_date AS origin_data
    , EXTRACT(YEAR FROM DATE(start_date)) AS YEAR
    , EXTRACT(MONTH FROM DATE(start_date)) AS MONTH
    , EXTRACT(DAY FROM DATE(start_date)) AS DAY
    , EXTRACT(WEEK(MONDAY) FROM DATE(start_date)) AS WEEKDAY
FROM `HR.job_history`
limit 5;

SELECT
			EXTRACT(HOUR FROM TIME "15:30:00") as hour
		, EXTRACT(MINUTE FROM TIME "15:30:00") as minute
		, EXTRACT(SECOND FROM TIME "15:30:00") as second

4.3.6. 날짜 및 시간 연산 함수

• DATE_ADD, TIME_ADD, DATETIME_ADD, TIMESTAMP_ADD
BigQuery에서는 해당 함수들을 통해 날짜와 시간에 특정 간격을 더할 수 있습니다. 일반적으로 날짜 및 시간 연산을 수행할 때 연산 결과가 유효한 날짜 및 시간 값인지 확인해야 합니다. 날짜 연산을 할 때 특정한 상황이나 예외 케이스에 대해 알고 있지 않으면 예기치 않은 결과나 오류를 만날 수 있습니다. 특히, 윤년과 같이 규칙적이지만 특별한 조건을 갖는 날짜의 경우, 연산을 잘못 수행하면 데이터 분석 결과에 오차가 발생할 수 있습니다. 예를 들어, 2월 28일에 2일을 더하면 어떻게 될지 고려해야 합니다. 이런 차이를 고려하지 않으면 해당 기간의 데이터 집계나 분석에 오류가 생길 수 있습니다. 해당 함수는 다음과 같은 인수를 지원합니다.
• date_or_time_expression: DATE, TIME, DATETIME 또는 TIMESTAMP 표현식입니다.
• int64_expression: 날짜 혹은 시간에 특정 간격을 더할 정수값 입니다.

-- DATE
DATE_ADD(date_expression, INTERVAL int64_expression date_part)

-- TIME
TIME_ADD(time_expression, INTERVAL int64_expression part)

-- DATETIME
DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)

-- TIMESTAMP
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

SELECT
			DATE "2023-01-25" AS original_time
		, DATE_ADD(DATE "2023-01-25", INTERVAL 5 DAY) AS later
		, DATE_ADD(DATE "2023-01-25", INTERVAL -5 DAY) AS before;

SELECT
      TIME "15:30:00" AS original_time
    , TIME_ADD(TIME "15:30:00", INTERVAL 30 MINUTE) AS later
    , TIME_ADD(TIME "15:30:00", INTERVAL -30 MINUTE) AS before;

SELECT
      DATETIME "2023-01-25 15:30:00" original_time
    , DATETIME_ADD(DATETIME "2023-01-25 15:30:00", INTERVAL 5 DAY) later_day
    , DATETIME_ADD(DATETIME "2023-01-25 15:30:00", INTERVAL -5 DAY) before_day
    , DATETIME_ADD(DATETIME "2023-01-25 15:30:00", INTERVAL 5 HOUR) later_hour;

SELECT
      TIMESTAMP "2023-01-25 15:30:00" original_time
    , TIMESTAMP_ADD(TIMESTAMP "2023-01-25 15:30:00", INTERVAL 5 DAY) later_day
    , TIMESTAMP_ADD(TIMESTAMP "2023-01-25 15:30:00", INTERVAL 30 MINUTE) later_minute
    , TIMESTAMP_ADD(TIMESTAMP "2023-01-25 15:30:00", INTERVAL -30 MINUTE) before_minute;

• DATE_SUB(), TIME_SUB(), DATETIME_SUB(), TIMESTAMP_SUB()
BigQuery에서는 해당 함수들을 사용하여 날짜와 시간에서 특정 간격을 뺄 수 있습니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• date_or_time_expression: DATE, TIME, DATETIME 또는 TIMESTAMP 표현식입니다.
• int64_expression: 날짜 혹은 시간에 특정 간격을 더할 정수값입니다.

-- DATE
DATE_SUB(date_expression, INTERVAL int64_expression date_part)

-- TIME
TIME_SUB(time_expression, INTERVAL int64_expression part)

-- DATETIME
DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)

-- TIMESTAMP
TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

SELECT
      DATE "2023-01-25" original_time
    , DATE_SUB(DATE "2023-01-25", INTERVAL 5 DAY) before;

SELECT
      TIME "15:30:00" original_time
    , TIME_SUB(TIME "15:30:00", INTERVAL 30 MINUTE) before;

SELECT
      DATETIME "2023-01-25 15:30:00" original_time
    , DATETIME_SUB(DATETIME "2023-01-25 15:30:00", INTERVAL 5 DAY) before_day
    , DATETIME_SUB(DATETIME "2023-01-25 15:30:00", INTERVAL 5 HOUR) before_hour;

SELECT
      TIMESTAMP "2023-01-25 15:30:00" original_time
    , TIMESTAMP_SUB(TIMESTAMP "2023-01-25 15:30:00", INTERVAL 5 DAY) before_day
    , TIMESTAMP_SUB(TIMESTAMP "2023-01-25 15:30:00", INTERVAL 30 MINUTE) before_minute;

• DATE_DIFF, TIME_DIFF, DATETIME_DIFF, TIMESTAMP_DIFF
BigQuery에서 해당 함수들은 두 날짜 및 시간 값 사이의 차이를 계산하는 데 사용됩니다. 기본적으로 해당 함수들은 첫 번째 인자에서 두 번째 인자를 뺀 결과를 반환합니다. 간격을 계산할 때 주어진 날짜와 시간 값의 순서에 따라 결과가 양수 또는 음수로 나타날 수 있습니다. 또한 시간대를 고려하지 않으면 예상하지 못한 결과가 나올 수 있으므로, 특히 TIMESTAMP 값을 비교할 때 주의가 필요합니다.
해당 함수는 다음과 같은 인수를 지원합니다.
• date_or_time_expression: DATE, TIME, DATETIME 또는 TIMESTAMP 표현식입니다.
• int64_expression: 날짜 혹은 시간에 특정 간격을 더할 정수값입니다.

-- DATE
DATE_DIFF(date_expression_a, date_expression_b, INTERVAL)

-- TIME
TIME_DIFF(time_expression_a, time_expression_b, INTERVAL)

-- DATETIME
DATETIME_DIFF(datetime_expression_a, datetime_expression_b, INTERVAL)

-- TIMESTAMP
TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, INTERVAL)

SELECT
      DATE_DIFF(DATE '2023-12-31', DATE '2023-01-01', DAY) AS day_diff
    , TIME_DIFF(TIME '23:59:59', TIME '00:00:00', SECOND) AS second_diff
    , DATETIME_DIFF(DATETIME '2023-12-31 23:59:59', DATETIME '2023-01-01 00:00:00', HOUR) AS hour_diff
    , TIMESTAMP_DIFF(TIMESTAMP '2023-12-31 23:59:59 UTC', TIMESTAMP '2023-01-01 00:00:00 UTC', MINUTE) AS minute_diff

4.4. 형변환(Casting)

1. 문자열 변환:
• 숫자, 날짜, 시간, 불린(Boolean) 등의 값을 문자열로 변환할 수 있습니다.

2. 숫자 변환:
• 문자열, 부동 소수점 등의 값을 정수나 다른 숫자 유형으로 변환할 수 있습니다.

3. 날짜와 시간 변환:
• 문자열을 날짜, 시간, 데이트타임, 타임스탬프 유형으로 변환할 수 있으며, 그 반대의 변환도 가능합니다.

4. 불린 변환:
• 문자열, 숫자 등의 값을 불린 값(TRUE 또는 FALSE)으로 변환할 수 있습니다.

1. 데이터 손실:
• 특히, 숫자를 정수로 형변환할 때나, 큰 숫자를 작은 범위의 숫자 유형으로 변환할 때 소수점 아래의 값이나 큰 값의 일부가 잘릴 수 있으며 이는 잠재적인 정보의 손실로 이어질 수 있습니다.

2. 무효한 값 변환:
• 유효하지 않은 형식의 데이터를 변환하려고 할 때 오류가 발생할 수 있습니다. 예를 들어, "ABC"와 같은 문자열을 정수로 변환하려고 할 때입니다.

3. 시간과 날짜 변환:
• 시간대의 차이나 윤년과 같은 복잡한 요소로 인해 예상치 못한 결과가 발생할 수 있습니다. 특히 문자열을 날짜나 시간으로 변환할 때는 입력 형식이 올바른지 확인해야 합니다.

4. 문자열 변환의 인코딩 문제:
• BYTES를 STRING으로 변환할 때, 바이트 시퀀스가 유효한 UTF-8 문자열로 해석될 수 없으면 오류가 발생합니다.

5. 충분한 테스트:
• 형변환을 포함하는 쿼리를 실행하기 전에 충분한 테스트가 필요합니다. 이를 통해 예상치 못한 오류나 결과를 미리 발견하고 수정할 수 있습니다.

6. 성능 고려:
• 대규모의 데이터셋에 대한 형변환은 성능에 영향을 줄 수 있습니다. 필요한 형변환만 수행하고, 가능하면 데이터를 로드할 때 원하는 유형으로 데이터를 불러오는 것이 좋습니다.
 

4.4.1. CAST

• CAST
BigQuery에서 데이터의 유형을 다른 유형으로 변환하려고 할 때 CAST() 구문을 사용합니다. 해당 함수는 특정 표현식의 결과 값을 다른 데이터 유형으로 바꿀 필요가 있을 때 쿼리 내에서 활용됩니다. 해당 함수는 다음과 같은 인수를 지원합니다.
• expression: 데이터 유형을 변경할 값을 가진 표현식입니다.
• target_type: 변환하고자 하는 목표 데이터 유형입니다.

CAST(expression AS target_type)

SELECT 
			CAST('12345' AS INT64) AS int_result
		, CAST('123.45' AS FLOAT64) AS float_result
		, CAST(123.45 AS NUMERIC) AS numeric_result
		, CAST(123.45 AS INTEGER) AS numeric_result

SELECT 
			CAST('2023-10-26' AS DATE) AS date_result
		, CAST('12:34:56' AS TIME) AS time_result
		, CAST('2023-10-26 12:34:56' AS DATETIME) AS datetime_result
		, CAST('2023-10-26 12:34:56 UTC' AS TIMESTAMP) AS timestamp_result;

SELECT.
			CAST(1 AS BOOL) AS bool_result1
		,	CAST(12 AS BOOL) AS bool_result2
		,	CAST(0 AS BOOL) AS bool_result3
		,	CAST(-1 AS BOOL) AS bool_result4

• SAFE_CAST
모든 형변환 작업이 성공적으로 완료되는 것은 아닙니다. 때로는 요청된 형변환을 수행할 수 없어 변환에 실패할 수 있습니다. 이러한 상황에서 쿼리의 안정성을 유지하고 싶다면 SAFE_CAST() 함수를 사용할 수 있습니다.
해당 함수는 CAST() 함수와 다르게, 형변환 중 에러가 발생하면 에러 메시지를 반환하지 않고 NULL 값을 반환합니다.
• expression: 데이터 유형을 변경할 값을 가진 표현식입니다.
• target_type: 변환하고자 하는 목표 데이터 유형입니다.

SAFE_CAST(expression AS target_type)

SELECT 
			SAFE_CAST('123' AS INT64) `Integer`
		, SAFE_CAST('ABC' AS INT64) `Fail1`
		, SAFE_CAST('123.45' AS FLOAT64) `Float`
		, SAFE_CAST('Hello' AS FLOAT64) `Fail2`
		, SAFE_CAST(123 AS STRING) `String`;

4.4.2. PARSE

• PARSE_DATE
해당 함수는 문자열 형태의 날짜 데이터를 DATE 타입으로 변환하는데 사용되는 BigQuery의 내장 함수입니다. 예를 들어, 'YYYY-MM-DD' 형식의 문자열을 DATE 유형으로 파싱할 수 있습니다.
Format_string과 입력 문자열과 정확히 일치하지 않으면 오류를 반환합니다. 예를 들어, DATE 유형으로 변환하려는 데이터가 ‘20230101’과 같은 형식이라면, format_string을 ‘%Y%m%d’로 입력해야 합니다.

• PARSE_TIME
해당 함수는 주어진 시간 형식의 문자열을 TIME 데이터 유형으로 변환합니다. 시간만을 나타내는 문자열 정보를 처리할 때 유용합니다.
Format_string과 입력 문자열과 정확히 일치하지 않으면 오류를 반환합니다. 예를 들어, TIME 유형으로 변환하려는 데이터가 ‘15:30:30’(15시 30분 30초)과 같은 형식이라면, format_string을 ‘%H:%M:%S’로 입력해야 합니다.

• PARSE_DATETIME
해당 함수는 문자열을 DATETIME 유형으로 변환합니다. 특정한 문자열 형태의 날짜 및 시간 정보를 DATETIME 데이터 타입으로 변환할 수 있습니다.
Format_string과 입력 문자열과 정확히 일치하지 않으면 오류를 반환합니다. 예를 들어, DATETIME 유형으로 변환하려는 데이터가 ‘2023-01-01 15:30:30’와 같은 형식이라면, format_string을 ‘%Y-%m-%d %H:%M:%S’로 입력해야 합니다.

• PARSE_TIMESTAMP
해당 함수는 문자열을 TIMESTAMP 데이터 유형으로 변환합니다. 주어진 패턴이나 형식을 기반으로 문자열을 해석하여, 해당 문자열이 나타내는 정확한 순간을 TIMESTAMP 형식으로 표현할 수 있게 해줍니다.
Format_string과 입력 문자열과 정확히 일치하지 않으면 오류를 반환합니다. 예를 들어, TIMESTAMP 유형으로 변환하려는 데이터가 ‘2023/01/01 15:30:30’과 같은 형식이라면, format_string을 ‘%Y/%m/%d %H:%M:%S’로 입력해야 합니다.
변환할 입력 인자에 timezone(시간대)을 설정할 수 있으며, 반환값은 UTC입니다.
 
해당 함수는 다음과 같은 인수를 지원합니다.
• format_string: 입력 데이터의 형태를 설명합니다. 예를 들어, 연(%Y), 월(%m), 일(%d), 시(%H), 분(%M), 초(%S) 등의 형식을 지정할 수 있습니다. 이 문자열은 추출할 날짜와 시간 데이터의 정확한 구조를 정의하는 데 사용됩니다.
BigQuery가 지원하는 형식 요소 목록은 공식 문서에서 지원하는 BigQuery의 ’format_string’ 자료에서 찾아볼 수 있습니다.
• date/time/datetime/timestamp_string: 실제로 변환하고자 하는 날짜와 시간 정보를 포함하고 있는 STRING 유형의 데이터입니다. Format_string에 정의된 형식을 따라야 합니다.

-- DATE
PARSE_DATE(format_string, date_string)

-- TIME
PARSE_TIME(format_string, time_string)

-- DATETIME
PARSE_DATETIME(format_string, datetime_string)

-- TIMESTAMP
PARSE_TIMESTAMP(format_string, timestamp_string[, time_zone])

SELECT 
			PARSE_DATE('%Y-%m-%d', '2023-01-01') parsed_date
		, PARSE_TIME('%H:%M:%S', '15:30:30') parsed_time
		, PARSE_DATETIME('%Y/%m/%d %H:%M:%S', '2023/01/01 20:00:00') parsed_dt
		, PARSE_TIMESTAMP('%y-%m-%d %H:%M:%S %Z', '23-01-01 20:00:00 Asia/Seoul') parsed_ts;

SELECT 
       PARSE_DATE('%A %b %e %Y', 'Thursday Jan 25 2023') parsed_date
     , PARSE_TIME('%I:%M:%S %p', '3:30:30 pm') parsed_time
     , PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Jan 25 07:30:00 2023") parsed_dt
	     , PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Jan 25 07:30:00 2023") parsed_ts;