Как посчитать warehouse utilization в SQL

Зачем utilization

Если warehouse заполнен на 60% — есть capacity. 95% — bottleneck (медленная picking, потеря товара). 100%+ — receiving blocked. Utilization — основная operational metric. Обычно target — 75-85% (buffer для seasonal swing).

Формула

utilization = occupied_volume / total_capacity_volume

Аналогично: pallet positions, по SKU count, по weight.

Utilization в SQL

WITH occupied AS (
    SELECT
        warehouse_id,
        SUM(quantity_on_hand * unit_volume_cbm) AS occupied_cbm
    FROM inventory_current
    GROUP BY warehouse_id
),
capacity AS (
    SELECT warehouse_id, total_capacity_cbm
    FROM warehouses
)
SELECT
    c.warehouse_id,
    o.occupied_cbm,
    c.total_capacity_cbm,
    o.occupied_cbm * 100.0 / NULLIF(c.total_capacity_cbm, 0) AS utilization_pct,
    CASE
        WHEN o.occupied_cbm / c.total_capacity_cbm > 0.95 THEN 'critical'
        WHEN o.occupied_cbm / c.total_capacity_cbm > 0.85 THEN 'high'
        WHEN o.occupied_cbm / c.total_capacity_cbm > 0.70 THEN 'optimal'
        ELSE 'underutilized'
    END AS verdict
FROM capacity c
JOIN occupied o USING (warehouse_id)
ORDER BY utilization_pct DESC;

critical — действия немедленно (overflow, new warehouse).

По зонам

SELECT
    warehouse_id,
    zone,
    SUM(quantity_on_hand * unit_volume_cbm) AS occupied,
    MAX(zone_capacity_cbm) AS zone_capacity,
    SUM(quantity_on_hand * unit_volume_cbm) * 100.0
    / NULLIF(MAX(zone_capacity_cbm), 0) AS zone_utilization_pct
FROM inventory_with_zone
GROUP BY warehouse_id, zone
ORDER BY warehouse_id, zone_utilization_pct DESC;

Bulk zone vs picking zone vs returns — разные capacity, разные targets.

Тренд во времени

SELECT
    DATE_TRUNC('week', snapshot_date)::DATE AS week,
    warehouse_id,
    AVG(occupied_cbm * 100.0 / capacity_cbm) AS avg_utilization_pct
FROM warehouse_history
WHERE snapshot_date >= CURRENT_DATE - INTERVAL '26 weeks'
GROUP BY DATE_TRUNC('week', snapshot_date), warehouse_id
ORDER BY week, warehouse_id;

Растущий тренд → planning new capacity или liquidation.

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

Ошибка 1. Считать только volume. Pallet positions важнее: 1 small SKU занимает позицию, но 0.1 cbm.

Ошибка 2. Usable vs total capacity. Aisles, picking paths — 30-40% не utilizable. Используйте usable capacity, не total.

Ошибка 3. Включать reserved/blocked. Reserved для order shipping не «free». Сегментируйте «net available».

Ошибка 4. Один target для всех warehouses. Distribution center vs fulfillment center — разные targets.

Ошибка 5. Не учитывать seasonal swing. Black Friday → 100%+ peak. Average все равно 75% — норма.

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

• Как посчитать inventory turnover в SQL
• Как посчитать stockout rate в SQL
• Как посчитать inventory days on hand в SQL
• Как посчитать sell-through rate в SQL

FAQ

Какой utilization optimal?

75-85% average. 90%+ — bottleneck. Меньше 60% — overcapacity.

CBM vs pallets?

CBM для bulk irregular. Pallets для standardized.

Peak vs daily — что считать?

Average — для planning. Peak — для capacity sizing.

Underutilized — что делать?

Sublease, consolidate с другим warehouse, или принять (cheap insurance).

When to expand?

Average свыше 85% за 2-3 месяца — план expansion.