0

Pity Google BigQuery still doesn't have a function such as forecast() that we see in Spreadsheets-- don't look down on yet; given one has the statistical know-how, surprising amount of smoothing and seasonality can be added to forecasting on spreadsheets.

BigQuery allows you to determine Standard Deviation, correlation and intercept metrics. Using that, one can create the prediction model-- refer to this and this. But that uses Linear regression model; so we are not happy with the seasonality aspect. Question is, how can we construct Multiple regression model for prediction in BigQuery?

If Y = a1x1+a2x2+a3x3+c, do we (1) separately determine a1, a2 and a3 and finally join the queries? But what about the intercept? How do we calculate one for MR in Bigquery?

Any contribution will be greatly appreciated...

Martin Weitzmann
  • 4,430
  • 10
  • 19
AliveToLearn
  • 41
  • 8
  • Does BigQuery's new ML feature help you? https://cloud.google.com/bigquery/docs/bigqueryml-intro – Graham Polley Aug 11 '18 at 10:21
  • Bigquery ML uses Linear Regression in its training model. It may be efficient, but we are talking about MR here! Thanks! – AliveToLearn Aug 11 '18 at 12:06
  • Most likely they'll add more models in future, so worth keeping an eye on it. – Graham Polley Aug 11 '18 at 13:24
  • Hi @GrahamPolly, thanks for commenting. About Google BQML, I have a question about applying a training model built on a table ('date', 'sales') on future dates. So, in the 'ML.PREDICT' query part, if I replace the 'date' field with UNNEST(GENERATE_DATE_ARRAY(DATE('immediate future start date'),DATE('30-days-on-stop-date'))) --- will it work? – AliveToLearn Aug 12 '18 at 06:03
  • Did you try it? ;-) – Graham Polley Aug 12 '18 at 10:28
  • Hi @GrahamPolly, it sort of works. But just extending the date may not be a correct method, considering the forecasting has only learnt past seasonalities and we have no way to put in the future influencers. Is there a way to input future influencers? – AliveToLearn Oct 23 '18 at 07:24

0 Answers0