[SQL] #5 시간 데이터 문제풀이

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

틀린방식
SELECT
    COUNT(id) AS cnt,
FROM `basic.trainer_poketmon`
WHERE
    EXTRACT(year from catch_date) AND EXTRACT(month from catch_date) = 1

맞는방식
SELECT
    COUNT(id) AS cnt,
FROM `basic.trainer_poketmon`
where
    EXTRACT(year from DATETIME(catch_datetime, 'Asia/Seoul')) = 2023 AND
    EXTRACT(month from DATETIME(catch_datetime, 'Asia/Seoul')) = 1

Q. 오전 6시부터 오후 6시까지 배틀을 한 횟수는 ?

SELECT
    COUNT(DISTINCT id),
FROM `basic.battle`
WHERE
    EXTRACT(HOUR FROM battle_datetime) >= 6 AND
    EXTRACT(HOUR FROM battle_datetime) <= 18
	------------------------------------------------
    EXTRACT(HOUR FROM battle_datetime) BETWEEN 6 and 
    # BETWEEN a and b => a와 b  사이에 있는 것을 반환

Q. 시간대별로 배틀을 한 횟수는 ?

SELECT
    hour,
    COUNT(id) AS cnt,
FROM(
SELECT
    *,
    EXTRACT(HOUR FROM battle_datetime) AS hour
FROM `basic.battle`
)
GROUP BY
    hour
ORDER BY
    hour

Q. 트레이너별로 첫 번째 포켓몬을 잡은 날짜를 달/일/년도 순으로 출력해주세요

SELECT
    trainer_id,
    FORMAT_DATE('%d/%m/%Y',min_catchdate_time)

FROM(
    SELECT
    trainer_id,
    MIN(DATE(catch_datetime,"Asia/Seoul")) AS min_catchdate_time
    -- trainer_id로 그룹화를 하면, 아이디당 잡은 모든 날짜가 다 출력이 된다. 거기서 MIN을 쓰면 가장 오래된 날짜가 출력
    FROM `basic.trainer_poketmon`
    GROUP BY
        trainer_id
)
ORDER BY
    trainer_id
-- ORDER BY는 SELECT문 가장 바깥에서 한번만 실행하는 게 좋다 이유는, 연산처리가 길기 때문에 

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

SELECT
    trainer_id,
    DATETIME_DIFF(max_day,leatest_day,DAY) AS diff_day
FROM(
SELECT
    DATETIME(MAX(catch_datetime),'Asia/Seoul') AS max_day,
  = MAX(DATETIME(catch_datetime,'Asia/Seoul')) AS max_day
    DATETIME(MIN(catch_datetime),'Asia/Seoul') AS leatest_day,
    trainer_id,
FROM `basic.trainer_poketmon`
GROUP BY
    trainer_id
)
ORDER BY 
    diff_day desc

---

풀면서 느낀 것

1. EXTRACT(month from catch_date)와 COUNT(id)를 같이 사용할 수가 없다.
   why ?
   집계함수와 어떤 컬럼이든, 표현식을 함께 사용할 때는 GROUP BY 절이 필요하다
   GROUP BY에 넣지 않으면 에러가 뜬다.
   그래서 집계함수 없이 EXTRACT(month from catch_date)를 쓰면 에러가 안 뜬다.
   문제는 집계함수였네..

2. 데이터가 UTC 기준으로 설정 돼 있을 수 있다. 그럼 반드시 타임존을 우리나라로 맞춘 값으로 변환하는 작업이 필요하다.
   위 풀이의 틀린방식은 UTC 기준으로 된 데이터에서 바로 데이터를 뽑아왔다.

3. 요청한 사람 또는 문제를 그대로 볼 경우에 틀릴 수가 있다. 회사에서도 비슷한 상황일 수 있다.
   컬럼을 꼭 파악하고(정의 확인) 쿼리를 작성하자 !

4. WHERE절은 boolean 타입으로 값을 도출할 수 없다.

5. COUNTIF는 조건에 해당하는 애들을 카운트 세준다.

6. 데이터를 출력하는 것 뿐만 아니라, 이 데이터가 정확한 데이터인지 검증하는 절차도 중요함