Linear Regression & Trend Forecasting in SQL

PostgreSQL has built-in regression aggregates (REGR_SLOPE, REGR_INTERCEPT, CORR, REGR_R2) that fit a linear model entirely in SQL — no Python, no external tools. We use these to forecast energy demand and detect deviating trends.

-- Fit a linear trend to each meter and forecast the next 7 days
WITH daily_kwh AS (
    SELECT
        meter_id,
        building,
        read_at::DATE                                           AS day,
        SUM(kwh)                                               AS daily_kwh,
        EXTRACT(EPOCH FROM read_at::DATE)                      AS epoch_day
    FROM ts_energy_readings
    GROUP BY meter_id, building, read_at::DATE
),
regression AS (
    SELECT
        meter_id,
        building,
        REGR_SLOPE(daily_kwh, epoch_day)                       AS slope,
        REGR_INTERCEPT(daily_kwh, epoch_day)                   AS intercept,
        REGR_R2(daily_kwh, epoch_day)                          AS r_squared,
        CORR(daily_kwh, epoch_day)                             AS correlation,
        COUNT(*)                                               AS data_points,
        MAX(epoch_day)                                         AS last_epoch,
        MAX(day)                                               AS last_day,
        AVG(daily_kwh)                                         AS avg_kwh
    FROM daily_kwh
    GROUP BY meter_id, building
),
forecasts AS (
    SELECT
        r.meter_id,
        r.building,
        r.slope,
        r.intercept,
        ROUND(r.r_squared::numeric, 3)                         AS r_squared,
        ROUND(r.avg_kwh::numeric, 2)                           AS avg_daily_kwh,
        r.last_day,
        gs.future_day,
        ROUND((r.slope * EXTRACT(EPOCH FROM gs.future_day)
               + r.intercept)::numeric, 2)                     AS forecast_kwh,
        CASE
            WHEN ABS(r.slope) < 0.0001                        THEN 'Flat'
            WHEN r.slope > 0                                   THEN 'Increasing'
            ELSE                                                    'Decreasing'
        END                                                    AS trend_direction
    FROM regression r
    CROSS JOIN LATERAL generate_series(
        (r.last_day + 1)::TIMESTAMP,
        (r.last_day + 7)::TIMESTAMP,
        INTERVAL '1 day'
    ) AS gs(future_day)
)
SELECT *
FROM forecasts
ORDER BY meter_id, future_day;