16.20 - Example: FILL with Previous Timebucket Value - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)

Use the FILL(PREVIOUS) or FILL(PREV) clause to replace missing values with the previous timebucket value for timebuckets with missing values.

FILL(PREVIOUS) and FILL(PREV) are identical and produce the same results.

The example shows the results of the query when the FILL clause is used with PREVIOUS/PREV:

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(PREVIOUS)
ORDER BY 2,3;

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(PREV)
ORDER BY 2,3;

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

The results of both queries are identical, so only one result set is shown.
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 ?
('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 49
('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 43
('2014-01-06 11:15:00.000000+00:00', '2014-01-06 11:30:00.000000+00:00') 7 44 43
('2014-01-06 11:30:00.000000+00:00', '2014-01-06 11:45:00.000000+00:00') 8 44 43