BigQuery7. 데이터 변환 3 (날짜 및 시간 데이터)

정리

• 날짜 및 시간 데이터 타입
  • DATE
  • DATETIME : DATE + TIME. 타임존 정보 X
  • TIMESTAMP : 특정 시점에 도장 찍은 값. 타임존 정보 O
  • UTC : 국제적인 표준 시간. 한국은 UTC+9
  • Millisecond : 1/1000초
  • Microsecond : 1/1000ms

• 날짜 및 시간 데이터 타입 변환 함수

  • TIMESTAMP_MILLIS
  • TIMESTAMP_MICROS
  • DATETIME :
    • PARSE_DATETIME : 문자열->DATETIME
    • FORMAT_DATETIME : DATETIME->문자열

• 날짜 및 시간 데이터 함수

  • CURRENT_DATETIME : 현재 DATETIME 알려주는 함수
  • EXTRACT : DATETIME의 특정 부분 추출
  • DATETIME_TRUNC : DATETIME 특정 부분 자르기
  • DATETIME_DIFF : DATETIME 차이 구하기

---

1. 날짜 및 시간 데이터

- user의 행위를 파악하는 데는 '시간'이 중요
    => 하지만 '시간'과 '개발에 사용하는 시간(TIMESTAMP, DATETIME, UTC 등)'이 같지 않을 때가 많다.
    => 시간 데이터에 대한 이해가 먼저 필요하다!

1.1. 날짜 및 시간 데이터

• DATE : DATE만 표시하는 데이터.

  '2023-12-31'

• DATETIME : DATE + TIME 표시하는 데이터. Time Zone 정보 X.

  '2023-12-31 14:00:00'

• TIME : 날짜와 무관하게 시간만 표시하는 데이터.

  '14:00:00'

1.2. TimeZone

• GMT(Greenwich Mean Time)
  : 영국의 그리니치 천문대(경도 0도)를 기준을 지역에 따른 시간의 차이를 조정하기 위해 생긴 시간의 구분선 (1884년채택).
  => 한국 시간: GMT+9
  

• UTC (Universal Time Coordinated)
  : 국제적인 표준 시간.
  => 한국 시간 : UTC+9

  타임존이 존재한다면 => 특정 지역의 표준 시간대를 말한다.

  UTC+9 => 한국 지역의 표준 시간대

• TIMESTAMP
  : UTC로부터 경과한 시간을 나타내는 값. TimeZone 정보 O

  '2023-12-31 14:00:00 UTC'

1.3. Millisecond, Microsecond

• millisecond (ms) : 시간의 단위. 1/1000 초

  • 개발 서버처럼, 초보다 더 정확하게 빠른 반응이 필요한 분야에서 사용
  • Millisecond -> TIMESTAMP -> DATETIME 순으로 변경

• microsecond (µs) : 1/1000ms. 1/1000000 초

  • µs보단 ms를 자주 사용

    ms(1704176819711), µs(1704176819711000) -> TIMESTAMP로 변환하기

    SELECT
    --TIMESTAMP_MILLIST(1704176819711) AS mills_to_timestamp_value,
    --TIMESTAMP_MICROS(1704176819711000) AS micros_to timestamp_value
    
    => timestamp로 변환하여 timezone인 'UTC'가 존재

1.4. TIMESTAMP <=> DATETIME

