17.10 - Example: FILL with Previous Timebucket Value - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1208-171K
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