Multiple Results for Time Series Aggregate Functions | Teradata Vantage - Example: Multiple Results Found for One or More Time Series Aggregate Functions - 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ā„¢

The example shows that more than one aggregate function has more than one result. Only one result per timebucket is returned. A warning message is also returned indicating that this has occurred.

CREATE TABLE ts_group_by_time_tbl(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER )
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 56, 44);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:10:24.000000', 44, 55, 43);
INSERT INTO ts_group_by_time_tbl VALUES(TIMESTAMP '2014-01-06 10:10:24.333300', 44, 56, 44);
 
/*should give one result each and 1 warning*/

SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, MODE(TEMPERATURE), MODE(SALINITY), COUNT(*)
  FROM ts_group_by_time_tbl
  WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 10:00:00' AND TIMESTAMP '2014-01-06 10:10:00'
GROUP BY TIME (MINUTES(10) AND BUOYID);

 *** Query completed. One row found. 6 columns returned.
 *** Warning: 4001 Multiple results found for one or more Time Series aggregate functions in this query, but only one result was returned. To get all results, resubmit this query with these aggregates isolated.
 *** Total elapsed time was 1 second.

Result:

TIMECODE_RANGE  ('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00')
GROUP BY TIME(MINUTES(10))            1
                    BUOYID           44
         MODE(TEMPERATURE)           43
            MODE(SALINITY)           55
                  Count(*)            2