For a PTI table:
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;
For a non-PTI table:
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;
Result:
The results of both queries are identical so only one result set is shown.
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 |
Example: Filtering Out NULL Results with the HAVING Clause
For a PTI table:
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;
For a non-PTI table:
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;
Result:
The results of both queries are identical so only one result set is shown.
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 |