cgroup by
使用cgroup by(cumulative group)子句可进行累计分组计算,第二组的记录包含第一个组的记录,第三个组的记录包含前两组的记录,以此类推。
若SQL语句使用cgroup by子句,其执行顺序如下:
-
使用过滤条件(若有)
-
根据cgroup by的列与group by的列(若有)
-
对select子句中的项目进行分组计算
-
根据order by的列(必须使用,且必须属于group by列或cgroup by列)对分组计算结果进行排序
-
计算累计值。若使用group by,则在每个group by组内计算累计值。
注意:cgroup by子句必须与order by子句一同使用,以在执行累积计算前,将分组计算结果排序。
使用cgroup by的SQL语句只支持以下聚合函数:sum, sum2, sum3, sum4, prod, max, min, first, last, count, size, avg, std, var, skew, kurtosis, wsum, wavg, corr, covar, contextCount, contextSum, contextSum2.
例子
下例使用cgroup by计算交易量加权平均交易价格(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 vwap from t where sym=`A cgroup by minute(time) as minute order by minute;
time | vwap |
---|---|
09:30m | 10.056667 |
09:31m | 10.055714 |
cgroup by可以与group by配合使用:
select wavg(price, volume) as vwap from t group by sym cgroup by minute(time) as minute order by minute;
sym | minute | vwap |
---|---|---|
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 vwap from t group by sym cgroup by minute(time) as minute order by sym, minute;
sym | minute | vwap |
---|---|---|
A | 09:30m | 10.056667 |
A | 09:31m | 10.055714 |
B | 09:30m | 20.126667 |
B | 09:31m | 20.135833 |