16.20 - Examples: GROUP BY TIME and TD_SEQNO Column - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ Time Series Tables and Operations

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1208-162K
Language
English (United States)

The USING TIMECODE clause allows you to specify an optional sequence number column. Results are further grouped and ordered by the sequence number column if values in the timecode column are the same. The default system generated sequence number column, TD_SEQNO, is inferred if there is a single sequenced PTI table found. If there are multiple candidates for the TD_SEQNO column to use, then no sequence number is inferred by Teradata Database.

You can use the USING TIMECODE clause to override this behavior by specifying a timecode but no sequence number.

The example infers TD_SEQNO as the sequence number column:

select avg(temperature)
from ocean_buoys_seq
group by time(minutes(10));

The next example shows that TD_SEQNO is not inferred as the sequence number column because the USING TIMECODE clause overrides the inference. If a sequence number is not specified in the USING TIMECODE clause that implies a sequence number should not be used for the purpose of grouping:

select avg(temperature)
from ocean_buoys_seq
group by time(minutes(10))
using timecode(td_timecode);

The example shows that TD_SEQNO is not inferred as the sequence number column because there are multiple sequenced PTI tables:

select avg(b.temperature)
from ocean_buoys_seq a , ocean_buoys_seq b
group by time(minutes(100))
using timecode(a.td_timecode);

If your data is sequenced in a non-PTI table, then you can specify a sequence number to use from that table as well. No columns are inferred by the USING TIMECODE clause for non-PTI sources.

The example shows how to specify the sequence number:

select avg(temperature)
from non_pti_tbl_seq
group by time(minutes(10))
using timecode(timecode, sequenceno);