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