In InfoWorks WS Pro it is possible to run queries that analyse the results across all time-steps. This is achieved by running aggregate functions on the results. It is not possible to mix queries using time series results with bare one-to-many fields or bare array fields.
When using results in this manner there are a number of additional aggregate functions and one aggregate function has a different meaning. The aggregate functions
COUNT
MAX
MIN
ANY
ALL
FIRST
LAST
have the expected meaning e.g.
COUNT(tsr.head>10) will return the number of timesteps in which the head is greater than 10
MAX(tsr.pressure) will return the maximum pressure,
MIN(tsr.pressure) will return the minimum pressure
ALL(tsr.head>10) will return true if the head is greater than 10 for all timesteps, whilst ANY(tsr.head<10) will return true if the head is less than 10 for any timestep.
In this context FIRST will return the value for the first timestep, and LAST will return the value for the last timestep.
AVG
In this context AVG does not simply return an average of the values at all the timesteps. Since the length of timesteps can vary, AVG returns the time weighted average i.e the sum of the values for all timesteps except the last multiplied by the duration of that timestep, divided by the total duration. The results are treated as step functions i.e. the results are assumed to remain constant for the duration of each timestep.
SUM
By contrast, SUM simply sums all the values at all the timesteps. This will almost certainly tell you something useful only if all the timesteps are the same length.
The new aggregate functions for time series results are as follows:
DURATION
Will return the time in minutes for which the parameter is true e.g. DURATION(tsr.head<110)>30
Will select those nodes for which the head drops below 110 for more than 30 minutes (not necessarily contiguous)
INTEGRAL
This returns the sum of the value of the expression at each timestep multiplied by the length of the timestep in minutes. Since the SQL engine is not aware of the units in which values are being reported it is the responsibility of the user to ensure that any required multiplication factor is applied.
WHENEARLIEST
Returns the earliest time for which the expression is true e.g.
WHENEARLIEST(tsr.head<150) will return the first time at which the head is less than 150.
EARLIEST
Returns the first non-null value of an expression. This is only likely to give a meaningful answer if used in combination with the IIF function e.g. to find the first time at which the head is less than 150 and to see the value at that time the query
SELECT EARLIEST(IIF(tsr.head<150,tsr.head,NULL)),WHENEARLIEST(tsr.head<150) could be run.
LATEST
Returns the latest time for which the expression is true.
WHENLATEST
Returns the last non-null value of an expression. As with WHENEARLIEST this is only likely to give a meaningful answer if used in combination with the IIF function.
WHENMIN
Returns the time at which the expression is at its minimum. If there is more than one timestep at which the expression is at the minimum value this will report the earliest time.
WHENMAX
Returns the time at which the expression is at its maximum. If there is more than one timestep at which the expression is at the maximum value this will report the earliest time.
Note that MAX, MIN, WHENMIN and WHENMAX work on signed values, if you want to ignore the sign of values you should use the ABS function.