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') |