17.00 - Example: FILL with Next Timebucket Value - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

For timebuckets with missing values, use the FILL(NEXT) clause to replace the missing values with the next non-missing timebucket value. If every succeeding timebucket is also missing values, the timebucket is set to NULL.

The example shows the results of the query when the FILL clause is used with NEXT:

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE)
FROM OCEAN_BUOYS
WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 09:45:00' AND TIMESTAMP '2014-01-06 11:45:00'
AND BUOYID=44
GROUP BY TIME (MINUTES(15) AND BUOYID) FILL(NEXT)
ORDER BY 2,3;

Result: All timebuckets are included as part of the result set. The missing value for timebucket #1 is set to the value of its immediate non-missing successor (timebucket #2, in this case) resulting in a value of 49. The missing value for timebucket #3 is set to the value of its immediate non-missing successor (timebucket #4, in this case) resulting in a value of 43. The missing values for timebuckets #6, #7 and #8 are all set to null because there is no succeeding timebucket from which to infer the value.

TIMECODE_RANGE GROUP BY TIME(MINUTES(15)) BUOYID AVG(TEMPERATURE)
('2014-01-06 09:45:00.000000+00:00', '2014-01-06 10:00:00.000000+00:00') 1 44 49
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:15:00.000000+00:00') 2 44 49
('2014-01-06 10:15:00.000000+00:00', '2014-01-06 10:30:00.000000+00:00') 3 44 43
('2014-01-06 10:30:00.000000+00:00', '2014-01-06 10:45:00.000000+00:00') 4 44 43
('2014-01-06 10:45:00.000000+00:00', '2014-01-06 11:00:00.000000+00:00') 5 44 43
('2014-01-06 11:00:00.000000+00:00', '2014-01-06 11:15:00.000000+00:00') 6 44 ?
('2014-01-06 11:15:00.000000+00:00', '2014-01-06 11:30:00.000000+00:00') 7 44 ?
('2014-01-06 11:30:00.000000+00:00', '2014-01-06 11:45:00.000000+00:00') 8 44 ?