[빅쿼리(BigQuery) - SQL] 날짜/시간 계산하기(날짜/시간 함수)

날짜/시간 계산하기(날짜/시간 함수)

 

 

두 날짜 데이터의 차이를 구하거나. 시간 데이터를 기준으로 1시간 후의 시간을 구하는 방법을 소개하겠다.

 

다음 테이블은 서비스 사용자의 등록 시간과 생일 정보를 포함하는 사용자 마스터 테이블이다. 이러한 샘플을 기반으로 회원 등록일 시점의 나이를 계산하는 쿼리를 생각해본다.

 

나이는 시간의 경과에 따라서 변화하므로, 일반적으로 생년월일을 저장하고, 이후에 계산해서 나이를 구한다. 나이를 사용하면 제품의 구매 연령대, 서비스를 사용하는 사용자의 연령 분포 등을 확인할 수 있다.

 

 

 

 

 

기본적인 날짜/시간 데이터 계산 방법으로 정수의 덧셈과 뺄셈을 사용하는 방법을 살펴본다. 코드 예로 회원 등록 시간 1시간 후와 30분 전의 시간, 등록일의 다음 날과 1달 전의 날짜를 계산하는 쿼리를 소개하면 다음과 같다. 날짜/시간 계산 방법은 미들웨어에 따라 다르다.

 

 

 

 

 

# timestamp에 관련된 여러 함수는 https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?hl=ko 에서 찾아볼 수 있다.

 

 

날짜 데이터들의 차이 계산하기

두 날짜 데이터를 사용해서 날짜의 차이를 계산하는 방법을 소개한다. 다음 코드 예는 회원 등록일과 현재 날짜의 날짜 차이, 그리고 회원 등록일과 생년월일과의 날짜 차이를 계산하는 쿼리이다. 날짜 차이를 계산하는 방법은 미들웨어에 따라 다르지만, 대부분 간단하다.

 

 

 

 

 

사용자의 생년월일로 나이 계산하기

날짜 차이를 계산하는 방법은 매우 단순하지만 나이를 계산하는 방법은 조금 복잡하다. 윤년등도 고려해야 하므로, 단순하게 날짜를 365일로 나누어 계산할 수 없기 때문이다.

 

나이를 정확하게 계산하려면, 날짜를 고정 자리 수의 정수로 표현하고 그 차이를 계산하면된다. 예를 들어 다음 코드는 날짜를 정수로 표현해서 나이를 계산하는 쿼리이다. 날짜의 정수 표현이란 예를 들어 2016년 2월 28일은 '20160228', 2000년 2월 29일은 '20000229'처럼 나타내는 것을 의미한다. 이러한 날짜를 빼고 10,000으로 나누면 나이가 계산된다.

 

 

 

 

 

다음 예는 샘플 데이터를 기반으로, 등록 시점과 현재 시점의 나이를 문자열 변환을 사용해 계산하는 쿼리이다. 문자열에서 하이픈을 제거하고 정수로 캐스트 하는 것뿐이지만, 미들웨어에 따라 데이터 자료형 또는 함수의 구현이 다르므로 조금씩 차이가 있다.

 

 

 

 

 

# FLOOR 함수는 값보다 작은 정수 중 가장 큰 수를 가져오는 함수이다. 자세한 설명은 https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions?hl=ko#floor 에서 찾아볼 수 있다.

 

# CAST 함수의 자세한 설명은 https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions?hl=ko 에서 찾아볼 수 있다.

 

# 문자열 함수인 SUBSTRING, REPLACE의 자세한 설명은 https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions?hl=ko#substring 에서 찾아볼 수 있다.