時系列集約のテーブルとデータ定義の例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - 時系列テーブルと操作の概要

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
2021年7月
Language
日本語
Last Update
2021-09-23
dita:mapPath
ja-JP/zzg1600277315070.ditamap
dita:ditavalPath
ja-JP/wrg1590696035526.ditaval
dita:id
B035-1208
Product Category
Software
Teradata Vantage

次のSQLは、時系列の例で使用するデータを含むサンプル テーブルを作成します。

/*PTI Table*/
CREATE TABLE OCEAN_BUOYS(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER ) 

PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);

INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);

/* Sequenced PTI table */
CREATE TABLE OCEAN_BUOYS_SEQ(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER, DATES DATE)

PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), sequenced);

INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:32:12.122200',1, 44, 55, 43, '2014-01-01');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:32:12.122200',1, 22, 25, 23, '2014-01-01');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:52:00.000009',2, 44, 55, 43, '2014-02-02');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:12:00.000000',3, 44, 55, 43, '2014-03-03');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:24.000000',4, 44, 55, 43, '2014-04-04');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:24.333300',5, 44, 55, 43, '2014-05-05');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:25.122200',6, 44, 55, 43, '2014-06-06');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:00:26.122200',7, 44, 55, 43, '2014-07-07');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:01:25.122200',8, 44, 55, 53, '2014-08-08');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:02:25.122200',9, 44, 55, 53, '2014-09-09');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:03:25.122200',10, 44, 55, 53, '2014-10-10');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:01:25.122200',11, 1, 55, 70,  '2014-11-11');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:02:25.122200',12, 1, 55, 71,  '2014-12-12');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:03:25.122200',13, 1, 55, 72,  '2015-01-13');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 21:01:25.122200',14, 2, 55, 80,  '2015-02-14');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 21:02:25.122200',15, 2, 55, 81,  '2015-03-15');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 21:03:25.122200',16, 2, 55, 82,  '2015-04-16');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:09:59.999999',17, 0, 55, 99,  '2015-05-17');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:08:59.999999',18, 0, 55, ,    '2015-06-18');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:10:00.000000',19, 0, 55, 10,  '2015-07-19');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:01:25.122200',20, 44, 55, 54, '2015-08-20');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:02:25.122200',21, 44, 55, 55, '2015-09-21');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 10:03:25.122200',22, 44, 55, 56, '2015-10-22');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:01:25.122200',23, 1, 55, 77,  '2015-11-23');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:02:25.122200',24, 1, 55, 78,  '2015-12-24');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 09:03:25.122200',25, 1, 55, 79,  '2016-01-25');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:00:00.000000',26, 0, 55, 10,  '2016-02-26');
INSERT INTO OCEAN_BUOYS_SEQ VALUES(TIMESTAMP '2014-01-06 08:10:00.000000',27, 0, 55, 100, '2016-03-27');

/*Non-PTI Table*/

CREATE TABLE OCEAN_BUOYS_NONPTI(TIMECODE TIMESTAMP(6), BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER)

PRIMARY INDEX (BUOYID);

INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);