Real-Time Fuel Consumption Analytics for Trucking

Fuel is the single largest variable operating cost in trucking — typically 25-40% of total cost per mile. Real-time fuel consumption analytics built on RisingWave lets fleet managers identify inefficiencies as they happen: high idle time, speeding, over-revving, and suboptimal routes — not in yesterday's report.

Why Real-Time Fuel Consumption Analytics Matters

Fuel efficiency in trucking is a function of dozens of variables: speed, load weight, terrain, tire pressure, engine RPM, idle time, driver behavior, and ambient temperature. A driver who runs at 75 mph instead of 65 mph burns roughly 15-20% more fuel per mile. A truck that idles for two hours at a loading dock wastes around 1.5–2 gallons of diesel.

Across a fleet of 200 trucks, identifying and correcting these behaviors can save tens of thousands of dollars per month. The challenge is that batch analytics only show you what happened yesterday. By the time a fleet manager reviews an idle time report and contacts a driver, the driver has already idled for two hours at the next stop.

Real-time analytics changes the intervention model. Instead of coaching drivers on last week's behavior, a dispatcher can contact a driver the moment idle time exceeds 30 minutes and ask them to shut down the engine. Instead of reviewing weekly speed reports, the system flags speeding patterns within the current shift window.

RisingWave maintains continuously updated fuel consumption views from the telematics stream. Every telemetry event triggers an incremental update to the per-truck and per-driver fuel efficiency metrics.

The Streaming SQL Solution

Telematics events arrive in Kafka with fuel rate readings from the J1939 CAN bus. RisingWave maintains materialized views for per-truck fuel consumption, idle time, speed-related waste, and efficiency benchmarks. Alerts fire when consumption exceeds targets or idle time thresholds are crossed.

Tutorial: Building It Step by Step

Step 1: Set Up the Data Source

-- Truck telematics with fuel telemetry from J1939
CREATE SOURCE truck_telemetry (
    vin                 VARCHAR,
    driver_id           VARCHAR,
    event_ts            TIMESTAMPTZ,
    speed_mph           DOUBLE PRECISION,
    engine_rpm          INT,
    engine_load_pct     INT,
    fuel_rate_lph       DOUBLE PRECISION,   -- liters per hour from J1939 FuelRate PGN
    fuel_level_pct      INT,
    odometer_km         DOUBLE PRECISION,
    idle_engine_on      BOOLEAN,            -- speed < 1 mph, engine on
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    load_weight_kg      DOUBLE PRECISION,
    ambient_temp_c      DOUBLE PRECISION
)
WITH (
    connector = 'kafka',
    topic = 'fleet.telemetry',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Fuel card transactions (for reconciliation with telematics estimates)
CREATE SOURCE fuel_transactions (
    transaction_id      VARCHAR,
    vin                 VARCHAR,
    driver_id           VARCHAR,
    station_name        VARCHAR,
    latitude            DOUBLE PRECISION,
    longitude           DOUBLE PRECISION,
    fuel_liters         DOUBLE PRECISION,
    cost_usd            DOUBLE PRECISION,
    price_per_liter     DOUBLE PRECISION,
    transaction_ts      TIMESTAMPTZ
)
WITH (
    connector = 'kafka',
    topic = 'fleet.fuel_transactions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Build Real-Time Aggregations

-- Per-truck fuel consumption in 1-hour tumbling windows
CREATE MATERIALIZED VIEW truck_fuel_hourly AS
SELECT
    vin,
    driver_id,
    window_start,
    window_end,
    -- Fuel consumed (estimated from J1939 fuel rate, sampled every ~10s)
    SUM(fuel_rate_lph) / 360.0              AS est_fuel_consumed_l,
    -- Idle fuel waste
    SUM(fuel_rate_lph) FILTER (WHERE idle_engine_on) / 360.0 AS idle_fuel_l,
    -- Distance traveled
    MAX(odometer_km) - MIN(odometer_km)     AS distance_km,
    -- Fuel efficiency
    ROUND(
        (MAX(odometer_km) - MIN(odometer_km)) /
        NULLIF(SUM(fuel_rate_lph) / 360.0, 0),
        3
    )                                       AS km_per_liter,
    -- Speed profile
    AVG(speed_mph) FILTER (WHERE speed_mph > 0) AS avg_moving_speed_mph,
    COUNT(*) FILTER (WHERE speed_mph > 70)  AS overspeed_events,
    -- Engine efficiency
    AVG(engine_rpm) FILTER (WHERE speed_mph > 0) AS avg_moving_rpm,
    AVG(engine_load_pct)                    AS avg_engine_load_pct,
    -- Idle stats
    COUNT(*) FILTER (WHERE idle_engine_on)  AS idle_samples,
    -- Idle as percentage of total running time
    ROUND(
        COUNT(*) FILTER (WHERE idle_engine_on) * 100.0 / NULLIF(COUNT(*), 0),
        2
    )                                       AS idle_time_pct
FROM TUMBLE(truck_telemetry, event_ts, INTERVAL '1 HOUR')
GROUP BY vin, driver_id, window_start, window_end;

-- Rolling 24-hour fleet fuel performance benchmark
CREATE MATERIALIZED VIEW fleet_fuel_benchmark_24h AS
SELECT
    window_start,
    window_end,
    AVG(km_per_liter)                       AS fleet_avg_km_per_l,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY km_per_liter) AS p25_km_per_l,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY km_per_liter) AS p75_km_per_l,
    AVG(idle_time_pct)                      AS fleet_avg_idle_pct,
    AVG(avg_moving_speed_mph)               AS fleet_avg_speed_mph,
    SUM(est_fuel_consumed_l)                AS fleet_total_fuel_l,
    SUM(idle_fuel_l)                        AS fleet_total_idle_fuel_l
