TIMESERIES
Use the TIMESERIES() table function to predict future values based on existing ones tracked over time. Optionally choose a prediction model, confidence interval, and seasonality among other parameters.
TIMESERIES() takes the following syntax.
| Name | Description |
|---|---|
INPUT | Required. A SELECT statement that includes date information and is the input to the TIMESERIES() function. |
FIELDS | Required. The field values in the input query from which to predict future values. Predicted values are automatically named {predicted_fieldName}. |
LENGTH | Required. The number of points to predict. |
DATE_COLS | Optional.{DATE_FIELDS}—The array of date fields from which to base future predictions.The {DATE_COLUMN_TYPE} string accepts these values.
|
ORDER | Optional, unless no DATE_COLS are specified. |
PARTITION | Optional. A field used to split query results into smaller partitions. The TIMESERIES() function resets when the field value changes. After each group of rows is completed for a given partition, TIMESERIES() runs on the next partition. |
PREDICTION_INTERVAL | Optional. The confidence interval to display for each forecasted data point. |
IGNORE_LAST | Optional. If set to TRUE, excludes the last time period from timeseries calculations. |
MODEL | Optional. Choose from these prediction models:
|
SEASONALITY | Optional. Use with DATE_COLS to specify the seasonality for the prediction. |
This example predicts the annual number of tourists for two years based on available data for previous years.
| date (Year) | predicted_sum_tourists | Sum of tourists |
|---|---|---|
| 2001 | 13008 | 13140 |
| 2002 | 13964 | 13543 |
| 2003 | 14934 | 15502 |
| 2004 | 15894 | 15894 |
| 2005 | 16855 | 16784 |
| 2006 | 17816 | 17713 |
| 2007 | 18777 | 18719 |
| 2008 | 19738 | - |
| 2009 | 20700 | - |
| 2010 | 21662 | - |
To fill in the gaps, the FILL() function takes the input of the annual tourist sums and fills in the gaps (if any) in date fields. TIMESERIES() takes the input from the FILL() function and predicts the number of tourists expected for the specified length. Here, LENGTH is set to 3, meaning three years. IGNORE_LAST is set to TRUE to exclude the last row for 2008 from generating timeseries predictions. TIMESERIES() predicts values from 2008–2010.
| date (Year) | predicted_sum_tourists | Sum of tourists |
|---|---|---|
| 2001 | 13008 | 13140 |
| 2002 | 13964 | 13543 |
| 2003 | 14934 | 15502 |
| 2004 | 15894 | 15894 |
| 2005 | 16855 | 16784 |
| 2006 | 17816 | 17713 |
| 2007 | 18777 | 18719 |
| 2008 | 19738 | - |
| 2009 | 20700 | - |
| 2010 | 21662 | - |