context by

context by 是 DolphinDB 独有的功能,是对标准 SQL 语句的拓展。使用 context by 子句可以简化对时间序列数据的操作。

传统的关系型数据库不支持时间序列数据处理。在关系型数据库管理系统 (RDBMS) 中,一张表由行的集合组成,行之间没有顺序。我们可以使用如 min, max, avg 等聚合函数来对行进行分组,但是不能在分组内的行使用顺序敏感的聚合函数,比如 first, last 等,或者对顺序敏感的向量函数,如 cumsum, cummax, ratios, deltas 等。DolphinDB 支持时间序列数据处理。context by 子句使组内处理时间序列数据更加方便。

context by 与 group by 类似,都对数据进行分组。但是,用 group by 时,每一组返回一个标量值,而用 context by 时,每一组返回一个和组内元素数量相同的向量。group by 只能配合聚合函数使用,而 context by 既可以配合聚合函数使用,也可以与移动窗口函数或累积函数等其它函数结合使用。context by 常用于基于组更新的场景,请参考 update 语句的例子。context by 还可以和 having 子句一起使用,详情参考 having

context by 通常与 cumsum, mavg 等时间序列函数一起使用,每个分组中记录的顺序对结果有直接影响。可在 context by 语句后使用 csort 关键字排序。使用 context by 分组后,csort 在 select 从句的表达式执行之前,对每个组内的数据进行排序。可对多个列(包括计算列)使用 csort 关键字,在组内进行升序或降序排序。csort 关键字还可以和 top 关键字一起使用,用于获取每个分组中的最新记录。

下例展示了 group by 和 context by 的不同之处。

