SQL Energy Analytics: Measure, Analyze, and Optimize Energy Consumption

Use PostgreSQL to analyze machine energy consumption, calculate energy KPIs, compare equipment efficiency, and identify where industrial energy is being used.

Energy is one of the most useful industrial signals because it can be analyzed at the same time as production, machines, downtime and maintenance. In this article, we use PostgreSQL to move from a simple energy measurement to actionable manufacturing KPIs.

The Smart Factory dataset contains 2,172 energy measurements, covering 12 machines over the January–June 2026 period. The underlying table stores the measurement timestamp, energy in kWh and power in kW.

---

1. Start with the energy table

The central table is smart_factory.energy_consumption.

CREATE TABLE smart_factory.energy_consumption (
    energy_id bigint NOT NULL,
    machine_id integer NOT NULL,
    measurement_timestamp timestamp NOT NULL,
    energy_kwh numeric(12,4) NOT NULL,
    power_kw numeric(12,4) NOT NULL
);

Each record therefore answers two questions:

• How much energy was consumed? → energy_kwh
• What power level was measured? → power_kw

---

2. How much energy does the factory consume?

Before comparing machines, establish a baseline for the complete dataset.

SELECT
    COUNT(*) AS measurements,
    ROUND(SUM(energy_kwh), 2) AS total_energy_kwh,
    ROUND(AVG(energy_kwh), 2) AS avg_energy_per_measurement,
    ROUND(AVG(power_kw), 2) AS avg_power_kw,
    ROUND(MIN(power_kw), 2) AS min_power_kw,
    ROUND(MAX(power_kw), 2) AS max_power_kw
FROM smart_factory.energy_consumption;

From the real dump:

KPI	Value
Energy measurements	2,172
Total recorded energy	293,796.90 kWh
Average energy / measurement	135.27 kWh
Average measured power	15.48 kW

This baseline becomes useful when we later compare machines and time periods.

---

3. Which machines consume the most energy?

The first useful ranking is total energy consumption by machine.

SELECT
    m.machine_code,
    m.machine_name,
    ROUND(SUM(e.energy_kwh), 2) AS total_energy_kwh,
    ROUND(AVG(e.power_kw), 2) AS avg_power_kw,
    ROUND(MAX(e.power_kw), 2) AS max_power_kw
FROM smart_factory.energy_consumption e
JOIN smart_factory.machines m
    ON m.machine_id = e.machine_id
GROUP BY
    m.machine_code,
    m.machine_name
ORDER BY total_energy_kwh DESC;

The largest consumers in the dataset are:

Machine	Energy	Avg power
M005 — CNC Machine B2	48,470.40 kWh	30.81 kW
M004 — CNC Machine B1	41,762.06 kWh	26.27 kW
M006 — CNC Machine B3	39,496.72 kWh	24.70 kW
M007 — Grinding Machine B4	32,657.33 kWh	20.63 kW
M011 — Packaging Machine C4	26,083.35 kWh	16.64 kW

M005 is the largest energy consumer in the dataset.

However, total energy alone can be misleading. A machine producing much more can naturally consume more energy. We therefore need an efficiency metric.

---

4. Energy consumption by production line

Machines belong to production lines. We can therefore aggregate energy at the factory-flow level.

SELECT
    pl.line_code,
    pl.line_name,
    ROUND(SUM(e.energy_kwh), 2) AS total_energy_kwh,
    ROUND(
        100.0 * SUM(e.energy_kwh)
        / SUM(SUM(e.energy_kwh)) OVER (),
        2
    ) AS pct_of_total
FROM smart_factory.energy_consumption e
JOIN smart_factory.machines m
    ON m.machine_id = e.machine_id
JOIN smart_factory.production_lines pl
    ON pl.line_id = m.line_id
GROUP BY
    pl.line_code,
    pl.line_name
ORDER BY total_energy_kwh DESC;

The three production lines are:

Line	Name	Energy	Share
L02	Heavy Machining Line	162,386.51 kWh	55.27%
L03	Final Assembly & Packaging	82,730.58 kWh	28.16%
L01	Precision Assembly Line	48,679.81 kWh	16.57%

The line totals account for all 12 machines in the energy table. For production intensity, we only calculate kWh/unit where corresponding production-event records exist. This is precisely why an industrial database should be analyzed through explicit relationships rather than assumptions.

---

5. Energy intensity: kWh per unit produced

A more useful KPI is:

How much energy was consumed for each produced unit?

We can combine energy data with production events.

WITH energy AS (
    SELECT
        machine_id,
        SUM(energy_kwh) AS energy_kwh
    FROM smart_factory.energy_consumption
    GROUP BY machine_id
),
production AS (
    SELECT
        machine_id,
        SUM(quantity_produced) AS units_produced
    FROM smart_factory.production_events
    GROUP BY machine_id
)
SELECT
    m.machine_code,
    m.machine_name,
    ROUND(e.energy_kwh, 2) AS energy_kwh,
    p.units_produced,
    ROUND(
        e.energy_kwh / NULLIF(p.units_produced, 0),
        4
    ) AS kwh_per_unit
