例 - 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

例: OCEAN_BUOYS_DELTA_Tテーブルの作成

/*PTI Table*/
CREATE TABLE OCEAN_BUOYS_DELTA_T(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER ) 
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:01.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);

/*Non-PTI Table*/
CREATE TABLE OCEAN_BUOYS_NONPTI_DELTA_T(TIMECODE TIMESTAMP(6), BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER)
PRIMARY INDEX (BUOYID);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:01.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);

例: 観測された最大温度と最小温度の検索

この例では、午前8:00から午前10:30の間の10分ごとに各ブイで観測された最大温度と最小温度の間の時間を測定します。

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, t1.BUOYID, DELTA_T((WHERE TEMPERATURE=MIN_TEMP),(WHERE TEMPERATURE=MAX_TEMP))
FROM (
      SELECT MIN(TEMPERATURE) AS MIN_TEMP, MAX(TEMPERATURE) AS MAX_TEMP, BUOYID
      FROM OCEAN_BUOYS_DELTA_T
      WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
      GROUP BY TIME(MINUTES(30) AND BUOYID)
) AS t1, OCEAN_BUOYS_DELTA_T
WHERE t1.buoyid=OCEAN_BUOYS_DELTA_T.buoyid
GROUP BY TIME(DAYS(1) and t1.buoyid)
USING TIMECODE(OCEAN_BUOYS_DELTA_T.td_timecode)
order by 2, 3;

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, t1.BUOYID, DELTA_T((WHERE TEMPERATURE=MIN_TEMP),(WHERE TEMPERATURE=MAX_TEMP))
FROM (
      SELECT MIN(TEMPERATURE) AS MIN_TEMP, MAX(TEMPERATURE) AS MAX_TEMP, BUOYID
      FROM OCEAN_BUOYS_NONPTI_DELTA_T
      WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
      GROUP BY TIME(MINUTES(30) AND BUOYID)
      USING TIMECODE(TIMECODE)
) AS t1, OCEAN_BUOYS_NONPTI_DELTA_T
WHERE t1.buoyid=OCEAN_BUOYS_NONPTI_DELTA_T.buoyid
GROUP BY TIME(DAYS(1) and t1.buoyid)
USING TIMECODE(OCEAN_BUOYS_NONPTI_DELTA_T.timecode)
order by 2, 3;

両方のテーブルの結果は次のとおりです。

TIMECODE_RANGE GROUP BY TIME (MINUTES(10)) BUOYID Delta_T(TD_ TIMECODE)
('2014-01-06 00:00:00.000000+00:00', '2014-01-07 00:00:00.000000+00:00') 737 0 ('2014-01-06 08:10:00.000000', '2014-01-06 08:10:01.000000')
('2014-01-06 00:00:00.000000+00:00', '2014-01-07 00:00:00.000000+00:00') 737 1 ('2014-01-06 09:01:25.122200', '2014-01-06 09:03:25.122200')
('2014-01-06 00:00:00.000000+00:00', '2014-01-07 00:00:00.000000+00:00') 737 44 ('2014-01-06 10:00:26.122200', '2014-01-06 10:03:25.122200')

ブイ44、タイムバケット13。結果は開始条件を満たす最終測定結果に基づいています。ブイ44、タイムバケット14については、開始条件および終了条件を満たす項目がないため、返される結果行はありません。

例: 経過時間を追跡するためのマルチセット テーブルの作成

この例では、配送サービスによって送信された小包に関する情報を含むテーブルを作成します。

/*PTI Table*/
CREATE MULTISET TABLE package_tracking_pti(ParcelNumber INTEGER, Status VARCHAR(512)) 
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(ParcelNumber), nonsequenced);
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:00:00', 55, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:05:00', 59, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:10:00', 55, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:20:00', 60, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:00:00', 55, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:45:00', 60, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:10:00', 55, 
'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:30:00', 55, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 17:00:00', 55, 
'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:00:00', 75, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:05:00', 79, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:10:00', 75, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:20:00', 80, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:00:00', 75, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:45:00', 80, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:10:00', 75, 
'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:30:00', 75, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 17:00:00', 75, 
'delivered to customer');

/*Non-PTI Table*/
CREATE MULTISET TABLE package_tracking(ParcelNumber INTEGER, CLOCK_TIME TIMESTAMP, Status VARCHAR(512));
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 08:00:00', 
'picked up from customer');
INSERT INTO package_tracking(59, TIMESTAMP'2016-10-15 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(60, TIMESTAMP'2016-10-15 09:20:00', 
'picked up from customer');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(60, TIMESTAMP'2016-10-15 10:45:00', 
'arrived at receiving station');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 16:10:00', 
'arrived at destination station');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 17:00:00', 
'delivered to customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 08:00:00', 
'picked up from customer');
INSERT INTO package_tracking(79, TIMESTAMP'2016-10-15 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(80, TIMESTAMP'2016-10-15 09:20:00', 
'picked up from customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(80, TIMESTAMP'2016-10-15 10:45:00', 
'arrived at receiving station');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 16:10:00', 
'arrived at destination station');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 17:00:00', 
'delivered to customer');

