NORMALIZE, EXPAND ON, PTI Tables | Teradata Vantage - 17.00 - NORMALIZE, EXPAND ON, and PTI Tables - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K

NORMALIZE

You can use a NORMALIZE clause in the CREATE TABLE statement for a PTI table just as you would use it for a non-PTI table. The NORMALIZE clause coalesces rows of the table that have values of a Period data type column that meet or overlap, if all other values in the rows are equivalent to each other. For more information about the NORMALIZE clause, see Teradata Vantageā„¢ - SQL Data Definition Language Syntax and Examples, B035-1144.

Example: A Normalized PTI table

This example coalesces five inserted rows into two rows.

CREATE TABLE flight_sensors ( 
      FlightID   INTEGER,
      SensorID   INTEGER,
      SensorData INTEGER,
      duration PERIOD(TIMESTAMP(6)),
NORMALIZE ALL BUT(SensorData) ON duration ON MEETS OR OVERLAPS)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-10-15', COLUMNS(flightID, SensorID), NONSEQUENCED);

INSERT INTO flight_sensors (TIMESTAMP '2017-01-06 08:10:00.000000', 67,4,1,
  PERIOD (timestamp '2017-01-06 08:10:00.000000',
          timestamp '2017-01-06 08:10:00.000010'));

INSERT INTO flight_sensors (TIMESTAMP '2017-01-06 08:10:00.000000', 67,4, 1,
  PERIOD (timestamp '2017-01-06 08:10:00.000000',
          timestamp '2017-01-06 08:10:00.000020'));

INSERT INTO flight_sensors (TIMESTAMP '2017-01-06 08:10:02.000000', 67,5, 99,
  PERIOD (timestamp '2017-01-06 08:10:02.000000',
          timestamp '2017-01-06 08:10:02.000030'));

INSERT INTO flight_sensors (TIMESTAMP '2017-01-06 08:10:02.000000', 67,5, 99,
  PERIOD (timestamp '2017-01-06 08:10:02.000000',
          timestamp '2017-01-06 08:10:02.000040'));

INSERT INTO flight_sensors (TIMESTAMP '2017-01-06 08:10:02.000000', 67,5, 99,
  PERIOD (timestamp '2017-01-06 08:10:02.000000',
          timestamp '2017-01-06 08:10:02.000050'));


SELECT * FROM flight_sensors;
TD_TIMECODE flightID SensorID SensorData duration
2017-01-06 08:10:00.000000 67 4 1 ('2017-01-06 08:10:00.000000','2017-01-06 08:10:00.000020')
2017-01-06 08:10:02.000000 67 5 99 ('2017-01-06 08:10:02.000000','2017-01-06 08:10:02.000050')

EXPAND ON

You can use the EXPAND ON clause of a SELECT statement against PTI tables to create multiple rows in the result set for every table row returned, according to the specification in the EXPAND ON clause.

Example: Using the SELECT...EXPAND ON Clause with a Normalized PTI Table

This example uses the table from the NORMALIZE example above to create a new PTI table that has two rows.

The SELECT...EXPAND ON query expands two rows of the flight_sensors table into several rows that fill the period associated with each original row in 0.00001 increments.

SELECT td_timecode, flightID, SensorData, tsp 
FROM flight_sensors
 EXPAND ON duration AS tsp BY INTERVAL '0.00001' SECOND
 FOR PERIOD (timestamp '2017-01-06 08:10:00.000000',
             timestamp '2017-01-06 08:10:02.000050');
TD_TIMECODE FlightID SensorData tsp
2017-01-06 08:10:00.000000 67 1 ('2017-01-06 08:10:00.000000', '2017-01-06 08:10:00.000010')
2017-01-06 08:10:00.000000 67 1 ('2017-01-06 08:10:00.000010', '2017-01-06 08:10:00.000020')
2017-01-06 08:10:02.000000 67 99 ('2017-01-06 08:10:02.000000', '2017-01-06 08:10:02.000010')
2017-01-06 08:10:02.000000 67 99 ('2017-01-06 08:10:02.000010', '2017-01-06 08:10:02.000020')
2017-01-06 08:10:02.000000 67 99 ('2017-01-06 08:10:02.000020', '2017-01-06 08:10:02.000030')
2017-01-06 08:10:02.000000 67 99 ('2017-01-06 08:10:02.000030', '2017-01-06 08:10:02.000040')
2017-01-06 08:10:02.000000 67 99 ('2017-01-06 08:10:02.000040', '2017-01-06 08:10:02.000050')