16.20 - Example: GROUP BY TIME with a Derived Table - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
wne1515028094879.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

The following example again shows the rules and restrictions for inferring the timecode are per query, this time showing an inner and an outer query. The GROUP BY TIME query in the inner query does not need a USING TIMECODE clause because there is a single PTI table from which to infer the timecode column. However, the outer GROUP BY TIME query needs a USING TIMECODE clause because the derived table (min_temp) is not a PTI table, so no timecode column can be inferred:

SELECT AVG(min_temp)
FROM
(
  SELECT MIN(temperature)
  FROM OCEAN_BUOYS
  GROUP BY TIME(minutes(1) AND BUOYID) 
) AS "inner" (min_temp)
GROUP BY TIME(minutes(10)); 

*** Failure 4359 Time Series: GROUP BY TIME clause may not be used without a TIMECODE specification.

The following example uses the USING clause to specify the TD_TIMECODE derived from a derived table for the outer block:

SELECT AVG(min_temp)
FROM
(
   SELECT MIN(temperature), BEGIN($td_timecode_range) d1
   FROM OCEAN_BUOYS
   GROUP BY TIME(minutes(1))
) AS "inner" (min_temp, td_timecode)
GROUP BY TIME(minutes(10)) USING TIMECODE(td_timecode);

*** Failure 3706 Syntax error: 
Virtual columns $TD_TIMECODE_RANGE and $TD_GROUP_BY_TIME are not allowed in the GROUP BY TIME clause.