Advanced Window Functions for OEE Analysis

Use PostgreSQL window functions to calculate, compare, rank, and trend OEE components across machines and production periods.

Smart Factory SQL Series — Article 17
PostgreSQL • Window Functions • OEE • Industrial KPI Analytics

Advanced Window Functions for OEE Analysis

Industrial question:
Which machines are losing OEE, which component is responsible, and how can PostgreSQL reveal the change without collapsing the data into a single average?

1. Why OEE Needs More Than a Simple Average

OEE is commonly expressed as:

OEE = Availability × Performance × Quality

The difficulty is not the multiplication. The difficulty is constructing defensible Availability, Performance, and Quality measures from heterogeneous shop-floor data.

Our Smart Factory database contains production orders, timestamped production events, downtime intervals, products with standard cycle times, and quality inspections. The objective is to transform these operational records into a temporal KPI layer that can be compared from one order, machine, day, or month to another.

2. The Database Model Behind the KPI

The production event table records the event timestamp, machine, event type, quantity produced, quantity rejected, and observed cycle time.

SELECT
    event_id,
    order_id,
    machine_id,
    event_timestamp,
    event_type,
    quantity_produced,
    quantity_rejected,
    cycle_time_sec
FROM smart_factory.production_events
ORDER BY event_timestamp
LIMIT 20;

Production orders provide planned and actual quantities plus planned and actual start/end timestamps. Products provide the standard cycle time used as the performance reference.

SELECT
    po.order_number,
    p.product_code,
    p.standard_cycle_time_sec,
    po.planned_quantity,
    po.actual_quantity,
    po.rejected_quantity
FROM smart_factory.production_orders po
JOIN smart_factory.products p
  ON p.product_id = po.product_id
ORDER BY po.order_id
LIMIT 20;

3. Important Data-Semantics Decision

In this training database, cycle_time_sec is recorded on production events, while each event can contain a quantity greater than one. Therefore, this article treats the observed event cycle time as the duration of the production cycle and compares it with the product's standard cycle time.

This matters. Multiplying the standard cycle time by every unit would produce an invalid performance calculation if one event represents a multi-unit production cycle.

Rule: Always establish the semantic meaning of the industrial measurement before applying an OEE formula.

4. Build the Order-Level OEE Components

We first calculate the three components at production-order level. Availability uses planned production time and unplanned downtime overlapping the order. Performance compares the standard cycle with observed production-cycle duration. Quality uses good quantity divided by total quantity.

WITH production AS (
    SELECT
        order_id,
        machine_id,
        SUM(quantity_produced) AS total_count,
        SUM(quantity_rejected) AS rejected_count,
        AVG(cycle_time_sec) AS avg_cycle_time_sec
    FROM smart_factory.production_events
    WHERE event_type = 'PRODUCTION'
    GROUP BY order_id, machine_id
)
SELECT
    po.order_number,
    p.product_code,
    p.standard_cycle_time_sec,
    pr.total_count,
    pr.rejected_count,
    ROUND(
        100.0 * p.standard_cycle_time_sec
        / NULLIF(pr.avg_cycle_time_sec, 0), 2
    ) AS performance_pct,
    ROUND(
        100.0 * (pr.total_count - pr.rejected_count)
        / NULLIF(pr.total_count, 0), 2
    ) AS quality_pct
FROM smart_factory.production_orders po
JOIN smart_factory.products p
  ON p.product_id = po.product_id
JOIN production pr
  ON pr.order_id = po.order_id
ORDER BY po.order_id
LIMIT 20;

5. Window Functions Enter the KPI Layer

Once OEE components exist at a consistent grain, window functions allow us to compare one observation with its temporal neighborhood.

The first tool is LAG().

SELECT
    month,
    machine_code,
    quantity_produced,
    LAG(quantity_produced)
        OVER (
            PARTITION BY machine_code
            ORDER BY month
        ) AS previous_month_production
FROM smart_factory.v_machine_monthly_performance
ORDER BY machine_code, month;

This changes the analytical question from:

"How much did the machine produce?"

to:

"How did production change compared with the previous period?"

6. Calculate Month-over-Month Change

WITH monthly AS (
    SELECT
        month,
        machine_code,
        quantity_produced,
        LAG(quantity_produced)
            OVER (
                PARTITION BY machine_code
                ORDER BY month
            ) AS previous_quantity
    FROM smart_factory.v_machine_monthly_performance
)
SELECT
    month,
    machine_code,
    quantity_produced,
    previous_quantity,
    ROUND(
        100.0 * (
            quantity_produced - previous_quantity
        ) / NULLIF(previous_quantity, 0),
        2
    ) AS production_change_pct
