[MySQL]Daily view 테이블 만들기

문제상황

매일 업데이트 되는 유튜브 영상 데이터를 이용해 한 영상당 일일조회수를 추적하는 테이블을 만든다.

현재 유튜브 영상의 데이터를 추적하기 위한 video_history라는 테이블이 존재하는데 주요 컬럼은 아래와 같다.

• video_id : 유튜브에서 부여한 영상마다의 id
• video_views : 그 날 수집한 영상의 총 조회수
• inserted_at : DB에 데이터가 들어온 timestmap

이것들을 이용해서 daily_view 테이블을 채워야한다. 주요 컬럼은 아래와 같다.

• channel_id : 유튜브에서 부여한 채널마다의 id
• video_id
• views : 일일조회수
• inserted_at

해결 방법

channel_id는 video 테이블에, video_id는 video_history 테이블과 video 테이블에, views는 video_history 테이블에서 두 날짜를 비교해 오늘날짜에서 어제날짜의 video_views를 뺸 값을 가져오면 된다.

우선 간단하게 쿼리를 통해 4월10일과 4월 11일 두 시점에 수집한 영상 총 조회수를 조회해봤다.

WITH temp0410 AS (
	select video_id, video_views, date(inserted_at) as date
    from video_history
	where date(inserted_at) = '2023-04-10'
    ),
    temp0411 AS (
    select video_id, video_views, date(inserted_at) as date
    from video_history
	where date(inserted_at) = '2023-04-11'
    )

select * from temp0410 t0
join temp0411 t1
on t0.video_id = t1.video_id;

결과를 보니, with절만 잘 이용하면 되겠다 싶어서 그대로 INSERT INTO를 쓰려했더니 안 되더라. 알고보니 MySQL은 with절을 바로 insert문에 쓸 수 없어, 서브쿼리를 이용했다.

INSERT INTO daily_views (channel_id, video_id, inserted_at, views)
SELECT t1.video_id, v.channel_id, t1.date AS inserted_at, (t1.video_views - t0.video_views) AS views
FROM (
  SELECT video_id, video_views, DATE(inserted_at) AS date
  FROM video_history
  WHERE DATE(crawl_update_at) = '2023-04-11'
) t1
JOIN (
  SELECT video_id, video_views, DATE(inserted_at) AS date
  FROM video_history
  WHERE DATE(crawl_update_at) = '2023-04-10'
) t0 ON t1.video_id = t0.video_id
JOIN video v ON t1.video_id = v.video_id
GROUP BY t0.video_id;

t1과 t0이라는 임시 테이블을 만들어주는 서브쿼리를 짜주었다. 그 뒤에 t1, t0, video 테이블을 video_id라는 공통의 컬럼으로 inner join 해주고 daily_views 테이블에 넣어주었다.

그 결과

쨔잔 위와 같이 daily view 테이블을 채울 수 있었다.

남은 문제

오잉? 근데 views에 -1이라는 음수값이 보이는 것을 확인 할 수 있었다. 그래서 혹시 몰라 더 다른 데이터를 확인해보니

select * from daily_views
where date(crawl_update_at) = '2023-04-11'
order by views;

이렇게나 많은 데이터가 음수값이더라. 그래서 데이터 수집을 잘못했나 생각해봤지만 역시 크게 잘못 된 코드는 없어서 구글링 해보니 유튜브측에서 조회수를 검증을 해서 유효하지 않은 조회 기록은 삭제를 한다고 했다. 그래서 전날 수집할 때에는 일시적으로 조회수로 카운트 되지만 하루 지나 유튜브의 검증을 끝내고 조회수로 카운트 하지 않기로 한다면 조회수가 감소하는 것이다.

우선 내 잘못은 아닌듯 하니 여기서 마무리 지었다🎃