contextby (:X)

Syntax

contextby(func, funcArgs, groupingCol, [sortingCol])

or

funcArg func:X groupingCol

Arguments

func is a function.

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.

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

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

Details

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.

Examples

$ 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);
[172.563,172.563,172.563,29.113,29.113,29.113]

$ price avg :X sym;
[172.563,172.563,172.563,29.113,29.113,29.113]

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

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

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

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

$ contextby(ratios, price, sym, trade_date) - 1;
[-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]);
[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;

trade_date	sym	qty	price
2013.05.08	IBM	5800	172.12
2013.05.06	IBM	700	170.32
2013.05.07	IBM	9000	175.25
2013.05.08	MS	6300	26.46
2013.05.06	MS	2100	31.45
2013.05.07	MS	5300	29.43

$ select trade_date, sym, qty, price from t1 where price > contextby(avg, price,sym);

trade_date	sym	qty	price
2013.05.07	IBM	9000	175.25
2013.05.06	MS	2100	31.45
2013.05.07	MS	5300	29.43