例: 品目の出荷と受領の間の経過時間の検索

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
             (WHERE Status LIKE 'picked%up%customer'),  
             (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking_pti
GROUP BY TIME(* AND ParcelNumber);

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
             (WHERE Status LIKE 'picked%up%customer'),  
             (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking
GROUP BY TIME(* AND ParcelNumber) USING TIMECODE(CLOCK_TIME);

結果は、両方のテーブルで同じです。

TIMECODE_RANGE ParcelNumber Delta_T(TD_ TIMECODE)
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00') 55 ('2016-10-15 08:00:00.000000', '2016-10-15 17:00:00.000000')
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00') 75 ('2016-10-15 08:00:00.000000', '2016-10-15 17:00:00.000000')

例: 誤配送されたパッケージの追跡

以下は、パッケージの誤配送に関する例です。パッケージは、2回集荷および配送された結果、出力行が2つになりました。
DELETE package_tracking_pti;
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:00:00', 65, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:05:00', 69, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:10:00', 65, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:20:00', 70, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:00:00', 65, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:45:00', 70, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:10:00', 65, 
'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:30:00', 65, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 17:00:00', 65, 
'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 17:05:00', 65, 
'notification of incorrect delivery');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 08:05:00', 65, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 09:10:00', 65, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 10:00:00', 65, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 16:30:00', 65, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 17:00:00', 65, 
'delivered to customer');


DELETE package_tracking;
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 08:00:00', 
'picked up from customer');
INSERT INTO package_tracking(69, TIMESTAMP'2016-10-16 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(70, TIMESTAMP'2016-10-16 09:20:00', 
'picked up from customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(70, TIMESTAMP'2016-10-16 10:45:00', 
'arrived at receiving station');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 16:10:00', 
'arrived at destination station');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 17:00:00', 
'delivered to customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 17:05:00', 
'notification of incorrect delivery');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 17:00:00', 
'delivered to customer');

テーブルの結果を確認する手順:

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
              (WHERE Status LIKE 'picked%up%customer'),  
              (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking_pti
GROUP BY TIME(* AND ParcelNumber);

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
              (WHERE Status LIKE 'picked%up%customer'),  
              (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking
GROUP BY TIME(* AND ParcelNumber) USING TIMECODE(CLOCK_TIME);

結果は、両方のテーブルで同じです。

TIMECODE_RANGE ParcelNumber DELTA_T(TD_ TIMECODE)
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00') 65 ('2016-10-16 08:00:00.000000', '2016-10-16 17:00:00.000000')
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00') 65 ('2016-10-17 08:05:00.000000', '2016-10-17 17:00:00.000000')

例: 出荷日数と配送エラーの割合の追跡

この例では、マーケティング チームは、指定した日数以内にパッケージの配送を保証するキャンペーンを開始します。パッケージがその日付範囲内に配送されない場合、会社は配送費用を負担します。

配送料を最小限にするために、会社は最近の配送を分析して、配達日に遅れたパッケージの数を判別します。分析では、DELTA_TとHAVING句によるフィルタ処理を使用します。
/*PTI Table*/
DELETE package_tracking_pti;
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:00:00', 65, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:05:00', 69, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:10:00', 65, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:20:00', 70, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:00:00', 65, 'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:45:00', 70, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:10:00', 65, 'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:30:00', 65, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 17:00:00', 65, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 10:00:00', 70, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 10:45:00', 69, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 11:45:00', 69, 'in transit to customer'); 
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 14:00:00', 69, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 15:00:00', 70, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 08:00:00', 71, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-18 09:10:00', 71, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-20 16:30:00', 71, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-20 17:00:00', 71, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-21 08:00:00', 72, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-25 09:10:00', 72, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-27 16:30:00', 72, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-27 17:00:00', 72, 'delivered to customer');
配送に2日以上かかったパッケージの数を確認するには、次の例を使用します。
/*PTI Table*/
SELECT COUNT(*) AS FailedDeliveries FROM
(
    SELECT DELTA_T((WHERE Status LIKE 'picked%up%customer'),
                   (WHERE Status LIKE 'delivered%customer')) AS deltaT
    FROM package_tracking_pti
    GROUP BY TIME(* AND ParcelNumber)
    HAVING INTERVAL(deltaT) DAY TO SECOND > INTERVAL '2 00:00:00' DAY TO SECOND
) as t1;
結果:
FailedDeliveries
2
配送に5日以上かかったパッケージの数を確認するには、次の例を使用します。
/*PTI Table*/
SELECT COUNT(*) AS FailedDeliveries FROM
(
    SELECT DELTA_T((WHERE Status LIKE 'picked%up%customer'),
                   (WHERE Status LIKE 'delivered%customer')) AS deltaT
    FROM package_tracking_pti
    GROUP BY TIME(* AND ParcelNumber)
    HAVING INTERVAL(deltaT) DAY TO SECOND > INTERVAL '5 00:00:00' DAY TO SECOND
) as t1;
結果:
FailedDeliveries
1
配送に5日以上かかるパッケージの割合を確認するには、次のようにします。
/*PTI Table*/
SELECT CAST(SUM(CASE
                WHEN INTERVAL(deltaT) DAY TO SECOND > INTERVAL '5 00:00:00' DAY TO SECOND
                THEN 1
                ELSE 0
                END) AS REAL) /
       CAST(COUNT(*) AS REAL) * 100 (FORMAT 'zz.zz%') AS "%FailedDeliveries"
FROM
(
    SELECT DELTA_T((WHERE Status LIKE 'picked%up%customer'),
                   (WHERE Status LIKE 'delivered%customer')) AS deltaT
    FROM package_tracking_pti
    GROUP BY TIME(* AND ParcelNumber)
) as t1;
結果:
% FailedDeliveries
20.00%