Time Series Results

Aggregate functions

When using results in this manner the following aggregate functions may be used:

ALL

Returns true if the expression is true for all timesteps under consideration.

ALL (tsr.depth2d > 1.0)

will return true if the depth is greater than 1 for all timesteps under consideration.

ANY

Returns true if the expression is true for at least one of the timesteps under consideration.

AVG

AVG does not simply return an average of the values at all timesteps as length of timesteps may 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 the timestep, divided by the total duration.

COUNT

Returns the number of timesteps under consideration for which the expression is true.

COUNT (tsr.depth2d > 1)

will return the number of timesteps under consideration for which depth2d > 1

DURATION

Returns the time in minutes for which the expression is true for timesteps under consideration. (Timesteps are not necessarily contiguous.)

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 depth is greater than 0.01

SELECT EARLIEST (IIF(tsr.depth > 0.01, tsr.depth, NULL)

FIRST

Returns the value of the expression for the first timestep under consideration.

INTEGRAL

Returns the sum of the value of the expression at each timestep under consideration multiplied by the length of the timestep in minutes.

Note that 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.

LAST

Returns the value of the expression for the last timestep under consideration.

LATEST

Returns the last non-null value of an expression. As with EARLIEST, only likely to give a meaningful answer if used in combination with the IIF function.

MAX

Returns the maximum value for all the timesteps under consideration.

MIN

Returns the minimum value for all the timesteps under consideration.

SUM

Returns the sum of all the values at all timesteps.

Probably only useful if all timesteps are of the same length.

WHENEARLIEST

Returns the earliest time for which the expression is true e.g.

WHENEARLIEST (tsr.depnod <150)

Returns the first time at which the level is less than 150

WHENLATEST

Returns the latest time for which the expression is true.

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.

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.

The aggregate functions EARLIEST, LATEST, WHENEARLIEST, WHENLATEST, WHENMAX and WHENMIN will return dates if the simulation is using absolute times or numbers of minutes if the simulation is using relative times.

The functions AVG, INTEGRAL and SUM will not consider the final timestep in the simulation because the final timestep does not a known duration since timestep lengths can vary. This only applies to the the final timestep in the simulation, not to the final timestep under consideration.

WHEN clauses

It is possible to limit the number of time-steps used in the time varying aggregate functions by use of a WHEN clause e.g.

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_no = 20

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_start = #01/01/2013 12:30#

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_no = tsr.timesteps

SELECT MAX(tsr.flooddepth) WHEN tsr.timestep_no > 20

If a WHEN clause is used, all the above aggregate functions perform their calculations only on the time-steps selected by the WHEN clause.

The field values that can be used in WHEN clauses are as follows:

tsr.timestep_no

The number of the timestep, with 1 being the first timestep as an integer

tsr.timestep_start

The time of the start of the timestep as a date or number (see below)

tsr.timesteps

The number of timesteps in the simulation as an integer

tsr.timestep_duration

The duration of the timestep as a number of minutes

tsr.sim_start

The time of the start of the simulation as a date or number (see below)

tsr.sim_end

The time of the end of the simulation as a date or number (see below)

If the simulation uses 'relative times' then timestep_start, sim_start and sim_end return a number of minutes from the notional time 0, otherwise the date and time is returned as a 'date' value.

The expression in the WHEN clause can include the above field values along with scalar and list variables e.g. you can list a number of timesteps you are interested in and then use the list function.

The results will only be processed for timesteps for which the WHEN clause is true.