FROM monthly
ORDER BY machine_code, month;

7. Rank Machines by OEE

RANK() and DENSE_RANK() turn a KPI table into a prioritization mechanism.

SELECT
    machine_code,
    oee_pct,
    RANK() OVER (
        ORDER BY oee_pct DESC
    ) AS oee_rank
FROM smart_factory.v_machine_oee
ORDER BY oee_rank;

If the OEE view does not yet exist, build the OEE components first and expose them through a semantic view. The important architecture is to keep the raw event tables separate from the KPI layer.

8. Compare Each Machine With the Factory Average

SELECT
    machine_code,
    oee_pct,
    ROUND(
        AVG(oee_pct) OVER (),
        2
    ) AS factory_avg_oee,
    ROUND(
        oee_pct - AVG(oee_pct) OVER (),
        2
    ) AS oee_gap_vs_factory
FROM smart_factory.v_machine_oee
ORDER BY oee_gap_vs_factory;

This is a classic window-function pattern: the factory average is calculated without collapsing the machine rows.

9. Compare a Machine With Its Production Line

SELECT
    line_code,
    machine_code,
    oee_pct,
    ROUND(
        AVG(oee_pct) OVER (
            PARTITION BY line_code
        ),
        2
    ) AS line_avg_oee,
    ROUND(
        oee_pct -
        AVG(oee_pct) OVER (
            PARTITION BY line_code
        ),
        2
    ) AS gap_vs_line
FROM smart_factory.v_machine_oee
ORDER BY line_code, gap_vs_line;

This is often more actionable than a factory-wide ranking. A machine should first be compared with the operational context in which it works.

10. Rolling OEE Trend

A rolling average reduces the noise of individual production periods.

