16.20 - 例: 複数の時系列集約関数が複数の結果を見つかる - Teradata Vantage NewSQL Engine

Teradata Vantage™ 時系列テーブルおよび操作

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1208-162K-JPN

この例では、複数の集約関数は複数の結果を生成することを示しています。タイムバケットごとに結果が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