FROM energy e
JOIN production p
    ON p.machine_id = e.machine_id
JOIN smart_factory.machines m
    ON m.machine_id = e.machine_id
ORDER BY kwh_per_unit DESC;

Among machines with production-event data, the highest energy intensity is:

Machine	Energy	Produced	kWh/unit
M006 — CNC Machine B3	39,496.72	30,205	1.3076
M007 — Grinding Machine B4	32,657.33	30,592	1.0675
M005 — CNC Machine B2	48,470.40	60,962	0.7951
M011 — Packaging Machine C4	26,083.35	41,904	0.6225
M002 — Assembly Press A2	20,192.54	43,383	0.4654

This produces a much more interesting industrial conclusion:

M005 consumes the most total energy, but M006 has the highest energy intensity among the machines for which production output is available.

---

6. Energy consumption over time

PostgreSQL's date_trunc() function is particularly useful for industrial time-series aggregation. It can truncate timestamps to a selected precision such as day or month. citeturn0search0turn0search6

SELECT
    DATE_TRUNC('month', measurement_timestamp)::date AS month,
    ROUND(SUM(energy_kwh), 2) AS total_energy_kwh,
    ROUND(AVG(power_kw), 2) AS avg_power_kw,
    ROUND(MAX(power_kw), 2) AS max_power_kw
FROM smart_factory.energy_consumption
GROUP BY 1
ORDER BY 1;

The monthly totals are:

Month	Energy
January 2026	49,807.86 kWh
February 2026	44,869.88 kWh
March 2026	51,298.48 kWh
April 2026	48,957.17 kWh
May 2026	50,726.53 kWh
June 2026	48,136.99 kWh

March is the highest-energy month in the dataset, while February is the lowest.

---

7. Detect unusually high-energy days

We can now move toward anomaly detection using a CTE.

WITH daily_energy AS (
    SELECT
        measurement_timestamp::date AS day,
        SUM(energy_kwh) AS total_energy_kwh
    FROM smart_factory.energy_consumption
    GROUP BY 1
),
baseline AS (
    SELECT AVG(total_energy_kwh) AS avg_daily_energy
    FROM daily_energy
)
SELECT
    d.day,
    ROUND(d.total_energy_kwh, 2) AS total_energy_kwh,
    ROUND(b.avg_daily_energy, 2) AS avg_daily_energy,
    ROUND(
        100.0 * (d.total_energy_kwh - b.avg_daily_energy)
        / NULLIF(b.avg_daily_energy, 0),
        2
    ) AS deviation_pct
FROM daily_energy d
CROSS JOIN baseline b
WHERE d.total_energy_kwh >
      b.avg_daily_energy * 1.20
ORDER BY deviation_pct DESC;

This is a very practical pattern for Smart Factory analytics:

raw measurements
      ↓
daily aggregation
      ↓
baseline
      ↓
deviation
      ↓
potential anomaly
      ↓
industrial investigation

SQL is therefore not limited to reporting. It can become the first layer of an industrial anomaly-detection workflow.

---

8. Energy is not the same as efficiency

This distinction is essential.

• Total energy tells you how much a machine consumed.
• Average power tells you the typical power level.
• Peak power reveals high-load or transient conditions.
• Energy per unit connects energy to production output.

For example, M005 is the largest total consumer, but M006 has the highest calculated kWh/unit among machines with production-event data.

That means an energy-reduction project should not automatically target the largest consumer. It should investigate both absolute consumption and energy intensity.

---

9. SQL challenge

1. Find the machine with the highest average power.
2. Find the machine with the largest difference between maximum and average power.
3. Calculate daily energy consumption for each production line.
4. Find the five days with the highest total energy consumption.
5. Calculate monthly energy consumption per machine.
6. Calculate kWh per produced unit for every machine with production data.
7. Compare energy intensity with reject rate.

Advanced challenge: use LAG() to calculate the month-to-month change in energy consumption and identify the largest increase.

---

10. What you learned

• How to aggregate energy consumption with SUM().
• How to compare machines using AVG() and MAX().
• How to connect energy data to machine and production data.
• How to calculate energy intensity in kWh per produced unit.
• How to aggregate industrial measurements by month with date_trunc().
• How to use CTEs to build an anomaly-detection query.
• Why total consumption and efficiency are different KPIs.

---

Work with the complete Smart Factory dataset

The complete dataset brings together production, machines, downtime, maintenance, quality and energy data in one PostgreSQL manufacturing environment.

Smart Factory Manufacturing Dataset v0.1 — $9

→ Get the complete dataset on Gumroad

Smart Factory SQL Series
PostgreSQL • Industrial SQL • Industry 4.0 • Manufacturing Analytics