• TIMESTAMP <=> DATETIME 변환에서 TIMEZONE 누락을 주의해야 한다
  

  ms -> TIMESTAMP -> DATETIME으로 변환하기

  SELECT
  --TIMESTAMP_MILLIST(1704176819711) AS mills_to_timestamp_value,
  --DATETIME(TIMESTAMP_MILLIS(1704176819711) AS datetime_value,
  --DATETIME(TIMESTAMP_MILLIS(1704176819711, 'Asia/Seoul') AS datetime_value_asia
  
  =>mills_to_timestamp_value와 datetime_value의 시간이 '06:26:59.71100'으로 차이가 없음
  ->즉, datetime_value는 TimeZone을 누락한 잘못된 쿼리
  ->datetime_value_asia는 제대로 된 결과 출력
  =>'Asia/Seoul'이라는 TimeZone을 제대로 작성했기 때문!

---

2. 날짜 및 시간 데이터 함수

• DATETIME 함수 위주로 다루었으나, 대부분 TIMESTAMP와 DATE에도 적용 가능한 함수이다

2.1. CURRENT_DATETIME

• CURRENT_DATETIME(timezone): 현재 DATETIME 출력

  SELECT
  --CURRENT_DATE() AS curret_date,
  --CURRENT_DATE("Asia/Seoul') AS asia_date,
  --CURRENT_DATETIME() AS current_datetime,
  --CURRENT_DATETIME("Asia/Seoul") AS current_datetime_asia
  
  current_datetime과 current_datetime_asia : 같은 날짜 & 9시간 차이

• "Asia/Seoul" : TimeZone => Asia 지역의 특정 표준 시간대가 출력된다

• 보통의 근무시간인 9시-18시 : TimeZone => 시간 차이만 발생시킬 뿐, 날짜는 바뀌지 않는다
• TimeZone에 따라 Date도 달라지는 경우 : 0시-8시 59분에 실행할 때
  • 예) 1월 22일 8시 실행 - Asia 기준
    => 1월 21일 23시 출력 - UTC 기준

2.2. EXTRACT

• EXTRACT(datetime_part FROM datetime) : DATETIME에서 특정 부분만 추출

  • datetime_part : (DATE, YEAR, MONTH, WEEK, DAY, DAYOFWEEK, DAYOFYEAR, WEEK(), ISOWEEK, QUARTER, ISOYEAR, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND) 中 선택 1

• 주로 일자 별 주문, 월 별 주문 등을 집계할 때 사용한다

  SELECT
  --EXTRACT(DATE FROM DATETIME "2024-01-02 14:00:00") AS date,
  --EXTRACT(YEAR FROM DATETIME "2024-01-02 14:00:00") AS year,
  --EXTRACT(MONTH FROM DATETIME "2024-01-02 14:00:00") AS month,
  --EXTRACT(DAY FROM DATETIME "2024-01-02 14:00:00") AS day,
  --EXTRACT(HOUR FROM DATETIME "2024-01-02 14:00:00") AS hour,
  --EXTRACT(MINUTE FROM DATETIME "2024-01-02 14:00:00") AS minute
  

• 요일을 추출하고 싶은 경우 :

  • EXTRACT(DAYOFWEEK FROM datetime) : 한 주의 첫날이 일요일인 [1,7] 범위의 값을 반환
    => 출력 값 1 = sunday, 2 = monday, 3 = tuesday, 4 = wednesday, 5 = thursday, 6 = friday, 7 = saturday

  • 주말 정보만 보고싶은 경우 => IN / CASE WHEN을 활용

  SELECT
  --EXTRACT(DAYOFWEEK FROM DATETIME "2024-01-21 14:00:00") AS day_of_week_sun,
  --EXTRACT(DAYOFWEEK FROM DATETIME "2024-01-22 14:00:00") AS day_of_week_mon,
  --EXTRACT(DAYOFWEEK FROM DATETIME "2024-01-23 14:00:00") AS day_of_week_tues,
  --EXTRACT(DAYOFWEEK FROM DATETIME "2024-01-27 14:00:00") AS day_of_week_satur
  

2.3. DATETIME_TRUNC

• DATETIME_TRUNC(datetime, datetime_part)

  • datetime_part : (DATE, YEAR, MONTH, WEEK, DAY, WEEK(), ISOWEEK, QUARTER, ISOYEAR, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND) 中 선택 1

  SELECT
  --DATETIME "2024-03-02 14:42:13" AS original_data,
  --DATETIME_TRUNC(DATETIME "2024-03-02 14:42:13", DAY) AS day_trunc,
  --DATETIME_TRUNC(DATETIME "2024-03-02 14:42:13", YEAR) AS year_trunc,
  --DATETIME_TRUNC(DATETIME "2024-03-02 14:42:13", MONTH) AS month_trunc,
  --DATETIME_TRUNC(DATETIME "2024-03-02 14:42:13", HOUR) AS hour_trunc;
  

• EXTRACT vs DATETIME_TRUNC

  • 데이터 분석에서 어떻게 활용할지에 따라 다르게 사용
  • DATETIME_TRUNC => 2024-01-01T00:00:00 출력 => '00:00:00'이 필요한가? => 필요없는 경우 EXTRACT 사용

  => EXTRACT는 YEAR, MONTH, DAY를 자를 때 자주 활용
  => DATETIME_TRUNC은 '시간'을 자를 때 자주 활용 (예) 1시간 단위로 수요 집계하는 경우)

2.4. PARSE_DATETIME

• PARSE_DATETIME('문자열의 형태', 'DATETIME 문자열') : 문자열로 저장된 DATETIME -> DATETIME 타입으로 변환

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

2.5. FORMAT_DATETIME

• FORMAT_DATETIME('문자열의 형태', 'DATETIME 문자열') : DATETIME 타입 -> 문자열 데이터로 변환

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

- '%Y-%m-%d %H:%M:%S', '%c'와 같은 요소의 의미는 cloud.google.com 공식 문서의 Format elements에서 확인 가능

2.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
  

  - WEEK(SUNDAY) : 주의 시작을 SUNDAY 기준으로 -> LAST_DAY(WEEK(SUNDAY)) = 토
  - WEEK(MONDAY) : 주의 시작을 MONDAY 기준으로 -> LAST_DAY(WEEK(MONDAY)) = 일
  => 실무에선, 통일성을 위해 대부분 SUNDAY 기준을 사용
  

2.7. DATETIME_DIFF

• DATETIME_DIFF(첫 DATETIME, 두번째 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,
  )
  

  - (첫번째 인자 > 두번째 인자) => + 값 출력 / (첫번째 인자 < 두번째 인자) => - 값 출력
  - month_diff : 39 => 39개월 차이, week_diff : 170 => 170주 차이
  

---

---

연습문제

1. 트레이너가 포켓몬을 포획한 날짜(catch_date)를 기준으로, 2023년 1월에 포획한 포켓몬의 수를 계산해주세요.

-- 쿼리를 작성하는 목표, 확인할 지표 : 포켓몬의 수
-- 쿼리 계산 방법 : COUNT
-- 데이터의 기간 : 2023년 1월 => catch_datetime을 사용 => EXTRACT
-- 사용할 테이블 : trainer_pokemon
-- Join KEY : X
-- 데이터 특징 : 확인 필요 => 테이블 -> 미리보기 및 스키마 확인

 --> catch_date : DATE 타입 
  -->catch_datetime : UTC -> TIMESTAMP 타입
  => 컬럼의 이름은 datetime인데 TIMESTAMP 타입으로 저장된 경우
  => 이 경우 catch_date 또한 UTC 기준인지? KR 기준인지? 에 대한 데이터 검정이 필요
  => 만약 catch_date != DATE(DATETIME(catch_datetime, "Asia/Seoul")) 인 경우가 있다면 
  => catch_date는 DATE로 사용하기 어려운 컬럼!

데이터 검증을 위한 쿼리)  
SELECT
	COUNT(*)
