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 preceding NORMALIZE example to create a 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') |