make-series operator

Syntax

T | make-series [MakeSeriesParameters] [ Column =] Aggregation [default = DefaultValue] [, ...] on AxisColumn [from start] [to end] step step [ by [ Column =] GroupExpression [, ...]]

Arguments

• Column: Optional name for a result column. Defaults to a name derived from the expression.

• DefaultValue: Default value that will be used instead of absent values. If there is no row with specific values of AxisColumn and GroupExpression, then in the results the corresponding element of the array will be assigned a DefaultValue. If DefaultValue is omitted, then 0 is assumed.

• Aggregation: A call to an aggregation function such as count() or avg(), with column names as arguments. See the list of aggregation functions. Only aggregation functions that return numeric results can be used with the make-series operator.

• AxisColumn: A column on which the series will be ordered, usually of type datetime or timespan, but all numeric types are also accepted.

• start: (optional) The low bound value of the AxisColumn for each of the series to be built. start, end, and step are used to build an array of AxisColumn values within a given range and using specified step. All Aggregation values are ordered respectively to this array. This AxisColumn array is also the last output column in the output that has the same name as AxisColumn. If a start value is not specified, the start is the first bin (step) which has data in each series.

• end: (optional) The high bound (non-inclusive) value of the AxisColumn. The last index of the time series is smaller than this value (and will be start plus integer multiple of step that is smaller than end). If end value is not provided, it will be the upper bound of the last bin (step) which has data per each series.

• step: The difference between two consecutive elements of the AxisColumn array (that is, the bin size). For a list of possible time intervals, see timespan.

• GroupExpression: An expression over the columns that provides a set of distinct values. Typically it's a column name that already provides a restricted set of values.

• MakeSeriesParameters: Zero or more (space-separated) parameters in the form of Name = Value that control the behavior. The following parameters are supported:

  Name	Description
  kind	Produces default result when the input of make-series operator is empty. Value: nonempty

Returns

The input rows are arranged into groups having the same values of the by expressions and the bin_at(AxisColumn, step, start) expression. Then the specified aggregation functions are computed over each group, producing a row for each group. The result contains the by columns, AxisColumn column and also at least one column for each computed aggregate. (Aggregations over multiple columns or non-numeric results are not supported.)

This intermediate result has as many rows as there are distinct combinations of by and bin_at(AxisColumn, step, start) values.

Finally the rows from the intermediate result arranged into groups having the same values of the by expressions and all aggregated values are arranged into arrays (values of dynamic type). For each aggregation, there is one column containing its array with the same name. The last column is an array containing the values of AxisColumn binned according to the specified step.

List of aggregation functions

Examples

A table that shows arrays of the numbers and average prices of each fruit from each supplier ordered by the timestamp with specified range. There's a row in the output for each distinct combination of fruit and supplier. The output columns show the fruit, supplier, and arrays of: count, average, and the whole timeline (from 2016-01-01 until 2016-01-10). All arrays are sorted by the respective timestamp and all gaps are filled with default values (0 in this example). All other input columns are ignored.

T | make-series PriceAvg=avg(Price) default=0
on Purchase from datetime(2016-09-10) to datetime(2016-09-13) step 1d by Supplier, Fruit

let data=datatable(timestamp:datetime, metric: real)
[
  datetime(2016-12-31T06:00), 50,
  datetime(2017-01-01), 4,
  datetime(2017-01-02), 3,
  datetime(2017-01-03), 4,
  datetime(2017-01-03T03:00), 6,
  datetime(2017-01-05), 8,
  datetime(2017-01-05T13:40), 13,
  datetime(2017-01-06), 4,
  datetime(2017-01-07), 3,
  datetime(2017-01-08), 8,
  datetime(2017-01-08T21:00), 8,
  datetime(2017-01-09), 2,
  datetime(2017-01-09T12:00), 11,
  datetime(2017-01-10T05:00), 5,
];
let interval = 1d;
let stime = datetime(2017-01-01);
let etime = datetime(2017-01-10);
data
| make-series avg(metric) on timestamp from stime to etime step interval

When the input to make-series is empty, the default behavior of make-series produces an empty result as well.

let data=datatable(timestamp:datetime, metric: real)
[
  datetime(2016-12-31T06:00), 50,
  datetime(2017-01-01), 4,
  datetime(2017-01-02), 3,
  datetime(2017-01-03), 4,
  datetime(2017-01-03T03:00), 6,
  datetime(2017-01-05), 8,
  datetime(2017-01-05T13:40), 13,
  datetime(2017-01-06), 4,
  datetime(2017-01-07), 3,
  datetime(2017-01-08), 8,
  datetime(2017-01-08T21:00), 8,
  datetime(2017-01-09), 2,
  datetime(2017-01-09T12:00), 11,
  datetime(2017-01-10T05:00), 5,
];
let interval = 1d;
let stime = datetime(2017-01-01);
let etime = datetime(2017-01-10);
data
| limit 0
| make-series avg(metric) default=1.0 on timestamp from stime to etime step interval
| count

Using kind=nonempty in make-series will produce a non-empty result of the default values:

let data=datatable(timestamp:datetime, metric: real)
[
  datetime(2016-12-31T06:00), 50,
  datetime(2017-01-01), 4,
  datetime(2017-01-02), 3,
  datetime(2017-01-03), 4,
  datetime(2017-01-03T03:00), 6,
  datetime(2017-01-05), 8,
  datetime(2017-01-05T13:40), 13,
  datetime(2017-01-06), 4,
  datetime(2017-01-07), 3,
  datetime(2017-01-08), 8,
  datetime(2017-01-08T21:00), 8,
  datetime(2017-01-09), 2,
  datetime(2017-01-09T12:00), 11,
  datetime(2017-01-10T05:00), 5,
];
let interval = 1d;
let stime = datetime(2017-01-01);
let etime = datetime(2017-01-10);
data
| limit 0
| make-series kind=nonempty avg(metric) default=1.0 on timestamp from stime to etime step interval