SQL How to Forecast with Linear Regression

SQL can be used to create even hundreds of thousands of linear models with one variable, quickly.

What

Solving the SLOPE and INTERCEPT parameters of a linear model equation with one variable is easy with some basic SQL. Here I give an example Code which shows too how to use the calculated SLOPE and INTERCEPT to make forecasts.

Code

--calculation of the linear model coefficients and saving them INTO linear_models table  
--the original data FROM actual_consumption_data_table has in addition a date_id dimension, separate values for every date  

SELECT   
[dimension_1],  
[dimension_2],  
   ((sum([actual_temperature]) * sum([average consumption])) - (count(1) * sum([actual_temperature] * [average consumption]))) /  
  NULLIF((power(sum([actual_temperature]), 2) - count(1) * sum(power([actual_temperature], 2))),0) as SLOPE,  

  ((sum([actual_temperature]) * sum([actual_temperature] * [average consumption])) -  
  (sum([average consumption]) * sum(power([actual_temperature], 2)))) /  
  NULLIF((power(sum([actual_temperature]), 2) - count(1) * sum(power([actual_temperature], 2))),0) as INTERCEPT   
INTO linear_models_table  
FROM actual_consumption_data_table  
GROUP BY  
dimension_1,  
dimension_2  


--using the coefficients to make for example a normalized average consumption curve:  
select  
a.[date_id],  
a.[dimension_1],  
a.[dimension_2],  
a.[normal_temperature]*b.[SLOPE] + b.[INTERCEPT]    
from actual_consumption_data_table a   
left join linear_models_table b on a.[dimension_1] = b.[dimension_1]  
and a.[dimension_2] = b.[dimension_2]