[강의 수강 10일차] 시간 데이터 연습 문제 풀기

시간 데이터의 중요성에 대해서 크게 깨닫지 못했었는 데

강의를 듣고 + 시계열 분석이 무엇보다 중요하다는 것을 직접 깨닫게 되면서

시간 데이터 타입의 이해 파트가 더더욱 와닿았고 필요하다고 느꼈다.

 

문제를 하나씩 풀어보며 시간 데이터에 대해서 추가적으로 이해해보고자 한다. 

 

1. 특정 월에 포획한 포켓몬 수 포획한 날짜 기준 계산하기 

-- 1. 트레이너가 포켓몬을 포획한 날짜(catch_date)를 기준으로 2023년 1월에 포획한 포켓몬 수를 계산해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 포켓몬의 수
# 쿼리 계산 방법 : COUNT
# 데이터의 기간 : 2023년 1월
# 사용할 테이블 : trainer_pokemon
# Join KEY : X
# 데이터 특징 : 직접 데이터 타입 확인이 필요하다.
 -- catch_date : DATE 타입
 -- catch_datetime : UTC가 찍혀 있다? TIMESTAMP 타입이다. => 컬럼명은 datetime인 데 TIMESTAMP 타입으로 저장되어 있다.
 -- 회사에서도 데이터가 잘못 저장된 경우 이런 경우가 있을 수 있음 
 -- 컬럼 이름만 믿고 바로 쿼리 작성하는 것이 아닌 데이터 검증 과정이 필수인 이유
 -- catch_date => KR 인지, UTC 기준인 지 
 -- catch_date_kr 또는 catch_date 컬럼의 Description 에 작성되어있는 지 확인! 
 -- catch_date가 UTC 기준인지? KR 기준인 지 확인해봐야 한다. 
 -- catch_date 컬럼 catch_datetime 컬럼을 비교 => DATE(DATETIME(catch_datetime, "Asia/Seoul"))
 -- catch_date != DATE(DATETIME(catch_datetime, "Asia/Seoul")) 값이 있다면 catch_date 사용이 어려울 수 있음
 -- 이러한 경우, 데이터를 저장하는 부분에서 이슈가 발생되었을 수 있음 
 
 -- #0. 데이터 검증을 위한 쿼리
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 -- != 같지 않다는 뜻, 결과 : 같지 않은 경우 141건, 같은 경우 238건 
    -- 컬럼의 설명을 꼭 확인하고 SQL 작성 필요!

-- #1. 데이터 추출
SELECT
	COUNT(DISTINCT id) AS cnt
FROM basic.trainer_pokemon
WHERE
	EXTRACT(YEAR FROM DATETIME(catch_datetime, "Asia/Seoul")) = 2023 # catch_datetime은 TIMESTAMP로 저장되어있으므로 DATETIME으로 변경해야 한다. 
    AND EXTRACT(MONTH FROM DATETIME(catch_datetime, "Asia/Seoul")) = 1

 

✏️ 해당 문제의 POINT는 요청한 사람 또는 문제를 그대로만 볼 경우 틀릴 수 있다.

따라서 반드시 컬럼을 파악 / 정의 확인 후 쿼리를 작성하는 습관을 들이자! 

 

2. 특정 시간에 배틀 시간 기준 배틀의 수 계산하기 

-- 2. 배틀이 일어난 시간(battle_datetime)을 기준으로, 오전 6시에서 오후 6시 사이에 일어난 배틀의 수를 계산해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 오전 6시~오후 6시 배틀의 수
# 쿼리 계산 방법 : COUNT
# 데이터의 기간 : 일자는 상관없고 오전 6시~오후 6시
# 사용할 테이블 : battle
# Join KEY : X
# 데이터 특징
 -- battle_date : battle_datetime 기반으로 만들어진 DATE
 -- battle_datetime : DATETIME
 -- battle_timestamp : TIMESTAMP
 -- battle_datetime이랑 DATETIME(battle_timestamp, "Asia/Seoul") 같은 지 검증!

