rowGroupby

Syntax

rowGroupby(func, funcArgs, groupingCol, [mode='tuple'], [ascending=true])

Arguments

func is an aggregate function.

funcArgs is the argument(s) passed to func. Multiple arguments can be represented in a tuple, and the dimension of each element must be consistent with groupingCol.

groupingCol is a non-empty matrix or array vector indicating the grouping column(s).

mode (optional) specifies the returned data form. It can be:

  • "tuple" (default): Return a tuple of length 2, the first element of which is an array vector that stores the grouping variables, and the second element is an array vector that stores the result of applying funcArgs to func in each group.

  • "dict": Return a dictionary with a key-value pair. 'key' stores the grouping variables and 'value' stores the result of applying funcArgs to func in each group.

  • "table": Return a table with two columns. 'key' stores the grouping variables and 'value' stores the result of applying funcArgs to func in each group.

ascending (optional) is a Boolean value indicating whether to sort the output by groupingCol in ascending or descending order. The default value is true.

Details

Group the data by groupingCol, then calculate func(funcArgs) and return a scalar for each group.

Return value: As specified in mode, sorted by groupingCol in ascending order.

Examples

Group data by price, and calculate the sum of qty for each group:

sym=`A`B
price = array(DOUBLE[], 0).append!([12.5 12.6 12.5 12.5 12.6, 15.5 15.5 15.5 15.3 15.3])
qty = array(INT[], 0).append!([201 212 220 215 214, 210 213 223 219 211])
t=table(sym,price,qty)
t;
sym price qty
A [12.5 12.6 12.5 12.5 12.6] [201, 212, 220, 215, 214]
B [15.5 15.5 15.5 15.3 15.3] [210, 213, 223, 219, 211]

Set mode='dict':

rowGroupby(func=sum,funcArgs=t.qty,groupingCol=t.price,mode='dict')

/* output:
key->[[12.5,12.6],[15.3,15.5]]
value->[[636,426],[430,646]]
*/
Set mode='table':
rowGroupby(func=sum,funcArgs=t.qty,groupingCol=t.price,mode='table')
key value
[12.5,12.6] [636,426]
[15.3,15.5] [430,646]

Set mode='tuple':

select rowGroupby(sum, qty, price, 'tuple') as `a`b from t
key value
[12.5,12.6] [636,426]
[15.3,15.5] [430,646]

Set ascending=false:

rowGroupby(func=sum,funcArgs=t.qty,groupingCol=t.price,mode='dict', ascending=false)

/* output:
value->[[426,636],[646,430]]
key->[[12.6000,12.5000],[15.5000,15.3000]]
*/

Example 2. Apply rowGroupby on matrices:

m=matrix([32.5 12.6 22.5 42.5 32.6, 17.5 25.5 35.5 17.3 19.3, 17 20.1 30 13 19])
g=matrix([1 2 2 5 4, 2 2 3 2 1, 1 3 2 3 5])
// rowGroupby m with groupingCol=g
rowGroupby(func=sum, funcArgs=m, groupingCol=g, mode='table')
key value
[1,2] [49.5,17.5]
[2,3] [38.1,20.1]
[2,3] [52.5,35.5]
[2,3,5] [17.3,13,42.5]
[1,4,5] [19.3,32.6,19]