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 |