Example: GROUP BY TIME with a Derived Table - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
zzg1600277315070.ditamap
dita:ditavalPath
zzg1600277315070.ditaval
dita:id
B035-1208
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.