Statistical Process Control with Streaming SQL

Statistical Process Control (SPC) has been a manufacturing quality cornerstone for decades, but traditional SPC systems evaluate control charts after data is collected—not in real time as measurements arrive. RisingWave, a PostgreSQL-compatible streaming database, enables continuous SPC computation: control limits calculated from live process data, Cpk updated with every measurement, and out-of-control signals fired within seconds of detection.

Why Real-Time SPC Matters in Manufacturing Quality

SPC uses statistical methods to distinguish natural process variation from assignable causes—signals that indicate something has changed in the process and requires investigation. The classic tools are control charts (X-bar, R-chart, individuals chart) and process capability indices like Cpk and Cp.

The problem with traditional SPC implementations:

• SPC software runs on collected batches: operators submit measurement data, software recomputes charts, and engineers review results—sometimes hours after the out-of-control condition began.
• Subgroup formation is manual: operators measure parts at intervals and enter readings, introducing transcription errors and gaps.
• Control limits are static: calculated once from a baseline study and rarely updated, even as the process drifts gradually over months.
• Cpk reporting is periodic: monthly or quarterly summaries mask within-shift variation that would trigger immediate action if visible.

Real-time SPC with streaming SQL closes each of these gaps. Measurements from coordinate measuring machines (CMMs), laser micrometers, and inline gauging flow into the system continuously. Control limits are recomputed dynamically. Out-of-control rules are evaluated as each point is plotted.

How Streaming SQL Solves This

RisingWave ingests measurement events from inline quality sensors and CMMs, maintaining materialized views that compute control chart statistics and process capability indices continuously. Quality engineers define Western Electric rules and specification limits in SQL; the engine evaluates them incrementally.

Key features for SPC:

• Rolling mean and standard deviation per characteristic and part number, updated with each measurement
• Subgroup statistics (X-bar, R, S) computed over configurable subgroup sizes using window functions
• Dynamic Cpk computation from the current process window without a separate reporting job
• Out-of-control signal detection using SQL CASE logic implementing Western Electric or Nelson rules

Building the System

Step 1: Data Source

