例: 複数の時系列集約関数が複数の結果を見つかる - Advanced SQL Engine - Teradata Database

この例では、複数の集約関数は複数の結果を生成することを示しています。タイムバケットごとに結果が1つだけ返されます。また、これが発生したことを示す警告メッセージも返されます。

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.

結果:

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