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]]
*/
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] |