Auto catagory generation for univariate forecasting

[xzdandy]

Search before asking

• I have searched the EvaDB issues and found no similar feature requests.

Description

In the current forecast usecase, we need to manually specify the catagory column and it can be only one column.

CREATE FUNCTION IF NOT EXISTS HomeSaleForecast FROM
(
      SELECT propertytype, datesold, price
      FROM postgres_data.home_sales
      WHERE bedrooms = 3 AND postcode = 2607
 )
TYPE Forecasting
PREDICT 'price'
TIME 'datesold'
ID 'propertytype'
FREQUENCY 'W'

For instance, for the above query, the catagory column is propertytype. As a result, we need to pick a specific bedrooms and postcode value.

CREATE FUNCTION IF NOT EXISTS HomeSaleForecast FROM
(
      SELECT postcode, price, bedrooms, datesold, propertytype
      FROM postgres_data.home_sales
)
TYPE Forecasting
PREDICT 'price'
TIME 'datesold'
FREQUENCY 'W'

What we want is to auto combining the rest three columns (e.g., postcode, bedrooms, and propertytype) into the catagory column and forecast every combination independently.

Use case

Supporting auto category generation for univariate forecasting allows us to forecast for all combinations at once.
In the above usecase, for example, we can then find the post code, bedrooms and propertytype, and month that have the lowest price.

Are you willing to submit a PR?

• Yes I'd like to help by submitting a PR!

[xzdandy]

As offline discussed, we do not want to explicitly combine columns without the user's knowledge, which may lead to confusion.

So instead, we propose to support the ID parameters accepting a list of columns.

CREATE FUNCTION IF NOT EXISTS HomeSaleForecast FROM
(
      SELECT postcode, price, bedrooms, datesold, propertytype
      FROM postgres_data.home_sales
)
TYPE Forecasting
PREDICT 'price'
TIME 'datesold'
ID "postcode", "bedrooms", "propertytype"
FREQUENCY 'W

The above query will combine the postcode, bedrooms, and propertytype column into the catagory column and forecast every combination independently.

On the other hand, for the below query, we will throw out a warning message Column bedrooms, propertytype are ignored.

CREATE FUNCTION IF NOT EXISTS HomeSaleForecast FROM
(
      SELECT postcode, price, bedrooms, datesold, propertytype
      FROM postgres_data.home_sales
)
TYPE Forecasting
PREDICT 'price'
TIME 'datesold'
ID "postcode"
FREQUENCY 'W