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 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可以与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