TL;DR
お気に入り（永久保持）と通常レコード（30日削除）で「お気に入り率」を出すと、分母が勝手に縮んで率が膨張する。保持期間が違うデータで率を出すときは、同一ウィンドウで切れるか確認し、切れないなら件数に切り替える。
起きたこと
Electron + SQLite の YouTube 管理アプリで、チャンネル別の「お気に入り率」を実装した。
お気に入り率 = お気に入り数 ÷ 全動画数
結果、ほぼすべてのチャンネルでお気に入り率が 80〜100% になった。
原因
データ保持ポリシーの違いが分母を歪めていた。
| 種類 | 保持期間 |
|---|---|
| 通常の動画レコード | 30 日で自動削除 |
| お気に入り動画 | 永久保持 |
分子（お気に入り）は蓄積され続けるが、分母（全動画）は 30 日で入れ替わる。時間が経つほど率は 100% に近づく。
1月: 5 / 30 = 17%
2月: 10 / 32 = 31% ← 1月の非お気に入り25件は削除済み
3月: 15 / 29 = 52% ← 2月も削除済み
修正
率ではなく件数にした。知りたいのは「どのチャンネルの配信をよく保存しているか」であり、全配信に対する割合ではなかった。
SELECT
v.channel_id,
COALESCE(c.title, v.channel_title) AS channel_title,
COALESCE(c.is_pinned, 0) AS is_pinned,
COUNT(*) AS favorite_count,
SUM(CASE WHEN v.viewed_at IS NOT NULL THEN 1 ELSE 0 END) AS viewed_count
FROM videos v
LEFT JOIN channels c ON c.id = v.channel_id
WHERE v.is_favorite = 1
AND v.channel_id IS NOT NULL
AND v.channel_id != ''
GROUP BY v.channel_id
ORDER BY
favorite_count DESC,
viewed_count DESC,
channel_title COLLATE NOCASE ASC
期間フィルタを入れていないのは、お気に入りが永久保持だから。同じアプリの「視聴済み率」は分母と分子が同じ 30 日ウィンドウで切れるので率が成立する。
確認ポイント
保持期間が異なるデータで率を出す前に確認すること。
- 分母と分子の保持期間は一致するか
- 同じ期間ウィンドウで切れるか
- そもそも率が必要か（件数で十分ではないか）
参考
- YouTom — YouTube 配信スケジュール管理 Electron アプリ
- 関連 Zenn 記事: 保持期間が違うデータから「お気に入り率」を出すのをやめた