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

Зачем Defect Rate

Defect Rate — % units that fail quality check. Production line, software releases, support tickets. Лежит в основе Six Sigma — 3.4 defects per million.

Формула

Defect Rate = defects / units_produced × 100%

DPMO (Defects per million opportunities):

DPMO = (defects / (units × opportunities_per_unit)) × 1,000,000

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

Данные: production_units(unit_id, produced_at, passed_qc, defect_type).

SELECT
    DATE_TRUNC('day', produced_at) AS day,
    COUNT(*) AS total_units,
    COUNT(*) FILTER (WHERE NOT passed_qc) AS defects,
    COUNT(*) FILTER (WHERE NOT passed_qc)::NUMERIC * 100 / COUNT(*) AS defect_rate_pct
FROM production_units
WHERE produced_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

По продуктам

SELECT
    product_id,
    COUNT(*) AS total_units,
    COUNT(*) FILTER (WHERE NOT passed_qc) AS defects,
    COUNT(*) FILTER (WHERE NOT passed_qc)::NUMERIC * 100 / COUNT(*) AS defect_rate_pct,
    -- Top defect types
    MODE() WITHIN GROUP (ORDER BY defect_type) FILTER (WHERE NOT passed_qc) AS top_defect_type
FROM production_units
WHERE produced_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY product_id
HAVING COUNT(*) FILTER (WHERE NOT passed_qc) > 0
ORDER BY defect_rate_pct DESC;

DPMO (Six Sigma)

WITH stats AS (
    SELECT
        SUM(units_produced * opportunities_per_unit) AS total_opportunities,
        SUM(defects_found) AS total_defects
    FROM qc_logs
    WHERE DATE >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    total_opportunities,
    total_defects,
    (total_defects::NUMERIC / NULLIF(total_opportunities, 0)) * 1000000 AS dpmo,
    -- Sigma level approximation
    CASE
        WHEN (total_defects::NUMERIC / NULLIF(total_opportunities, 0)) * 1000000 < 3.4 THEN '6σ (world-class)'
        WHEN (total_defects::NUMERIC / NULLIF(total_opportunities, 0)) * 1000000 < 233 THEN '5σ (excellent)'
        WHEN (total_defects::NUMERIC / NULLIF(total_opportunities, 0)) * 1000000 < 6210 THEN '4σ (average)'
        WHEN (total_defects::NUMERIC / NULLIF(total_opportunities, 0)) * 1000000 < 66807 THEN '3σ (below avg)'
        ELSE '<3σ (poor)'
    END AS sigma_level
FROM stats;

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

Ошибка 1. Defect ≠ defect. Major vs minor defects — weight separately.

Ошибка 2. Sample vs population. QC sample (e.g. 10%) — extrapolate carefully.

Ошибка 3. Discovery delay. Defect found at customer 3 months later — attribute to production date?

Ошибка 4. False positives в QC. QC system itself can be wrong. Re-test.

Ошибка 5. Definition drift. «Passing tolerance» changes over time. Stable definition critical.

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

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

FAQ

Какой Defect Rate ok?

Manufacturing: < 1% — good, < 0.1% — excellent. Software: critical bugs < 0.5% releases.

DPMO vs Defect Rate?

DPMO нормализует by «opportunities» — позволяет сравнивать complex products. Defect Rate — unit-level.

Defect vs Defective?

Defective unit = unit with ≥1 defect. Unit can have multiple defects.

Six Sigma's 3.4 DPMO?

«World-class» quality. Less than 4 defects per million opportunities.

Defect Rate trending up?

Process degradation. Root cause: equipment wear, material change, training gap.