CREATE SOURCE quality_measurements (
    measurement_id     VARCHAR,
    line_id            VARCHAR,
    machine_id         VARCHAR,
    part_number        VARCHAR,
    characteristic_id  VARCHAR,  -- e.g. 'DIM_A_DIAMETER','SURFACE_ROUGHNESS'
    shift_code         VARCHAR,
    measured_value     FLOAT,
    usl                FLOAT,    -- upper spec limit from engineering drawing
    lsl                FLOAT,    -- lower spec limit
    nominal            FLOAT,    -- target value
    measurement_time   TIMESTAMPTZ,
    operator_id        VARCHAR,
    gauge_id           VARCHAR
)
WITH (
    connector = 'kafka',
    topic = 'manufacturing.quality.measurements',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Real-Time View

Compute rolling process statistics per characteristic including mean, standard deviation, and process capability indices. A 1-hour rolling window provides current-shift Cpk:

CREATE MATERIALIZED VIEW spc_process_stats AS
SELECT
    line_id,
    characteristic_id,
    part_number,
    shift_code,
    window_start,
    window_end,
    COUNT(*)                    AS sample_count,
    AVG(measured_value)         AS process_mean,
    STDDEV_POP(measured_value)  AS process_stddev,
    MIN(measured_value)         AS min_value,
    MAX(measured_value)         AS max_value,
    MAX(measured_value) - MIN(measured_value) AS range_value,
    -- Cpk: min of (USL - mean) / 3σ and (mean - LSL) / 3σ
    CASE
        WHEN STDDEV_POP(measured_value) > 0 THEN
            LEAST(
                (AVG(usl) - AVG(measured_value)) / (3 * STDDEV_POP(measured_value)),
                (AVG(measured_value) - AVG(lsl)) / (3 * STDDEV_POP(measured_value))
            )
        ELSE NULL
    END AS cpk,
    -- Cp: (USL - LSL) / 6σ
    CASE
        WHEN STDDEV_POP(measured_value) > 0 THEN
            (AVG(usl) - AVG(lsl)) / (6 * STDDEV_POP(measured_value))
        ELSE NULL
    END AS cp,
    AVG(usl) AS usl,
    AVG(lsl) AS lsl
FROM TUMBLE(
    quality_measurements,
    measurement_time,
    INTERVAL '1 hour'
)
GROUP BY line_id, characteristic_id, part_number, shift_code, window_start, window_end;

Compute X-bar and R-chart control limits dynamically from recent subgroups:

CREATE MATERIALIZED VIEW xbar_control_limits AS
SELECT
    line_id,
    characteristic_id,
    window_start,
    window_end,
    process_mean                            AS x_bar,
    process_mean + (3 * process_stddev)     AS ucl,
    process_mean - (3 * process_stddev)     AS lcl,
    process_mean + (2 * process_stddev)     AS warning_ucl,
    process_mean - (2 * process_stddev)     AS warning_lcl,
    range_value                             AS r_bar,
    sample_count,
    cpk
FROM spc_process_stats;

Step 3: Alerts

Detect out-of-control conditions using Western Electric Rule 1 (point beyond 3-sigma control limits) and low Cpk, routing alerts to quality engineering:

CREATE MATERIALIZED VIEW spc_ooc_alerts AS
SELECT
    m.line_id,
    m.characteristic_id,
    m.part_number,
    m.measured_value,
    m.measurement_time,
    cl.ucl,
    cl.lcl,
    cl.cpk,
    CASE
        WHEN m.measured_value > cl.ucl          THEN 'ABOVE_UCL'
        WHEN m.measured_value < cl.lcl          THEN 'BELOW_LCL'
        WHEN m.measured_value > m.usl           THEN 'SPEC_VIOLATION_HIGH'
        WHEN m.measured_value < m.lsl           THEN 'SPEC_VIOLATION_LOW'
        WHEN cl.cpk < 1.33                      THEN 'CPK_BELOW_TARGET'
        ELSE 'IN_CONTROL'
    END AS ooc_rule
FROM quality_measurements m
JOIN xbar_control_limits cl
    ON m.line_id = cl.line_id
    AND m.characteristic_id = cl.characteristic_id
WHERE m.measured_value > cl.ucl
   OR m.measured_value < cl.lcl
   OR m.measured_value > m.usl
   OR m.measured_value < m.lsl
   OR cl.cpk < 1.33;

CREATE SINK spc_alerts_sink
FROM spc_ooc_alerts
WITH (
    connector = 'kafka',
    topic = 'manufacturing.quality.spc.alerts',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

Q: How do I implement Western Electric Rule 2 (two of three consecutive points beyond 2-sigma)? This requires tracking consecutive measurement sequences. RisingWave supports window functions including LAG() over partitioned streams. You can create a view that uses LAG() over the measurement stream partitioned by characteristic_id to check the previous two measurements against the 2-sigma warning limit.

Q: Should control limits be recalculated from a fixed baseline or dynamically from recent data? Both approaches are valid. RisingWave supports both: use a fixed reference table for static control limits (updated periodically via a batch source), or compute dynamic limits from a rolling window of recent measurements as shown above. Many quality engineers prefer static limits for stable processes and dynamic limits during process qualification.

Q: Can RisingWave handle measurement data from CMMs that produce hundreds of characteristics per part? Yes. RisingWave's materialized views partition naturally by characteristic_id, processing each characteristic independently within the same query. The system scales horizontally as measurement volume increases, and all characteristics in a batch CMM report are processed concurrently.

Key Takeaways

• Real-time SPC with streaming SQL enables out-of-control signal detection within seconds of measurement, rather than minutes or hours after batch collection.
• RisingWave's windowed aggregations compute process mean, standard deviation, Cpk, and Cp continuously, providing a live process capability snapshot without separate reporting jobs.
• Dynamic control limits derived from the current process window reflect actual process behavior, reducing false alarms from limits calculated on outdated baseline studies.
• SQL-expressed Western Electric rules are readable, auditable, and modifiable by quality engineers without custom streaming code, lowering the barrier to advanced SPC implementation.

Get started | Slack