Как посчитать supplier on-time rate в SQL

Зачем supplier on-time

Если supplier обещает 14 days, а делает 21 — operational planning ломается. On-time rate — core indicator vendor reliability. Низкий rate = безопасный stock buffer (= cash в товаре) или late deliveries (= stockout).

Формула

on_time_rate = orders_delivered_on_time / total_delivered

«On time» обычно — within 1-2 days от promised date. Strict: точно в day.

On-time rate в SQL

SELECT
    supplier_id,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE delivered_at <= promised_at) AS on_time,
    COUNT(*) FILTER (WHERE delivered_at <= promised_at)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS on_time_pct
FROM purchase_orders
WHERE delivered_at IS NOT NULL
  AND delivered_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY supplier_id
HAVING COUNT(*) >= 20
ORDER BY on_time_pct;

Industry standard 90-95%. Меньше 80% — поиск замены.

По поставщикам

SELECT
    s.supplier_name,
    s.category,
    COUNT(*) AS orders,
    AVG(EXTRACT(EPOCH FROM (po.delivered_at - po.promised_at)) / 86400) AS avg_delay_days,
    COUNT(*) FILTER (WHERE po.delivered_at <= po.promised_at)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS on_time_pct
FROM purchase_orders po
JOIN suppliers s USING (supplier_id)
WHERE po.delivered_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY s.supplier_name, s.category
HAVING COUNT(*) >= 10
ORDER BY on_time_pct;

Категория «raw materials» обычно более reliable чем «specialty parts».

Average lateness

Не просто «in time vs late», а на сколько дней:

SELECT
    supplier_id,
    COUNT(*) AS orders,
    AVG(GREATEST(EXTRACT(EPOCH FROM (delivered_at - promised_at)) / 86400, 0)) AS avg_lateness_days,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY
        GREATEST(EXTRACT(EPOCH FROM (delivered_at - promised_at)) / 86400, 0)
    ) AS p95_lateness_days
FROM purchase_orders
WHERE delivered_at IS NOT NULL
  AND delivered_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY supplier_id
HAVING COUNT(*) >= 10
ORDER BY avg_lateness_days DESC;

P95 lateness важнее avg — worst case scenario для planning.

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

Ошибка 1. Считать только delivered. Cancelled / never-delivered orders — тоже supplier issue. Include как «failures».

Ошибка 2. Promised date менялся. Supplier пересогласовал → new promised date. Используйте original promise для on-time.

Ошибка 3. Quantity short. Order delivered on-time, но 80% количества — это не «on-time fulfillment». Track separately.

Ошибка 4. One promised date для всех SKU в order. Multi-SKU order может double-bill complete date. Best — на line-item level.

Ошибка 5. Average lateness без quality. Late delivery с perfect product лучше on-time с defects. Combine on-time + defect rate.

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

• Как посчитать on-time delivery в SQL
• Как посчитать perfect order rate в SQL
• Как посчитать stockout rate в SQL
• Как посчитать fill rate в SQL

FAQ

Какой on-time норма?

90-95% для reliable suppliers. Свыше 95% — top-tier. Меньше 80% — replace.

Tolerance window?

±1 day стандарт. ±3 days — relaxed. Same-day — strict.

Penalty для chronic late?

В contract — late delivery fees. Часто 1-5% от order value за день.

Тренд upgrade или downgrade?

Падение on-time на 5pp за 3 месяца — flag. Investigate.

Multi-supplier strategy?

Yes — критичные SKU 2-3 suppliers. Спасает от single point of failure.