# How can I get a linear forecast in BigQuery?

BigQuery has no native support for linear forecasting outside of machine learning, but it does support user-defined functions, and these can accept arrays as inputs. However, (as of 2023/10 anyway), these can only be expressions, not multi-line statements.

Is it possible to calculate a linear regression within these limitations?

Yes you can, though the limitations of BigQuery make it a bit ugly on the back end. Calling the method is simple enough:

``````SELECT util.linear_forecast([ 20.0, 28.0, 31.0, 38.0, 40.0 ], [ 6.0, 7.0, 9.0, 15.0, 21.0 ], 30.0);
``````

Result: 10.6073.

The `linear_forecast` function calls two helpers, to keep the code somewhat readable. Start with this one:

``````CREATE OR REPLACE FUNCTION util.linear_slope(xValues ARRAY<FLOAT64>, yValues ARRAY<FLOAT64>)
RETURNS FLOAT64 AS
(
(
SELECT
SUM
(
-- For each entry, take the average value of x less the value of x; multiply that by the difference between the average value of y less y
((SELECT AVG(x) FROM UNNEST(xValues) AS x) - x)
* ((SELECT AVG(y) FROM UNNEST(yValues) AS y) - y)
)
FROM
UNNEST(xValues) AS x WITH OFFSET x_seq
INNER JOIN UNNEST(yValues) AS y WITH OFFSET y_seq ON x_seq = y_seq
)
/
(
SELECT
SUM
(
-- For each entry, take the average of x less the value of x; square it
POWER((SELECT AVG(x) FROM UNNEST(xValues) AS x) - x, 2.0)
)
FROM
UNNEST(xValues) AS x
)
);
``````

...then this one:

``````CREATE OR REPLACE FUNCTION util.linear_intercept(xValues ARRAY<FLOAT64>, yValues ARRAY<FLOAT64>)
RETURNS FLOAT64 AS
(
(SELECT AVG(y) FROM UNNEST(yValues) AS y)
- (SELECT AVG(x) FROM UNNEST(xValues) AS x) * util.linear_slope(xValues, yValues)
);
``````

...and finally the wrapper function:

``````CREATE OR REPLACE FUNCTION util.linear_forecast(xValues ARRAY<FLOAT64>, yValues ARRAY<FLOAT64>, forecastX FLOAT64)
RETURNS FLOAT64 AS
(
util.linear_intercept(xValues, yValues) + util.linear_slope(xValues, yValues) * forecastX
);
``````

Watch the parentheses; they are all needed.