Как посчитать rebuffer rate в SQL

Зачем rebuffer rate

Rebuffer (buffering during playback) — главный реальный fail QoE (quality of experience) в video. Юзер видит «loading…» в середине video — frustration → abandonment. Industry benchmark: < 0.5% buffer ratio для premium services (Netflix), < 2% для consumer apps.

Формула

rebuffer_rate = views_with_rebuffer / total_views
buffer_ratio = total_buffer_seconds / total_watch_seconds

Два разных metrics. Rate — частота. Ratio — насколько долго.

Rebuffer в SQL

SELECT
    DATE_TRUNC('day', view_started_at)::DATE AS day,
    COUNT(*) AS views,
    COUNT(*) FILTER (WHERE rebuffer_count > 0) AS views_with_rebuffer,
    COUNT(*) FILTER (WHERE rebuffer_count > 0)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS rebuffer_rate_pct,
    AVG(rebuffer_count) FILTER (WHERE rebuffer_count > 0) AS avg_rebuffers_per_view
FROM video_view_events
WHERE view_started_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE_TRUNC('day', view_started_at)
ORDER BY day;

Тренд — главный сигнал. Спайк = CDN issue или network spike.

По региону и ISP

SELECT
    region,
    isp,
    COUNT(*) AS views,
    AVG(rebuffer_count) AS avg_rebuffers,
    SUM(total_buffer_seconds) / NULLIF(SUM(total_watch_seconds), 0) * 100 AS buffer_ratio_pct
FROM video_view_events
WHERE view_started_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY region, isp
HAVING COUNT(*) >= 100
ORDER BY buffer_ratio_pct DESC
LIMIT 20;

Если один ISP в одном регионе показывает rebuffer 10% при average 1% — CDN peering issue.

Buffer ratio

SELECT
    SUM(total_buffer_seconds) AS total_buffer,
    SUM(total_watch_seconds) AS total_watch,
    SUM(total_buffer_seconds)::NUMERIC * 100
    / NULLIF(SUM(total_watch_seconds), 0) AS buffer_ratio_pct
FROM video_view_events
WHERE view_started_at >= CURRENT_DATE - INTERVAL '24 hours';

Buffer ratio 0.5% означает: 0.5% времени юзеры смотрят на «buffering».

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

Ошибка 1. Initial buffering vs rebuffer. Startup buffer (загрузка перед playback) — отдельная metric. Rebuffer — во время playback.

Ошибка 2. Не учитывать playback events. Если tracker не send «buffering_started» / «buffering_ended» — count incorrect.

Ошибка 3. Single CDN snapshot. Different times (peak vs off-peak) — разные rebuffer rates. Time-of-day matters.

Ошибка 4. Считать на mobile. Mobile networks variable. iOS Wi-Fi vs Android cellular — different patterns.

Ошибка 5. Buffer ratio > 100%. Bug: buffer seconds считаются дольше playback. Sanity check.

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

• Как посчитать video startup time в SQL
• Как посчитать video completion rate в SQL
• Как посчитать crash rate в SQL
• Как посчитать API latency в SQL

FAQ

Какой rebuffer rate норма?

Premium streaming (Netflix): < 0.5%. Free OTT: 1-3%. Live sports — самое сложное.

Mobile vs Wi-Fi?

Mobile rebuffer 2-3× выше из-за variable bandwidth.

Adaptive bitrate спасает?

Yes — ABR сокращает rebuffer на 50-70% versus fixed bitrate.

Peak hours?

Evening prime time → 2× rebuffer. Plan capacity и CDN scale.

CDN provider impact?

Yes — Akamai vs Cloudflare vs custom CDN дают разные rebuffer rates по regions.