Как посчитать First Pass Yield в SQL

Зачем FPY

First Pass Yield (FPY) = % units, прошедшие через process без rework. Rework — hidden cost factory. FPY 95% звучит хорошо, но при 5 sequential stages общий yield = 0.95^5 = 77%.

Формула

FPY = units_passed_first_time / units_started × 100%

«First time» = no rework, no rejection.

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

SELECT
    DATE_TRUNC('day', stage_end) AS day,
    COUNT(*) AS units_processed,
    COUNT(*) FILTER (WHERE result = 'pass' AND rework_count = 0) AS first_pass,
    COUNT(*) FILTER (WHERE result = 'pass' AND rework_count = 0)::NUMERIC * 100 / COUNT(*) AS fpy_pct
FROM process_stage_log
WHERE stage_end >= CURRENT_DATE - INTERVAL '30 days'
  AND stage_name = 'final_assembly'
GROUP BY 1
ORDER BY 1;

Rolled Throughput Yield

RTY — overall yield для multi-stage processes:

RTY = FPY_stage1 × FPY_stage2 × ... × FPY_stageN

WITH stage_fpy AS (
    SELECT
        stage_name,
        COUNT(*) FILTER (WHERE result = 'pass' AND rework_count = 0)::NUMERIC
        / NULLIF(COUNT(*), 0) AS fpy
    FROM process_stage_log
    WHERE stage_end >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY stage_name
)
SELECT
    stage_name,
    fpy,
    -- Cumulative RTY using EXP(SUM(LN()))
    EXP(SUM(LN(NULLIF(fpy, 0))) OVER (ORDER BY stage_name)) AS rty_cumulative
FROM stage_fpy
ORDER BY stage_name;

По стадиям

Какая stage — bottleneck по FPY:

SELECT
    stage_name,
    COUNT(*) AS units,
    COUNT(*) FILTER (WHERE result = 'fail') AS fails,
    COUNT(*) FILTER (WHERE rework_count > 0) AS reworks,
    COUNT(*) FILTER (WHERE result = 'pass' AND rework_count = 0)::NUMERIC * 100 / COUNT(*) AS fpy_pct
FROM process_stage_log
WHERE stage_end >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY stage_name
ORDER BY fpy_pct;  -- bottleneck first

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

Ошибка 1. FPY = final yield. Confuse them. FPY — at each stage. Final yield — units that exit process.

Ошибка 2. Rework hidden. Reworked unit eventually passes — counts? No (rework count > 0).

Ошибка 3. Sequential vs parallel. RTY = product of FPYs только для sequential. Parallel branches — другая math.

Ошибка 4. Scrap vs rework. Scrap = thrown away (lost). Rework = fix + re-test. Treat separately.

Ошибка 5. Sampling. Sample-based QC underestimates defects. Random sampling vs 100% inspection.

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

• Как посчитать defect rate в SQL
• Как посчитать perfect order rate в SQL
• Как посчитать fill rate в SQL
• Incident response на собесе SA

FAQ

Какой FPY ok?

Manufacturing: 90%+ — average, 99%+ — excellent. Software: 80%+ deployment success.

FPY vs RTY?

FPY — single stage. RTY — multi-stage compound.

Rework считается?

No. FPY = unit passed first time, no rework. Hidden Factory = cost of rework.

Why RTY drops fast?

Multiplicative. 5 stages × 95% = 77%. 10 stages × 95% = 60%. Compound effect.

FPY for services?

Yes. Customer onboarding stages: signup → verify → activate → first action. Each — FPY measurable.