FROM (
	SELECT
		catch_date,
		DATE(datetime(catch_datetime, "Asia/Seoul")) AS catch_datetime_kr_date
FROM basic.trainer_pokemon
)
WHERE
	catch_date != catch_datetime_kr_date

  --DATE(DATETIME(catch_datetime, "Asia/Seoul")) : KR 기준의 DATE
  => catch_date 컬럼은 UTC 기준의 DATE이므로 활용하기 어렵다!

• 회사에서도 이렇게 데이터를 저장하는 부분에서 이슈가 발생하는 경우가 있다
  => 컬럼의 이름만 보고 바로 쿼리를 작성하는 것이 아니라, 이렇게 꼭 데이터를 확인해야 한다
  => 컬럼의 설명 및 정의(Description: 테이블 -> 스키마에서 확인 가능)를 꼭 파악하고 쿼리를 작성해야 한다!

/문제 풀이/
SELECT
	COUNT(DISTINCT id) AS cnt
FROM basic.trainer_pokemon
WHERE
	EXTRACT(YEAR FROM DATETIME(catch_datetime, "Asia/Seoul")) = 2023
	AND EXTRACT(MONTH FROM DATETIME(catch_datetime, "Asia/Seoul")) = 1

COUNT(DISTINCT id)을 쓴 이유 :
문제: 포켓몬의 수를 계산 
=> 1. trainer_pokemon 테이블에선 하나의 row가 한 번의 포획을 의미 
	& 동일한 포켓몬을 잡았어도 별도로 count하는 것을 의도한 문제
