Как посчитать Fill Rate в SQL

Зачем Fill Rate

Клиент заказал 5 SKU. Доступно 4. Order partially filled = 80% Fill Rate. Если регулярно — клиенты уходят к конкурентам (которые always in stock).

Формула

Order Fill Rate = orders_fulfilled_complete / total_orders × 100%
Unit Fill Rate = units_delivered / units_ordered × 100%
Line Fill Rate = order_lines_delivered_complete / total_order_lines × 100%

Базовый расчёт

Данные: order_lines(order_id, sku, qty_ordered, qty_delivered).

SELECT
    DATE_TRUNC('week', order_date) AS week,
    COUNT(*) AS total_lines,
    COUNT(*) FILTER (WHERE qty_delivered >= qty_ordered) AS lines_complete,
    SUM(qty_ordered) AS total_units,
    SUM(qty_delivered) AS units_delivered,
    SUM(qty_delivered)::NUMERIC * 100 / NULLIF(SUM(qty_ordered), 0) AS unit_fill_rate_pct,
    COUNT(*) FILTER (WHERE qty_delivered >= qty_ordered)::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS line_fill_rate_pct
FROM order_lines
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Line vs Unit Fill Rate

Line fill — % полных lines. Unit fill — % delivered units.

-- Order: 5 lines, 4 complete, 1 partial (50 of 100)
-- Line fill = 4/5 = 80%
-- Unit fill = ((100*4) + 50) / ((100*5)) = 90%

Line stricter. Unit more granular.

Stock-out Rate

SELECT
    sku,
    COUNT(*) AS total_lines,
    COUNT(*) FILTER (WHERE qty_delivered < qty_ordered) AS partial_or_zero,
    COUNT(*) FILTER (WHERE qty_delivered = 0) AS stockouts
FROM order_lines
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY sku
HAVING COUNT(*) FILTER (WHERE qty_delivered < qty_ordered) > 0
ORDER BY stockouts DESC
LIMIT 50;

Top stockout SKUs — re-order priority.

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

Ошибка 1. Cancelled orders. Cancelled — не fill rate denominator. Exclude.

Ошибка 2. Back-orders. Order partially filled today, rest delivered next month. Counts at original date or delivery date?

Ошибка 3. Substitutions. Out of brand A, customer accepted brand B. Filled? Counted but with note.

Ошибка 4. Window choice. Fill rate за неделю vs месяц — разные numbers.

Ошибка 5. Cumulative vs single shipment. Order delivered в 2 shipments — fully filled? Yes если total delivered ≥ ordered.

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

• Как посчитать inventory turnover в SQL
• Как посчитать on-time delivery в SQL
• Как посчитать sell-through rate в SQL
• Как посчитать cart abandonment в SQL

FAQ

Какой Fill Rate ok?

E-com: 95%+. B2B: 98%+. Pharma / Healthcare: 99%+ (critical).

Line vs Unit?

Line — strict (customer perspective). Unit — operational.

Fill Rate падает — что делать?

1. Inventory planning improvement. 2) Better demand forecasting. 3) Supplier diversification. 4) Reorder points review.

Substitutions?

Track separately. Customer-accepted sub = filled. Customer-rejected = stockout.

Fill Rate vs Perfect Order Rate?

Fill Rate — units delivered. Perfect Order = on-time + complete + damage-free + correct invoice.