Как посчитать video QoE в SQL

Зачем QoE композит

Startup time + rebuffer + bitrate — не одна метрика, а три. Composite QoE score объединяет в одну. Используется в alerts (drop in QoE = page on-call), в product OKR (target QoE score > 80), в A/B (player change → QoE delta).

Компоненты

Стандартный composite:

• startup time (lower = better) → 25%
• rebuffer ratio (lower = better) → 35%
• avg bitrate (higher = better) → 20%
• exit before play / error rate → 20%

Веса arbitrary, calibrate под бизнес.

QoE score в SQL

WITH normalized AS (
    SELECT
        view_id,
        -- normalize 0-100 для каждой метрики
        GREATEST(0, LEAST(100, 100 - (startup_ms - 500) / 50.0)) AS startup_score,
        GREATEST(0, LEAST(100, 100 - (rebuffer_ratio * 200))) AS rebuffer_score,
        GREATEST(0, LEAST(100, avg_bitrate_kbps / 50.0)) AS bitrate_score,
        CASE WHEN exit_before_play OR has_fatal_error THEN 0 ELSE 100 END AS reliability_score
    FROM video_view_events
    WHERE view_started_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT
    AVG(startup_score) * 0.25
    + AVG(rebuffer_score) * 0.35
    + AVG(bitrate_score) * 0.20
    + AVG(reliability_score) * 0.20 AS qoe_score
FROM normalized;

QoE 90+ — premium experience. 70-90 — норма. < 70 — investigate.

Сегментация

SELECT
    region,
    platform,
    AVG(startup_score) * 0.25
    + AVG(rebuffer_score) * 0.35
    + AVG(bitrate_score) * 0.20
    + AVG(reliability_score) * 0.20 AS qoe_score,
    COUNT(*) AS views
FROM qoe_normalized
GROUP BY region, platform
HAVING COUNT(*) >= 1000
ORDER BY qoe_score;

Регион с low QoE — приоритет для CDN expansion / optimization.

QoE vs engagement

SELECT
    CASE
        WHEN qoe_score >= 90 THEN '90+'
        WHEN qoe_score >= 75 THEN '75-90'
        WHEN qoe_score >= 60 THEN '60-75'
        ELSE '<60'
    END AS qoe_bucket,
    AVG(seconds_watched) AS avg_watch_time,
    SUM(CASE WHEN completed THEN 1 ELSE 0 END)::NUMERIC * 100 / COUNT(*) AS completion_pct
FROM video_views_with_qoe
GROUP BY 1
ORDER BY MIN(qoe_score);

Direct correlation: viewers с QoE 90+ имеют 50%+ больше watch time, чем <60.

Частые ошибки

Ошибка 1. Веса наугад. Calibrate weights через regression: какие компоненты больше всего predict abandonment.

Ошибка 2. Бинарная reliability. Exit-before-play и fatal error — different severity. Use partial scores.

Ошибка 3. Aggregate без segmentation. Average QoE 85 при региональной variance 60-95 = маскирует problem regions.

Ошибка 4. Не сравнивать с baseline. QoE 80 — много или мало? Compare к prior week / prior month.

Ошибка 5. Bitrate как absolute. 4K bitrate vs 720p — разные expectations. Normalize on resolution.

Связанные темы

• Как посчитать video startup time в SQL
• Как посчитать rebuffer rate в SQL
• Как посчитать video completion rate в SQL
• Как посчитать engagement score в SQL

FAQ

Какой QoE хороший?

Premium streaming: 85+. Free / live: 75+. Mobile: 70+ realistic.

Composite vs separate?

Composite — для top-line metric. Components — для root cause.

Веса как калибровать?

Linear regression: components → engagement (watch time). Output = optimal weights.

Real-time alerting?

QoE drop > 10 points за час — alert. CDN или backend issue.

Per-video QoE?

Yes — некоторые видео имеют consistently lower QoE (длинные, 4K).