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);
```sql
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);
```
日次でぼざクリ DB にクエリ飛ばす.
今日と前日のデータについて,キリ番祝ひの対象となる各再生数について,該当の値とその次に大きい値以内で再生数を絞り込み,今日に含まれるが前日に含まれなぃ動画をリスト化し,それらの情報(タグ,いぃ感じに抽出したコメント含む)をニジカに食はせる.
まだ未定だが,以下のやぅな URI になりさぅ:
https://nizika.monster/nico/videos/?date=2024-10-09&minViews=194&maxViews=244
(キリ番候補が [100, 194, 245, 300, ...] と並んでゐて,194 再生を祝ひたぃ場合)
型定義うんぬんはこの課題とは別っすね.
クローズします.
コメントとかの情報も食はせなきゃだからまだ閉ぢちゃだめだった.
前日で 194 再生に達してゐなかったのが 245 再生達成すると両方で祝ってしまふため,複数ある場合は最高再生数のを採択するやぅに変更する.
検索妨碍対策もしないと.