次の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);