SELECT
    day,
    machine_code,
    oee_pct,
    ROUND(
        AVG(oee_pct) OVER (
            PARTITION BY machine_code
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS oee_7_period_avg
FROM smart_factory.v_machine_daily_oee
ORDER BY machine_code, day;

The seven-period window is not automatically a seven-day business rule. It means seven observations. If the source has missing days, build a complete calendar first.

11. Detect a Deteriorating OEE Trend

WITH trend AS (
    SELECT
        day,
        machine_code,
        oee_pct,
        AVG(oee_pct) OVER (
            PARTITION BY machine_code
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS rolling_oee,
        LAG(oee_pct, 7) OVER (
            PARTITION BY machine_code
            ORDER BY day
        ) AS oee_7_periods_ago
    FROM smart_factory.v_machine_daily_oee
)
SELECT
    day,
    machine_code,
    ROUND(oee_pct,2) AS oee_pct,
    ROUND(rolling_oee,2) AS rolling_oee,
    ROUND(
        oee_pct - oee_7_periods_ago,
        2
    ) AS change_vs_7_periods
FROM trend
WHERE oee_7_periods_ago IS NOT NULL
ORDER BY change_vs_7_periods ASC;

Now the SQL identifies machines whose current OEE is deteriorating relative to their recent history.

12. Which OEE Component Is Responsible?

A declining OEE value is not an explanation. We need to decompose the change.

SELECT
    day,
    machine_code,
    availability_pct,
    performance_pct,
    quality_pct,
    oee_pct,

    LAG(availability_pct)
        OVER (
            PARTITION BY machine_code
            ORDER BY day
        ) AS previous_availability,

    LAG(performance_pct)
        OVER (
            PARTITION BY machine_code
            ORDER BY day
        ) AS previous_performance,

    LAG(quality_pct)
        OVER (
            PARTITION BY machine_code
            ORDER BY day
        ) AS previous_quality

FROM smart_factory.v_machine_daily_oee
ORDER BY machine_code, day;

This lets the analyst determine whether the loss is primarily caused by downtime, slower cycles, or rejected production.

13. The OEE Loss Tree in SQL

Instead of presenting one KPI, create a loss-oriented ranking.

SELECT
    machine_code,
    availability_pct,
    performance_pct,
    quality_pct,
    oee_pct,
    ROUND(100 - availability_pct, 2)
        AS availability_loss_pct,
    ROUND(100 - performance_pct, 2)
        AS performance_loss_pct,
    ROUND(100 - quality_pct, 2)
        AS quality_loss_pct
FROM smart_factory.v_machine_oee
ORDER BY oee_pct;

This produces a simple loss tree: the largest gap identifies where the next investigation should begin.

14. Use NTILE() to Segment Machines

SELECT
    machine_code,
    oee_pct,
    NTILE(4) OVER (
        ORDER BY oee_pct DESC
    ) AS oee_quartile
FROM smart_factory.v_machine_oee
ORDER BY oee_rank;

Quartile segmentation is useful when a factory wants to distinguish top performers, normal performers, improvement candidates, and critical machines without choosing arbitrary thresholds.

15. Detect the Worst Recent Performance

WITH ranked AS (
    SELECT
        day,
        machine_code,
        oee_pct,
        ROW_NUMBER() OVER (
            PARTITION BY machine_code
            ORDER BY oee_pct ASC
        ) AS worst_rank
    FROM smart_factory.v_machine_daily_oee
)
SELECT
    day,
    machine_code,
    oee_pct
FROM ranked
WHERE worst_rank <= 3
ORDER BY machine_code, worst_rank;

ROW_NUMBER() is particularly useful when the requirement is "give me the three worst observations for every machine."

16. Connect OEE Loss to Downtime

The database stores downtime as intervals with machine, order, start time, end time, reason category, reason code and a planned/unplanned flag.

SELECT
    machine_id,
    reason_category,
    reason_code,
    planned,
    COUNT(*) AS events,
    ROUND(
        SUM(
            EXTRACT(
                EPOCH FROM (end_time - start_time)
            )
        ) / 60.0,
        2
    ) AS downtime_minutes
FROM smart_factory.downtime_events
WHERE end_time IS NOT NULL
GROUP BY
    machine_id,
    reason_category,
    reason_code,
    planned
ORDER BY downtime_minutes DESC;

The next step is to use window ranking to identify the dominant downtime causes per machine.

WITH causes AS (
    SELECT
        machine_id,
        reason_category,
        reason_code,
        SUM(
            EXTRACT(
                EPOCH FROM (end_time - start_time)
            )
        ) / 60.0 AS downtime_minutes
    FROM smart_factory.downtime_events
    WHERE end_time IS NOT NULL
    GROUP BY machine_id, reason_category, reason_code
),
ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY machine_id
            ORDER BY downtime_minutes DESC
        ) AS cause_rank
    FROM causes
)
SELECT *
FROM ranked
WHERE cause_rank <= 3
ORDER BY machine_id, cause_rank;

17. Real Database Signal: M005

Using the supplied dump and the methodology defined in this article, M005 — CNC Machine B2 — emerges as the weakest machine in the illustrative machine-level OEE calculation, at approximately 90.5%.

Its approximate component profile is:

Machine	Availability	Performance	Quality	OEE
M005 — CNC Machine B2	99.3%	94.1%	96.9%	90.5%

The important observation is that Availability is not the principal weakness. Performance and Quality contribute more strongly to the OEE gap.

The dump also contains multiple unplanned mechanical and electrical failures associated with M005, including spindle-vibration events. That gives us a concrete path from KPI → component → operational cause.

18. Rank the Losses With a Window Function

WITH losses AS (
    SELECT
        machine_id,
        reason_category,
        SUM(
            EXTRACT(
                EPOCH FROM (end_time-start_time)
            )
        )/60.0 AS downtime_minutes
    FROM smart_factory.downtime_events
    WHERE end_time IS NOT NULL
      AND planned = false
    GROUP BY machine_id, reason_category
)
SELECT
    machine_id,
    reason_category,
    ROUND(downtime_minutes,2) AS downtime_minutes,
    RANK() OVER (
        PARTITION BY machine_id
        ORDER BY downtime_minutes DESC
    ) AS loss_rank
FROM losses
ORDER BY machine_id, loss_rank;

19. Previous Article → This Article

Article 16 used window functions to analyze downtime trends over time. This article extends the same technique into the KPI layer, where Availability, Performance, Quality and OEE become comparable temporal signals.

20. This Article → Next Step

The next logical step is to move from KPI calculation to quality analytics and loss prioritization: defect Pareto analysis, first-pass yield, defect trends, and process capability.

Build the Complete Industrial SQL Toolkit

Get the complete Smart Factory SQL learning pack, database exercises, and practical industrial analytics workflow.

Available on Gumroad — $9

Get the SQL Pack on Gumroad

Smart Factory SQL Series
Previous: Article 16 — Using Window Functions to Analyze Downtime Trends Over Time
Next: Article 18 — Advanced Quality Analytics for Smart Factory Data

Labels: PostgreSQL, Industrial SQL, Smart Factory, Window Functions, OEE, Industrial KPI, Manufacturing Analytics