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

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