$ sym = `C`MS`MS`MS`IBM`IBM`C`C`C$SYMBOL
$ price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
$ qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
$ timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
$ t1 = table(timestamp, sym, qty, price);

$ t1;

timestamp

sym

qty

price

09:34:07

C

2200

49.6

09:36:42

MS

1900

29.46

09:36:51

MS

2100

29.52

09:36:59

MS

3200

30.02

09:32:47

IBM

6800

174.97

09:35:26

IBM

5400

175.23

09:34:16

C

1300

50.76

09:34:26

C

2500

50.32

09:38:12

C

8800

51.29

$ select wavg(price,qty) as wvap, sum(qty) as totalqty from t1 group by sym;

sym

wvap

totalqty

C

50.828378

14800

IBM

175.085082

12200

MS

29.726389

7200

$ select sym, price, qty, wavg(price,qty) as wvap, sum(qty) as totalqty from t1 context by sym;

sym

price

qty

wvap

totalqty

C

49.6

2200

50.828378

14800

C

50.76

1300

50.828378

14800

C

50.32

2500

50.828378

14800

C

51.29

8800

50.828378

14800

IBM

174.97

6800

175.085082

12200

IBM

175.23

5400

175.085082

12200

MS

29.46

1900

29.726389

7200

MS

29.52

2100

29.726389

7200

MS

30.02

3200

29.726389

7200

计算每家公司的股票收益,我们不能使用 group by,但是可以使用 context by 和 ratio(\) 函数。在使用 context by 之前,需要确保记录在每一组内已按时间排好序。

$ select sym, timestamp, price, eachPre(\,price)-1.0 as ret from t1 context by sym;

sym

timestamp

price

ret

C

09:34:07

49.6

C

09:34:16

50.76

0.023387

C

09:34:26

50.32

-0.008668

C

09:38:12

51.29

0.019277

IBM

09:32:47

174.97

IBM

09:35:26

175.23

0.001486

MS

09:36:42

29.46

MS

09:36:51

29.52

0.002037

MS

09:36:59

30.02

0.016938

可以使用 contextby (:X) 高阶函数来达到同样的效果,但是结果是一个向量而不是表。

$ contextby(eachPre{ratio}, t1.price, t1.sym);
[,,1.002037,1.016938,,1.001486,1.023387,0.991332,1.019277]

这里用了一个部分应用 eachPre{ratio}。详情参考 部分应用

对每个股票,计算每分钟累计交易量:

$ select *, cumsum(qty) from t1 context by sym, timestamp.minute();

timestamp

sym

qty

price

cumsum_qty

09:34:07

C

2200

49.6

2200

09:34:16

C

1300

50.76

3500

09:34:26

C

2500

50.32

6000

09:38:12

C

8800

51.29

8800

09:32:47

IBM

6800

174.97

6800

09:35:26

IBM

5400

175.23

5400

09:36:42

MS

1900

29.46

1900

09:36:51

MS

2100

29.52

4000

09:36:59

MS

3200

30.02

7200

和 top 子句一起使用:

$ select top 2 * from t1 context by sym;

timestamp

sym

qty

price

09:34:07

C

2200

49.6

09:34:16

C

1300

50.76

09:32:47

IBM

6800

174.97

09:35:26

IBM

5400

175.23

09:36:42

MS

1900

29.46

09:36:51

MS

2100

29.52

和 top 子句一起使用时,不允许在 top 子句中指定一个范围:

$ select top 2:3 * from t1 context by sym;
Syntax Error: [line #2] When top clause uses together with context clause in SQL query, can't specify a range in top clause

context by 与 csort、top 子句一起使用,获取每只股票时间最近两条记录:

$ select top 2 * from t1 context by sym csort timestamp desc;

timestamp

sym

qty

price

09:38:12

C

8800

51.29

09:34:26

C

2500

50.32

09:35:26

IBM

5400

175.23

09:32:47

IBM

6800

174.97

09:36:59

MS

3200

30.02

09:36:51

MS

2100

29.52

context by 与 limit 一起使用能够获取表中每个分组前 n 条记录或最后 n 条记录。如果 limit 后面为正数,表示取前 n 条记录;如果 limit 后面为负数,表示取最后 n 条记录。例如,获取每只股票的前 2 条记录:

$ select * from t1 context by sym limit 2;

timestamp

sym

qty

price

09:34:07

C

2200

49.6

09:36:42

MS

1900

29.46

09:36:51

MS

2100

29.52

09:32:47

IBM

6800

174.97

09:35:26

IBM

5400

175.23

09:34:16

C

1300

50.76

获取每只股票最后两条记录:

$ select * from t1 context by sym limit -2;

timestamp

sym

qty

price

09:36:51

MS

2100

29.52

09:36:59

MS

3200

30.02

09:32:47

IBM

6800

174.97

09:35:26

IBM

5400

175.23

09:34:26

C

2500

50.32

09:38:12

C

8800

51.29

context by 与 csort, limit 一起使用能够获取表中每个分组排序后的前 n 条记录或最后 n 条记录:

$ select * from t1 context by sym csort qty limit -2;

timestamp

sym

qty

price

09:34:26

C

2500

50.32

09:38:12

C

8800

51.29

09:35:26

IBM

5400

175.23

09:32:47

IBM

6800

174.97

09:36:51

MS

2100

29.52

09:36:59

MS

3200

30.02

从每个股票价格对数量的回归模型,计算价格的拟合值。

$ select *, ols(price, qty)[0]+ols(price, qty)[1]*qty as fittedPrice from t1 context by sym;

timestamp

sym

qty

price

fittedPrice

09:34:07

C

2200

49.6

50.282221

09:34:16

C

1300

50.76

50.156053

09:34:26

C

2500

50.32

50.324277

09:38:12

C

8800

51.29

51.207449

09:32:47

IBM

6800

174.97

174.97

09:35:26

IBM

5400

175.23

175.23

09:36:42

MS

1900

29.46

29.447279

09:36:51

MS

2100

29.52

29.535034

09:36:59

MS

3200

30.02

30.017687

context by 和 order by 配合使用。order by 的字段必须是 context by 结果表中的字段。

$ select *, ols(price, qty)[0]+ols(price, qty)[1]*qty as fittedPrice from t1 context by sym order by timestamp;

timestamp

sym

qty

price

fittedPrice

09:32:47

IBM

6800

174.97

174.97

09:34:07

C

2200

49.6

50.075318

09:34:16

C

1300

50.76

49.911222

09:34:26

C

2500

50.32

50.130017

09:35:26

IBM

5400

175.23

175.23

09:36:42

MS

1900

29.46

29.447279

09:36:51

MS

2100

29.52

29.535034

09:36:59

MS

3200

30.02

30.017687

09:38:12

C

8800

51.29

51.278686

请注意,以上例子仅用于展示,股票价格对交易量的回归没有经济意义。

context by 与 contextby 函数的区别主要有以下三个方面:

1. contextby 生成一个向量,而 context by 在 select 语句中使用中,结果为数据表。 2. contextby 只能使用一个列进行分组,而 context by 子句可以使用多个列进行分组。 3. contextby 每次调用都只对一个列进行计算,因此如果要对多列进行计算,contextby 效率不高。使用 context by 可以一次性完成分组,再同时对多列进行计算。

提高性能的技巧

在使用 context by 之前,若对数据按照 context by 的列进行排序,可加快 context by 的运行速度。

$ n=1000000
$ ID=rand(100, n)
$ x=rand(10.0, n)
$ ta=table(ID, x)
$ tb=select * from ta order by ID;
$ timer select (NULL \:P x)-1 as ret from ta context by ID;
Time elapsed: 4.018 ms

$ timer select (NULL \:P x)-1 as ret from tb context by ID;
Time elapsed: 2.991 ms

在特定条件下使用 context by 子句时,系统内部进行了查询优化:

若要查询分区表中某些组的最新数据,可使用 context by 配合 csort,limit。在满足以下条件时,对 context by + csort + limit 场景,系统对查询语句的性能进行了优化:

1. 在 where 子句中对 context by 列进行了条件过滤。

2. csort 指定的列必须是分区列,且该列的分区方式是 VALUE 或 RANGE。

3. context by 与 csort 只能指定单列。

4. context by 指定的列也需要一起输出(通过 select 语句指定该列)。

可以通过添加 [HINT_EXPLAIN] 关键字查询执行计划来判断是否进行了优化。