#1 キリ番祝ひ

Open

opened 1 month ago by みてるぞ · 5 comments

日次でぼざクリ DB にクエリ飛ばす．

今日と前日のデータについて，キリ番祝ひの対象となる各再生数について，該当の値とその次に大きい値以内で再生数を絞り込み，今日に含まれるが前日に含まれなぃ動画をリスト化し，それらの情報（タグ，いぃ感じに抽出したコメント含む）をニジカに食はせる．

まだ未定だが，以下のやぅな URI になりさぅ：
https://nizika.monster/nico/videos/?date=2024-10-09&minViews=194&maxViews=244
（キリ番候補が [100, 194, 245, 300, ...] と並んでゐて，194 再生を祝ひたぃ場合）

select * from (select a.id, a.video_id, a.views_count from video_histories as a inner join (SELECT video_id, MAX(fetched_at) as latest FROM `video_histories` GROUP BY video_id) as b on a.video_id = b.video_id and a.fetched_at = b.latest WHERE a.views_count >= 2000) as unko where video_id not in (
select a.video_id from video_histories as a inner join (SELECT video_id, MAX(fetched_at) as yesterday FROM `video_histories` WHERE fetched_at < CURRENT_DATE GROUP BY video_id) as b on a.video_id = b.video_id and a.fetched_at = b.yesterday WHERE a.views_count >= 2000);

みてるぞ referenced this issue from a commit 3 weeks ago

#1 もろもろ基盤

みてるぞ referenced this issue from a commit 3 weeks ago