SQL実践：動画カテゴリ別の平均再生進捗率を算出する方法

動画プラットフォームのデータ分析において、ユーザーのエンゲージメントを測る重要な指標の一つに「再生進捗率」があります。本記事では、カテゴリ別の平均再生進捗率を算出し、特定の閾値を超えるカテゴリを抽出するSQLクエリの構築方法を解説します。

データ構造の理解

分析対象となるテーブルは以下の2つです。

視聴ログテーブル（viewing_logs）

• user_id：ユーザー識別子
• content_id：動画識別子
• view_start：再生開始時刻
• view_end：再生終了時刻
• follow_status：フォロー状態
• like_flag：いいね有無
• share_flag：シェア有無
• comment_id：コメントID

動画情報テーブル（video_master）

• content_id：動画識別子
• creator_id：クリエイターID
• category：カテゴリタグ
• total_duration：動画の長さ（秒）
• publish_date：公開日時

要件の整理

求められる出力は以下の通りです：

1. 各カテゴリの平均再生進捗率を算出
2. 平均再生進捗率が60%を超えるカテゴリのみ抽出
3. 進捗率は小数点第2位まで表示
4. 平均再生進捗率の降順でソート

進捗率の定義：

進捗率 = 実視聴時間 ÷ 動画全体の長さ × 100%

注意点： 実視聴時間が動画全体の長さを超える場合は、進捗率を100%として扱います。

クエリ構築のアプローチ

段階的にクエリを構築していきます。まずは基本構造を定義し、その後で詳細な計算ロジックを実装します。

基本フレームワーク：

SELECT
    category,
    avg_play_rate AS average_progress
FROM viewing_logs vlog
JOIN video_master vmst ON vlog.content_id = vmst.content_id
GROUP BY category
HAVING avg_play_rate > 60
ORDER BY avg_play_rate DESC;

詳細ロジックの実装

進捗率計算には以下の要素が必要です：

実視聴時間の算出：

TIMESTAMPDIFF(SECOND, view_start, view_end)

進捗率の計算と制御： 視聴時間が動画長を超えるケースを考慮し、CASE式で条件分岐させます。

CASE
    WHEN TIMESTAMPDIFF(SECOND, view_start, view_end) >= total_duration
    THEN 1.0
    ELSE TIMESTAMPDIFF(SECOND, view_start, view_end) / total_duration
END

平均値の算出とパーセント形式への変換：

ROUND(
    AVG(
        CASE
            WHEN TIMESTAMPDIFF(SECOND, view_start, view_end) >= total_duration
            THEN 100.0
            ELSE (TIMESTAMPDIFF(SECOND, view_start, view_end) / total_duration) * 100
        END
    ),
    2
)

完成したクエリ

SELECT
    vmst.category AS tag,
    CONCAT(
        ROUND(
            AVG(
                LEAST(
                    TIMESTAMPDIFF(SECOND, vlog.view_start, vlog.view_end) / vmst.total_duration,
                    1.0
                ) * 100
            ),
            2
        ),
        '%'
    ) AS avg_play_progress
FROM viewing_logs vlog
INNER JOIN video_master vmst
    ON vlog.content_id = vmst.content_id
GROUP BY vmst.category
HAVING AVG(
        LEAST(
            TIMESTAMPDIFF(SECOND, vlog.view_start, vlog.view_end) / vmst.total_duration,
            1.0
        ) * 100
    ) > 60
ORDER BY avg_play_progress DESC;

ポイント解説

LEAST関数の活用： IF文やCASE式の代わりにLEAST関数を使用することで、進捗率の上限を1.0（100%）に制限しています。複数の値のうち最小値を返すこの関数は、条件分岐を簡潔に記述できる便利な手段です。

HAVING句の使用： 集計関数の結果に対する条件絞り込みにはWHEREではなくHAVINGを使用します。ここでは平均進捗率が60%を超えるカテゴリのみを抽出します。

CONCATによる書式設定： 計算結果に「%」記号を付与するため、CONCAT関数で文字列連結を行っています。なお、HAVING句では数値比較を行う必要があるため、CONCAT適用前の数値で判定しています。

代替実装例

CASE式を使用したより明示的な記述例：

SELECT
    category_name,
    CONCAT(progress_rate, '%') AS avg_play_progress
FROM (
    SELECT
        vm.category_name,
        ROUND(
            AVG(
                CASE
                    WHEN TIMESTAMPDIFF(SECOND, vl.start_datetime, vl.end_datetime) > vm.length_seconds
                    THEN 100.00
                    ELSE (TIMESTAMPDIFF(SECOND, vl.start_datetime, vl.end_datetime) * 100.0 / vm.length_seconds)
                END
            ),
            2
        ) AS progress_rate
    FROM user_view_log vl
    JOIN video_info vm ON vl.movie_id = vm.movie_id
    GROUP BY vm.category_name
) AS calculated_data
WHERE progress_rate > 60
ORDER BY progress_rate DESC;

サブクエリを使用することで、計算ロジックと表示処理を分離し、可読性を向上させています。