contextby

contextby(func, funcArgs, groupingCol, [sortingCol], [semanticFilter=1])

or

funcArg func:X groupingCol

or

func:X(funcArgs, groupingCol, [sortingCol], [semanticFilter=1])

func is a function. For the second use case, func can only have one parameter (funcArg).

funcArgs are the parameters of func. It is a tuple if there are more than 1 parameter of func.

groupingCol is the grouping variable(s). It can be one or multiple vectors.

sortingCol is an optional argument for within group sorting before applying func.

semanticFilter (optional) is a positive integer indicating which columns to include in calculations when funcArgs is a table. The possible values are:

• 0 - All columns
• 1 (default) - Columns of FLOATING, INTEGRAL, and DECIMAL categories, excluding the COMPRESSED data type.
• 2 - Columns of TEMPORAL category.
• 3 - Columns of LITERAL category, excluding the BLOB data type.
• 4 - Columns of FLOATING, INTEGRAL, DECIMAL, and TEMPORAL categories, excluding the COMPRESSED data type.

The vectors in groupingCol, sortingCol and each of the function argument in funcArgs all have the same size.

Calculate func(funcArgs) for each groupingCol group. The result is a vector of the same size as each of the input arguments other than func. If func is an aggregate function, all elements within the same group have identical result. We can use sortingCol to sort the within-group data before the calculation.

Note: The keyword defg must be used to declare an aggregate function.

sym=`IBM`IBM`IBM`MS`MS`MS
price=172.12 170.32 175.25 26.46 31.45 29.43
qty=5800 700 9000 6300 2100 5300
trade_date=2013.05.08 2013.05.06 2013.05.07 2013.05.08 2013.05.06 2013.05.07;
contextby(avg, price, sym);
// output
[172.563,172.563,172.563,29.113,29.113,29.113]

price avg :X sym;
// output
[172.563,172.563,172.563,29.113,29.113,29.113]

price at price>contextby(avg, price,sym);
// output
[175.25,31.45,29.43]

price at price>price avg :X sym;
// output
[175.25,31.45,29.43]

sym at price>contextby(avg, price,sym);
// output
["IBM","MS","MS"]

// calculate volume-weighted average price (vwap)
contextby(wavg, [price, qty], sym);
// output
[173.856,173.856,173.856,28.374,28.374,28.374]

contextby(ratios, price, sym, trade_date) - 1;
// output
[-0.01786,,0.028946,-0.100917,,-0.064229]

Multiple vectors for groupingCol :

sym=`IBM`IBM`IBM`IBM`IBM`IBM`MS`MS`MS`MS`MS`MS
date=2020.12.01 + 0 0 0 1 1 1 0 0 0 1 1 1
qty=5800 700 9000 1000 3500 3900 6300 2100 5300 7800 1200 4300
contextby(cumsum, qty, [sym,date]);
// output
[5800,6500,15500,1000,4500,8400,6300,8400,13700,7800,9000,13300]

We can embed a contextby template into a SQL query:

t1=table(trade_date,sym,qty,price);
t1;

// select orders with a price higher than the average price of the group
select trade_date, sym, qty, price from t1 where price > contextby(avg, price,sym);

// add 1 day to all trade dates in t1
contextby(temporalAdd{,1d}, t1, t1.sym,,2)