Example: Calculating the Maximum for PTI and non-PTI Tables
The following example shows how the MAX function can be used with both Primary Time Index (PTI) and non-PTI tables.
The examples use the tables and data setup in Table and Data Definition for Time Series Aggregates Examples.
/*PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, MAX(TEMPERATURE), COUNT(*) 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; /*Non-PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, MAX(TEMPERATURE), COUNT(*) 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 | MAX (TEMPERATURE) | COUNT(*) |
---|---|---|---|---|
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') | 1 | 0 | 99 | 3 |
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') | 2 | 0 | 100 | 2 |
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') | 7 | 1 | 79 | 6 |
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') | 13 | 44 | 56 | 10 |
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') | 14 | 44 | 43 | 1 |
Example: Using the HAVING Clause to Filter the MAX Function Results
The following example shows how the MAX function can be used with the HAVING clause to filter the results. In this case, the example filters out the rows which compute their result on less than three values.
The examples use the tables and data setup in Table and Data Definition for Time Series Aggregates Examples.
/*PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, MAX(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 COUNT(*) >= 3 ORDER BY 3,2,1; /*Non-PTI Table*/ SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, MAX(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 COUNT(*) >= 3 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 | MAX (TEMPERATURE) |
---|---|---|---|
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') | 1 | 0 | 99 |
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00' | 7 | 1 | 79 |
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') | 13 | 44 | 56 |