-- 2-0. battle_datetime, battle_timestamp 검증
-- countif를 통해 스토리지 정보의 행 수와 타입 같은 것 여부 수 더한 것이 같으면 데이터 정합성 맞는 것! 
SELECT
	--id, 
    --battle_datetime,
    --DATETIME(battle_timestamp, "Asia/Seoul) AS battle_timestamp_kr,
    COUNTIF(battle_datetime = DATETIME(battle_timestamp, "Asia/Seoul)) battle_datetime_same_battle_timestamp_kr,
    COUNTIF(battle_datetime != DATETIME(battle_timestamp, "Asia/Seoul)) battle_datetime_not_same_battle_timestamp_kr,
FROM basic.battle -- 여기서 countif를 통해 데이터 정합성을 검증할 수 있다. 

-- 2-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
    -- between A and B A와 B 사이 있을 경우 활용 
    EXTRACT(HOUR FROM battle_datetime) between 6 and 18

-- 2-2. 시간대별로 몇 건이 있는가?
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

 

✅COUNTIF 함수를 통해서 스토리지에 있는 행값과의 비교로 정합성을 확인할 수 있다. 

여기서 잘못된 정보가 많다면 데이터에 노이즈가 있는 케이스로 분석으로 활용하기 어렵다.

 

✏️ 데이터 추출을 하기 전에 데이터를 이해하고 확인하는 과정은 필수이다. 

➡️ 물론, 자주 쓰는 테이블 기준으로 데이터를 이해해두면 이런 과정을 거치지 않아도 되니 편할 것! 아카이빙해두기 ⭐️

 

3. 트레이너별 포획한 첫날의 날짜 형식 변경하기

-- 3. 각 트레이너별로 그들이 포켓몬을 포획한 첫 날(catch_date)을 찾고 그 날짜를 'DD/MM/YYYY' 형식으로 출력해주세요
-- 2024-01-01 > 01/01/2024
# 쿼리를 작성하는 목표, 확인할 지표 : 날짜를 특정 형태로 변경! + 포획한 첫 날
# 쿼리 계산 방법 : DATE => 문자열, FORMAT_DATETIME + MIN (*처음으로 포획한 날 계산) 
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon
# Join KEY : X
# 데이터 특징 : catch_date는 UTC 기준 데이터, 한국 기준으로 하려면 cate_datetime을 사용해야 함

SELECT
	trainer_id,
    FORMAT_DATETIME("%d/%m/%Y", min_catch_datetime) AS new_min_catch_date --형식 변경, FORMAT_DATE도 가능 
FROM(
    SELECT
    --포획한 첫날 + 날짜 변경
        trainer_id,
        MIN(DATE(cate_datetime, "Asia/Seoul")) AS min_catch_date
    FROM basic.trainer_pokemon
    GROUP BY -- 이렇게 하면 행값 - id 값 비교하면서 데이터 맞는 지 확인 가능
        trainer_id
)
ORDER BY
        trainer_id
--ORDER BY : SELECT 제일 바깥에서 1번만 하면 됨
--ORDER BY => 모든 ROW를 확인해서 재정렬 > 연산 시간 많이 소요되어 처리 시간이 오래 걸림
-- ORDER BY 위치는 SELECT의 가장 바깥에서 실행하는 게 효율적

 

✏️ 이건 제품 첫구매를 구할 때 "MIN" 함수를 사용하면 될 것 같고 날짜가 필요한 데 형식 통일할 때 활용하면 좋을 것 같다.

 

4. 배틀 발생 기준 요일별 배틀 자주 일어났는 지 계산하기

--4. 배틀이 일어난 날짜(battle_date)를 기준으로 요일별로 배틀이 얼마나 자주 일어났는 지 계산해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 요일별로 배틀이 얼마나 자주 일어났는 가? 배틀의 건수
# 쿼리 계산 방법 : 요일별로 COUNT
# 데이터 기간 : X
# 사용할 테이블 : battle
# Join KEY : X
# 데이터 특징 : battle_date가 정상적! 정합성 있음
-- 요일 어떻게 추출할 것인가?
-- EXTRACT

SELECT
	day_of_week,
    COUNT(DISTINCT id) AS battle_cnt
FROM (
	SELECT
    	*,
    	EXTRACT(DAYOFWEEK FROM battle) AS day_of_week --bigquery extract로 구글링해서 알아볼 수 있음 
    FROM basic.battle
)
GROUP BY
	day_of_week
ORDER BY
	day_of_week

 

5. 트레이너가 포켓몬 처음/마지막 포획한 날짜 간격이 큰 순 정렬하기

--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
    -- 데이터 검증 : 쿼리 결과 보고 날짜 하나 행값 확인해 네이버에서 d-day 계산기 통해 내가 예상한 답과 같은 지 확인하기 
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

 

✏️ 여기서 데이터의 정합성을 검증하는 절차를 반드시 초반 단에 할 수 있도록 하기 

만약 데이터에 문제가 있다면 분석 결과 자체를 신뢰할 수 없기에 꼭 이 과정을 거치는 것을 습관화해야 한다. 

 

✏️ 이번 강의와 학습을 통해 느낀점과 배운점 
✅ 날짜 형식에 대해서 왜 그렇게 봐야 하는 지를 먼저 고민해보자.
✅ 추출하는 과정에서 "데이터 정합성"을 검증하는 과정을 반드시 거치기, 좋은 분석을 하기 위한 첫걸음이다. 

 

어쨌든 DATE 타입도 많이 접해봐야 알 것 같고 현재는 국내만 위주로 분석을 진행하지만

추후 해외까지 확장된다고 생각하면 개념을 잘 숙지해두고 원리를 이해하는 게 필요할 것 같다는 생각을 했다.