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

Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cxa1555383531762.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1208
lifecycle
previous
Product Category
Teradata Vantageā„¢

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