group by

sym = `C`MS`MS`MS`IBM`IBM`C`C`C$SYMBOL
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t1 = table(timestamp, sym, qty, price);

t1;

select count(sym) as counts from t1 group by sym;

select top 2:3 count(sym) as counts from t1 group by sym;

select max(price), sym from t1 group by sym, minute(timestamp)

select avg(qty) from t1 group by sym;

select wavg(price, qty) as vwap, sum(qty) from t1 group by sym;

select wsum(price, qty) as dollarVolume, sum(qty) from t1 group by minute(timestamp) as ts;

select sum(qty) from t1 group by sym, timestamp.minute() as minute;

When group by is used with order by, the order by column(s) can be one or more of group by columns, or one or more of the new columns generated by group by calculation.

select sum(qty) from t1 group by sym, timestamp.minute() as minute order by minute;

In the examples above, the result of the function for each group is a scalar. In other cases, a function may output a vector or a dictionary rather than a scalar. For examples, the function stat outputs a dictionary, and the function ols outputs a coefficient vector or a dictionary that includes parameter estimates, t-stat, R square, etc. To output the results in multiple columns, we need to convert the vector/dictionary output to multiple scalars. In other words, we need to convert a composite column into multiple columns. This can be accomplished with "as" keyword and a constant string vector for column names.

y=1..15
factor1=3.2 1.2 5.9 6.9 11.1 9.6 1.4 7.3 2.0 0.1 6.1 2.9 6.3 8.4 5.6
factor2=1.7 1.3 4.2 6.8 9.2 1.3 1.4 7.8 7.9 9.9 9.3 4.6 7.8 2.4 8.7
id=take(1 2 3, 15).sort();
t=table(id, y, factor1, factor2);

t;

select ols(y,(factor1,factor2),true,0) as `int`factor1`factor2 from t group by id;

select ols(y,(factor1,factor2),true,2).Coefficient.tstat[1:] as `t1`t2 from t group by id;

To skip certain elements from the output of the function, we can leave the names of the corresponding elements empty.

select ols(y,(factor1,factor2),true,2).Coefficient.beta as ``factor1`factor2 from t group by id;

To customize the output, we can write a simple wrapper. In the following example, the output includes coefficient estimates and R square:

def myols(y,x) {
  r=ols(y,x,true,2)
  return r.Coefficient.beta join r.RegressionStat.statistics[0]
}

select myols(y,(factor1,factor2)) as `int`factor1`factor2`R2 from t group by id;