=> 2. 출력 값이 정말 unique한지 확인하기 위해 DISTINCT 사용 
(해당 문제의 id는 중복없는 고유한 값(PK)이지만, 만약 JOIN이 들어가 데이터가 증가된다면 
	-> DISTINCT를 사용해야 함) 

2. 배틀이 일어난 시간(battle_datetime)을 기준으로, 오전 6시에서 오후 6시 사이에 일어난 배틀의 수를 계산해주세요.

--쿼리를 작성하는 목표, 확인할 지표 : 오전 6시 ~ 오후6시 배틀의 수
--쿼리 계산 방법 : COUNT
--데이터의 기간 : 일자는 상관 없고, 오전6시~오후시 => battle_datetime 사용 => EXTRACT
--사용할 테이블 : battle
--Join KEY : X
--데이터의 특징 : 확인 필요

--> battle_date : battle_datetime 기반으로 만들어진 DATE로 추정된다
--> battle_datetime : DATETIME, battle_timestamp : TIMESTAMP
--> battle_datetime = DATETIME(battle_timestamp, "Asia/Seoul") 인지 검증하기!

/검증을 위한 쿼리/
SELECT
	COUNTIF(battle_datetime = DATETIME(battle_timestamp, "Asia/Seoul")) AS same_kr
	COUNTIF(battle_datetime = DATETIME(battle_timestamp, "Asia/Seoul")) AS not_same_kr
FROM basic.battle

  								(전체 행 수를 알고 싶다면 => 테이블->세부정도->스토리지 정보 확인)
  =>battle_datetime 은 KR 기준의 DATETIME이므로 그대로 사용하면 된다!

/문제풀이 1/
SELECT
	COUNT(DISTINCT id) AS battle_cnt