FROM TUMBLE(truck_fuel_hourly, window_end, INTERVAL '24 HOURS')
GROUP BY window_start, window_end;

-- Fuel card vs telematics reconciliation (1-hour window)
CREATE MATERIALIZED VIEW fuel_reconciliation AS
SELECT
    t.vin,
    t.driver_id,
    t.window_start,
    t.window_end,
    t.est_fuel_consumed_l   AS telematics_est_l,
    SUM(f.fuel_liters)      AS card_actual_l,
    SUM(f.cost_usd)         AS fuel_cost_usd,
    ABS(t.est_fuel_consumed_l - SUM(f.fuel_liters)) /
        NULLIF(SUM(f.fuel_liters), 0) * 100 AS variance_pct
FROM truck_fuel_hourly t
LEFT JOIN fuel_transactions f
    ON t.vin = f.vin
    AND f.transaction_ts BETWEEN t.window_start AND t.window_end
GROUP BY t.vin, t.driver_id, t.window_start, t.window_end, t.est_fuel_consumed_l;

Step 3: Detect Anomalies or Generate Alerts

-- Idle time alert: engine idle for more than 30 consecutive minutes
CREATE MATERIALIZED VIEW idle_time_alerts AS
SELECT
    vin,
    driver_id,
    window_start,
    window_end,
    idle_samples / 6.0              AS idle_minutes_est,   -- ~10s samples = 6/min
    idle_fuel_l,
    idle_time_pct,
    'EXCESSIVE_IDLE' AS alert_type
FROM truck_fuel_hourly
WHERE idle_time_pct > 30.0
  AND idle_samples > 180;  -- at least 30 minutes of idle samples

-- Fuel efficiency outlier: truck running 30% below fleet benchmark
CREATE MATERIALIZED VIEW fuel_efficiency_alerts AS
SELECT
    h.vin,
    h.driver_id,
    h.window_start,
    h.window_end,
    h.km_per_liter,
    b.fleet_avg_km_per_l,
    ROUND(h.km_per_liter / NULLIF(b.fleet_avg_km_per_l, 0), 2) AS efficiency_ratio,
    'LOW_FUEL_EFFICIENCY' AS alert_type
FROM truck_fuel_hourly h
JOIN fleet_fuel_benchmark_24h b
    ON h.window_end BETWEEN b.window_start AND b.window_end
WHERE h.km_per_liter < b.fleet_avg_km_per_l * 0.70
  AND h.distance_km > 50;  -- exclude short trips

-- Sink: fuel alerts to fleet ops
CREATE SINK fuel_alert_sink
FROM (
    SELECT vin, driver_id, CAST(idle_minutes_est AS VARCHAR) AS metric, window_end AS alert_ts, alert_type FROM idle_time_alerts
    UNION ALL
    SELECT vin, driver_id, CAST(efficiency_ratio AS VARCHAR) AS metric, window_end AS alert_ts, alert_type FROM fuel_efficiency_alerts
)
WITH (
    connector = 'kafka',
    topic = 'alerts.fleet.fuel',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

How accurate is the J1939 fuel rate signal compared to actual fuel card data? Accuracy varies by engine ECM and fuel rate PGN implementation. Typical variance is 2-8% between J1939 estimates and physical fuel card fill-ups. The fuel_reconciliation view exposes this variance continuously, alerting when it exceeds a configurable threshold.

Can I calculate cost per mile in real time? Yes. Extend truck_fuel_hourly with a fuel price source (from fuel_transactions or a price feed Kafka topic). Divide est_fuel_consumed_l * avg_price_per_liter by distance_km for a real-time cost-per-km figure.

What if the telematics device does not report J1939 fuel rate? Some lighter telematics devices report only GPS and ignition state. In that case, you can estimate fuel consumption using a load-speed-RPM model applied as a SQL expression. The accuracy is lower but still useful for fleet-level benchmarking.

How do I handle trucks that operate across multiple drivers in a single day? The truck_fuel_hourly view groups by (vin, driver_id), so driver changes within the day are naturally segmented. A driver handoff event on the HOS ELD stream can serve as an additional boundary marker.

Key Takeaways

• J1939 fuel rate telemetry processed through RisingWave TUMBLE windows provides hourly fuel consumption estimates per truck and driver without any application code.
• Idle fuel waste is computed as a subset of the main fuel aggregation, enabling targeted interventions when idle time exceeds 30% of operating hours.
• Fleet-wide percentile benchmarks maintained in a 24-hour TUMBLE window let you automatically flag trucks running 30% below fleet efficiency.
• The PostgreSQL-compatible interface allows fuel cost and efficiency data to flow directly into ERP or cost management systems without a custom API.

• RisingWave documentation: Tumble window functions
• J1939 Fuel Consumption PGN specification
• FMCSA SmartWay fuel efficiency program