cgroup by

The cgroup by (cumulative group) clause performs cumulative grouping calculations. Calculations are conducted in the first cgroup by group, the first 2 cgroup by groups, the first 3 cgroup by groups, and so on.

A SQL statement with cgroup by clause must also use order by clause to sort the grouping results before conducting the cumulative calculation.

If the SQL statement uses the cgroup by clause, the execution sequence is as follows: first apply the filtering conditions (if any), and then conduct the calculations in the select clause within the groups determined by the cgroup by and group by (if any) columns, then sort the grouping calculation results according to the required order by columns (must belong to the cgroup by and group by (if any) columns), and finally calculate the cumulative value of the grouping calculation results. If group by is used, the cumulative value is calculated within each group by group.

A SQL statement with cgroup by clause only supports the following aggregate functions: sum, sum2, sum3, sum4, prod, max, min, first, last, count, size, avg, std, var, skew, kurtosis, wsum, wavg, corr, covar, contextCount, contextSum, contextSum2.

Examples

The following example uses the cgroup by clause to calculate volume-weighted average price (vwap).

t = table(`A`A`A`A`B`B`B`B as sym, 09:30:06 09:30:28 09:31:46 09:31:59 09:30:19 09:30:43 09:31:23 09:31:56 as time, 10 20 10 30 20 40 30 30 as volume, 10.05 10.06 10.07 10.05 20.12 20.13 20.14 20.15 as price);
t;
sym time volume price
A 09:30:06 10 10.05
A 09:30:28 20 10.06
A 09:31:46 10 10.07
A 09:31:59 30 10.05
B 09:30:19 20 20.12
B 09:30:43 40 20.13
B 09:31:23 30 20.14
B 09:31:56 30 20.15
select wavg(price, volume) as wvap from t where sym=`A cgroup by minute(time) as minute order by minute;
time wvap
09:30m 10.056667
09:31m 10.055714

cgroup by can be used together with group by:

select wavg(price, volume) as wvap from t group by sym cgroup by minute(time) as minute order by minute;
sym minute wvap
A 09:30m 10.056667
B 09:30m 20.126667
A 09:31m 10.055714
B 09:31m 20.135833
select wavg(price, volume) as wvap from t group by sym cgroup by minute(time) as minute order by sym, minute;
sym minute wvap
A 09:30m 10.056667
A 09:31m 10.055714
B 09:30m 20.126667
B 09:31m 20.135833