FROM basic.battle
WHERE
	EXTRACT(HOUR FROM battle_datetime) >= 6
  	AND EXTRACT(HOUR FROM battle_datetime < 18
----
/문제풀이 2/
SELECT
	COUNT(DISTINCT id) AS battle_cnt
FROM basic.battle
WHERE
	EXTRACT(HOUR FROM battle_datetime) BETWEEN 6 AND 17

• BETWEEN a AND b : a와 b 사이에 있는 것을 반환

2-1. 시간대 별로 배틀의 건수를 계산해주세요.

SELECT
  --hour,
  --COUNT(DISTINCT id) AS battle_cnt
  FROM(
  --SELECT
  ----*,
  ----EXTRACT(HOUR FROM battle_datetime) AS hour
  ---FROM basic.battle
  )
  GROUP BY 
  --hour
  ORDER BY hour

3. 각 트레이너별로 그들이 포켓몬을 포획한 첫 날(catch_date)을 찾고, 그 날짜를 'DD/MM/YYYY' 형식으로 출력해주세요.

-- 쿼리를 작성하는 목표, 확인할 지표 : 포획한 첫 날 + 그 날짜를 특정 형태로 변경
-- 쿼리 계산 방법 : DATE => 문자열 FORMAT_DATETIME
-- 데이터의 기간 : X
-- 사용할 테이블 : trainer_pokemon
-- Join KEY : X
-- 데이터 특징 :  catch_date는 UTC 기준의 DATE. 한국 기준으로 하려면 catch_datetime을kr기준으로 바꿔서 사용해야 한다
--연산 순서상, 포획한 첫 날을 먼저 구한 후 -> 날짜 형식 변경
--'DD/MM/YYYY' 형식으로 바꾸는 법 => 공식 문서 검색 활용

SELECT
	trainer_id,
	FORMAT_DATE("%d/%m/%Y", min_catch_date) AS new_min_catch_date
FROM (
	SELECT
		trainer_id,
		MIN(DATE(catch_datetime, "Asia/Seoul")) AS min_catch_date
	FROM basic.trainer_pokemon
  	GROUP BY
  		trainer_id
  )
ORDER BY trainer_id

4. 배틀이 일어난 날짜(battle_date)를 기준으로, 요일 별로 배틀이 얼마나 자주 일어났는지 계산해주세요.

--쿼리를 작성하는 목표, 확인할 지표 : 요일 별로 배틀이 얼마나 자주 일어났는가? 배틀의 건
--쿼리 계산 방법 : 요일 별 COUNT
--데이터의 기간 : X
--사용할 테이블 : battle
--Join KEY : X
--데이터 특징 : battle_date가 정상적(즉, KR 기준의 DATE 타입)이므로 그대로 사용
  --요일을 어떻게 추출? => EXTRACT, DAYOFWEEK

/문제 풀이 1/
SELECT
	day_of_week,
	COUNT(DISTINCT id) AS battle_cnt
FROM (
	SELECT
		*, 
		EXRACT(DAYOFWEEK FROM battle_date) AS day_of_week
	FROM basic.battle
  )
GROUP BY
	day_of_week
ORDER BY 
	day_of_week

/문제 풀이 2/
SELECT
	EXTRACT(DAYOFWEEK FROM battle_date) AS day_of_week,
	COUNT(DISTINCT id) AS battle_cnt
FROM basic.battle
GROUP BY
	EXTRACT(DAYOFWEEK FROM battle_date)
ORDER BY
	day_of_week

- 해당 데이터에선 서브쿼리를 쓰지 않아도 되지만, 특정 연산에서는 함수가 바로 적용되지 않는 경우가 있다 
  => 그럴 땐 서브 쿼리로 감싸서 사용

5. 트레이너가 포켓몬을 처음으로 포획한 날짜와 마지막으로 포획한 날짜의 간격이 큰 순으로 정렬하는 쿼리를 작성해주세요.

--쿼리를 작성하는 목표, 확인할 지표 : 트레이너의 처음과 마지막의 diff 큰 순으로 정렬
--쿼리 계산 방법 : 처음 포획한 날짜(min)+마지막으로 포획한 날짜(max) -> DATETIME_DIFF -> 차이가 큰 순으로 정렬(ORDER BY)
--데이터의 기간 : X
--사용할 테이블 : trainer_pokemon
--Join KEY : X
--데이터 특징 : catch_date는 UTC 기반으로 만들어진 일자 => catch_datetime을 사용

SELECT
	*,
	DATETIME_DIFF(max_catch_datetime, min_catch_datetime, DAY) AS diff
FROM (
	SELECT
		trainer_id,
		MIN(DATETIME(catch_datetime, "Asia/Seoul")) AS min_catch_datetime,
		MAX(DATETIME(catch_datetime, "Asia/Seoul")) AS max_catch_datetime
	FROM basic.trainer_pokemon
  	GROUP BY
		trainer_id
)
ORDER BY
	diff DESC

• DATETIME 차이가 맞는지 확인하는 방법 : 날짜 하나를 찍고, 네이버 등에서 D-Day 계산기 사용