interval#

swordfish.function.interval()#

In SQL queries, group data into continuous intervals with the length of duration. For intervals without any data, fill the results using the interpolation method specified by fill. This function must be used in a SQL group by clause.

Note

Temporal data types in “where” conditions are automatically converted.

Parameters:

  • X (Constant) – A vector of integral or temporal type.

  • duration (Constant) – Of integral or duration type. The following time units are supported (case-sensitive): w, d, H, m, s, ms, us, ns, and trading calendar identifier consisting of four capital letters (the trading calendar file must be stored in marketHolidayDir). As time unit y is not supported, to group X by year, convert the data format of X with function year.

  • fill (Constant) –

    Indicates how to fill the missing values of the result. It can take the value of “prev”, “post”, “linear”, “null”, a specific numeric value and “none”.

    • ”prev”: the previous value

    • ”post”: the next value

    • ”linear”: linear interpolation. For non-numerical data, linear interpolation cannot be used and the “prev” method will be used instead.

    • ”null”: null value

    • a specific numeric value.

    • ”none”: do not interpolate

  • step (Constant, optional) –

    Of integral or duration type. It’s an optional parameter indicating the step size of the sliding window. It must be a number which can divide the duration. This parameter allows us to specify a sliding interval that is smaller than duration. The default value is the same as duration, which means the calculation window slides by the length of duration.

    Note

    If step is specified, the following aggregation calculations are not supported: atImax, atImin, difference, imax, imin, lastNot, mode, percentile.

    If the value of step differs from duration, user-defined aggregation functions are not supported for distributed queries.

  • explicitOffset (Constant, optional) – An optional BOOLEAN. When explicitOffset = true, the first interpolation window starts at the starting point specified by the SQL where clause. When explicitOffset = false (default), the first interpolation window starts at the nearest point that is divisible by step before the starting point specified by the SQL where clause.

  • closed (Constant, optional) – A string indicating which boundary of the interval is closed. It can be specified as ‘left’ or ‘right’.

  • label (Constant, optional) – A string indicating which boundary is used to label the interval with. It can be specified as ‘left’ or ‘right’.

  • origin (Constant, optional) –

    A string or a scalar of the same temporal type as X, indicating the timestamp when the intervals start. When origin is a string, it can be specified as:

    • ’epoch’: 1970-01-01;

    • ’start’: the first value of the timeseries;

    • ’start_day’: 00:00 of the first day of the timeseries;

    • ’end’: the last value of the timeseries;

    • ’end_day’: 24:00 of the last day of the timeseries.

    Note that origin is specified only when explicitOffset = false.

On this page