例: DESCRIBEを使用した温度の検出
/*PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, DESCRIBE(TEMPERATURE) FROM OCEAN_BUOYS WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) ORDER BY 2,3,4; /*Non-PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, DESCRIBE(TEMPERATURE) FROM OCEAN_BUOYS_NONPTI WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) USING TIMECODE(TIMECODE) ORDER BY 2,3,4;
結果は、両方のテーブルで同じです。
TIMECODE_RANGE | GROUP BY TIME (MINUTES (10)) | BUOYID | Max (TEMPER-ATURE) | Min (TEMPER-ATURE) | Average (TEMPER-ATURE) | Stddev_Samp (TEMPER-ATURE) |
---|---|---|---|---|---|---|
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') | 1 | 0 | 99 | 10 | 55 | 62.9325 |
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') | 2 | 0 | 100 | 10 | 54 | 63.6396 |
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') | 7 | 1 | 79 | 70 | 74 | 3.9479 |
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') | 13 | 44 | 56 | 43 | 43 | 5.7581 |
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') | 14 | 44 | 43 | 43 | 50 | ? |
例: VERBOSEオプションの使用
/*PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, DESCRIBE VERBOSE(TEMPERATURE) FROM OCEAN_BUOYS WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) ORDER BY 2,3,4; /*Non-PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, DESCRIBE VERBOSE(TEMPERATURE) FROM OCEAN_BUOYS_NONPTI WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) USING TIMECODE(TIMECODE) ORDER BY 2,3,4;
結果は、両方のテーブルで同じです。
TIMECODE_ RANGE | GROUP BY TIME (MINUTES (10)) | BUOY- ID | Max (TEM- PERA- TURE) | Min (TEM- PERA- TURE) | Average (TEM- PERA- TURE) | Stddev_ Samp (TEMPER- ATURE) | Median (TEM- PERA- TURE) | Mode (TEM-PERA- TURE) | Percentile (TEMPER- ATURE,25) | Percentile (TEMPER- ATURE,5) | Percentile (TEMPER- ATURE,75) |
---|---|---|---|---|---|---|---|---|---|---|---|
('2014-01-06 10:10:00.000000+ 00:00', '2014-01-06 10:20:00.000000+ 00:00') | 14 | 44 | 43 | 43 | 50 | ? | 43 | 43 | 43 | 43 | 43 |
('2014-01-06 10:00:00.000000+ 00:00', '2014-01-06 10:10:00.000000+ 00:00') | 13 | 44 | 56 | 43 | 43 | 5.7581 | 53 | 43 | 43 | 53 | 53.75 |
('2014-01-06 09:00:00.000000+ 00:00', '2014-01-06 09:10:00.000000+ 00:00') | 7 | 1 | 79 | 70 | 74 | 3.9479 | 74.5 | 70 | 71.25 | 74.5 | 77.75 |
('2014-01-06 09:00:00.000000+ 00:00', '2014-01-06 09:10:00.000000+ 00:00') | 7 | 1 | 79 | 70 | 74 | 3.9479 | 74.5 | 71 | 71.25 | 74.5 | 77.75 |
('2014-01-06 09:00:00.000000+ 00:00', '2014-01-06 09:10:00.000000+ 00:00') | 7 | 1 | 79 | 70 | 74 | 3.9479 | 74.5 | 72 | 71.25 | 74.5 | 77.75 |
('2014-01-06 09:00:00.000000+ 00:00', '2014-01-06 09:10:00.000000+ 00:00') | 7 | 1 | 79 | 70 | 74 | 3.9479 | 74.5 | 77 | 71.25 | 74.5 | 77.75 |
('2014-01-06 09:00:00.000000+ 00:00', '2014-01-06 09:10:00.000000+ 00:00') | 7 | 1 | 79 | 70 | 74 | 3.9479 | 74.5 | 78 | 71.25 | 74.5 | 77.75 |
('2014-01-06 09:00:00.000000+ 00:00', '2014-01-06 09:10:00.000000+ 00:00') | 7 | 1 | 79 | 70 | 74 | 3.9479 | 74.5 | 79 | 71.25 | 74.5 | 77.75 |
('2014-01-06 08:10:00.000000+ 00:00', '2014-01-06 08:20:00.000000+ 00:00') | 2 | 0 | 100 | 10 | 54 | 63.6396 | 55 | 10 | 32.5 | 55 | 77.5 |
('2014-01-06 08:10:00.000000+ 00:00', '2014-01-06 08:20:00.000000+ 00:00') | 2 | 0 | 100 | 10 | 54 | 63.6396 | 55 | 100 | 32.5 | 55 | 77.5 |
('2014-01-06 08:00:00.000000+ 00:00', '2014-01-06 08:10:00.000000+ 00:00') | 1 | 0 | 99 | 10 | 55 | 62.9325 | 54.5 | 10 | 32.25 | 54.5 | 76.75 |
('2014-01-06 08:00:00.000000+ 00:00', '2014-01-06 08:10:00.000000+ 00:00') | 1 | 0 | 99 | 10 | 55 | 62.9325 | 54.5 | 99 | 32.25 | 54.5 | 76.75 |
例: HAVING句を使用したSTDDEV_SAMPのNULL値を持つ行のフィルタでの除外
/*PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, DESCRIBE(TEMPERATURE) FROM OCEAN_BUOYS WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) HAVING STDDEV_SAMP(TEMPERATURE) IS NOT NULL ORDER BY 2,3,4; /*Non-PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, DESCRIBE(TEMPERATURE) FROM OCEAN_BUOYS_NONPTI WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' GROUP BY TIME (MINUTES(10) AND BUOYID) USING TIMECODE(TIMECODE) HAVING STDDEV_SAMP(TEMPERATURE) IS NOT NULL ORDER BY 2,3,4;
結果は、両方のテーブルで同じです。
TIMECODE_ RANGE | GROUP BY TIME(MINUTES (10)) | BUOYID | Max (TEMPER-ATURE) | Min (TEMPER-ATURE) | Average (TEMPER-ATURE) | Stddev_Samp (TEMPER-ATURE) |
---|---|---|---|---|---|---|
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') | 7 | 1 | 79 | 70 | 74 | 4 |
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') | 13 | 44 | 56 | 43 | 43 | 6 |
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') | 1 | 0 | 99 | 10 | 55 | 63 |
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') | 2 | 0 | 100 | 10 | 54 | 64 |