0

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?

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.