PTIテーブルの場合:
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, STDDEV_SAMP(CAST(TEMPERATURE AS REAL)) 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 3,2,1;
非PTIテーブルの場合:
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, STDDEV_SAMP(CAST(TEMPERATURE AS REAL)) 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 3,2,1;
結果:
両方のクエリーの結果は同じであるため、1つの結果セットだけが表示されます。
TIMECODE_RANGE | GROUP BY TIME (MINUTES(10)) | BUOYID | Stddev_Samp (TEMPERATURE) |
---|---|---|---|
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') | 14 | 44 | ? |
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') | 13 | 44 | 5.75808610178378E 000 |
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') | 7 | 1 | 3.93700393700591E 000 |
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') | 2 | 0 | 6.36396103067893E 001 |
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') | 1 | 0 | 6.29325035256027E 001 |
例: HAVING句を使用したNULL結果のフィルタでの除外
PTIテーブルの場合:
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, STDDEV_SAMP(CAST(TEMPERATURE AS REAL)) 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 3,2,1;
非PTIテーブルの場合:
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, STDDEV_SAMP(CAST(TEMPERATURE AS REAL)) 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 3,2,1;
結果:
両方のクエリーの結果は同じであるため、1つの結果セットだけが表示されます。
TIMECODE_RANGE | GROUP BY TIME (MINUTES(10)) | BUOYID | Stddev_Samp (TEMPERATURE) |
---|---|---|---|
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') | 1 | 0 | 6.29325035256027E 001 |
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') | 2 | 0 | 6.36396103067893E 001 |
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') | 7 | 1 | 3.93700393700591E 000 |
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') | 13 | 44 | 5.75808610178378E 000 |