FILLUse the FILL() table function to fill in any gaps in date fields. By specifying the date fields to check, FILL() creates rows that contain the missing month, day, week, quarter, or year and null data. Use it with TIMESERIES() to forecast future results when there are gaps in input data.
FILL() takes the following syntax.
1SELECT <PROJECTION_LIST>|* FROM FILL(
2 INPUT=>(SELECT STATEMENT),
3 DATE_COLS=>ARRAY[<DATE_FIELDS>, 'DATE_COLUMN_TYPE'],
4 [PARTITION=>'FIELD_NAME']
5)| Name | Description |
|---|---|
INPUT | Required. A SELECT statement that includes date information and is the input to the FILL() function. |
DATE_COLS | Required.{DATE_FIELDS}—The array of date fields in which to check for gaps.The {DATE_COLUMN_TYPE} string accepts these values.
|
PARTITION | Optional. A field used to split query results into smaller partitions. The FILL() function resets when the field value changes. After each group of rows is completed for a given partition, FILL() runs on the next partition. |
This example uses FILL() to add missing quarter and year values to tourist data.
1SELECT "year", "quarter", tourists FROM FILL(
2 INPUT=>(SELECT EXTRACT(YEAR FROM "date") as "year", EXTRACT(QUARTER FROM "date") as "quarter",
3tourists FROM "TouristsData"),
4 DATE_COLS=>ARRAY['year', 'quarter', 'Y-Q']);The input SELECT statement returns the year, quarter, and number of tourists for each quarter. Based on the results from the first three years represented in the dataset, the only date data available is for the first quarter.
Another way to project all columns from the INPUT query is by using SELECT * FROM FILL(...).
Tip
These are the results from executing only the INPUT SELECT query.
| year | quarter | tourists |
|---|---|---|
| 2001 | 1 | 4127 |
| 2002 | 1 | 4173 |
| 2003 | 1 | 4621 |
FILL() specifies in the DATE_COLS array to check for gaps in year and quarter fields in the input data. To have a complete dataset of years and quarters, FILL() adds the 2nd, 3rd, and 4th quarters for each year and a null value for the number of tourists.
| year | quarter | tourists |
|---|---|---|
| 2001 | 1 | 4127 |
| 2001 | 2 | - |
| 2001 | 3 | - |
| 2001 | 4 | - |
| 2002 | 1 | 4173 |
| 2002 | 2 | - |
| 2002 | 3 | - |
| 2002 | 4 | - |
| 2003 | 1 | 4621 |
| 2003 | 2 | - |
| 2003 | 3 | - |
| 2003 | 4 | - |
j