# 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;
```

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);
```

sym

qty

price

2013.05.07

IBM

9000

175.25

2013.05.06

MS

2100

31.45

2013.05.07

MS

5300

29.43