pivot by
pivot by 是 DolphinDB 的独有功能,是对标准 SQL 语句的拓展。它将表中一列或多列的内容按照两个维度重新排列,亦可配合数据转换函数使用。 与 select 子句一起使用时返回一个表,而和 exec 语句一起使用时返回一个矩阵。若重新排列后存在行维度存在多个相同值,则会进行去重,只保留最后一个值。 参考 pivot 和 unpivot。
pivot by 在以下版本中进行了优化:
优化版本 |
优化内容 |
---|---|
1.20.4
|
pivot by 后面的列数可为两个以上,其中最后一列为列维度,其它各列均为行维度。
使用 pivot by 时,select 子句可使用多个指标。
|
1.30.21
|
对满足以下2个条件的 pivot by 语句进行了优化:
1. 对查询的列不使用以下函数时:聚合函数(例如sum)、序列相关函数(例如:last)、行函数(例如:rowMin)、填充(例如:ffill)函数。
2. pivot by 最后一列为分区列。
|
例子
例1:行维度为一列(即 pivot by 之后仅有两列)
$ sym = `C`MS`MS`MS`IBM`IBM`C`C`C
$ 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:35:42,09:36:51,09:36:59,09:35:47,09:36:26,09:34:16,09:35:26,09:36:12]
$ t2 = table(timestamp, sym, qty, price);
$ t2;
timestamp |
sym |
qty |
price |
---|---|---|---|
09:34:07 |
C |
2200 |
49.6 |
09:35:42 |
MS |
1900 |
29.46 |
09:36:51 |
MS |
2100 |
29.52 |
09:36:59 |
MS |
3200 |
30.02 |
09:35:47 |
IBM |
6800 |
174.97 |
09:36:26 |
IBM |
5400 |
175.23 |
09:34:16 |
C |
1300 |
50.76 |
09:35:26 |
C |
2500 |
50.32 |
09:36:12 |
C |
8800 |
51.29 |
将表 t2 的 timestamp 列作为行索引,sym 列作为列索引,查看 price 列的值。
$ select price from t2 pivot by timestamp, sym;
timestamp |
C |
IBM |
MS |
---|---|---|---|
09:34:07 |
49.6 |
||
09:34:16 |
50.76 |
||
09:35:26 |
50.32 |
||
09:35:42 |
29.46 |
||
09:35:47 |
174.97 |
||
09:36:12 |
51.29 |
||
09:36:26 |
175.23 |
||
09:36:51 |
29.52 |
||
09:36:59 |
30.02 |
查看表 t2 每一分钟,不同 sym 对应的 price。
$ a=select price from t2 pivot by timestamp.minute(), sym;
$ a;
minute_timestamp |
C |
IBM |
MS |
---|---|---|---|
09:34m |
50.76 |
||
09:35m |
50.32 |
174.97 |
29.46 |
09:36m |
51.29 |
175.23 |
30.02 |
$ typestr a;
TABLE
//pivot by 与 exec 搭配使用,查看一分钟内各个 sym 对应的 price 的数量,结果为一个矩阵
$ b=exec count(price) from t2 pivot by timestamp.minute(), sym;
$ b;
label |
C |
IBM |
MS |
---|---|---|---|
09:34m |
2 |
||
09:35m |
1 |
1 |
1 |
09:36m |
1 |
1 |
2 |
$ typestr b;
FAST DOUBLE MATRIX
例2:计算 ETF 的内在价值。为简便起见,假设 ETF 有两个成分股:AAPL 和 FB,其成分权重为 [0.6, 0.4]。
$ symbol=take(`AAPL, 6) join take(`FB, 5)
$ time=2019.02.27T09:45:01.000000000+[146, 278, 412, 445, 496, 789, 212, 556, 598, 712, 989]
$ price=173.27 173.26 173.24 173.25 173.26 173.27 161.51 161.50 161.49 161.50 161.51
$ quotes=table(symbol, time, price)
$ weights=dict(`AAPL`FB, 0.6 0.4)
$ ETF = select symbol, time, price*weights[symbol] as price from quotes;
$ select price from ETF pivot by time, symbol;
Time |
AAPL |
FB |
---|---|---|
2019.02.27T09:45:01.000000146 |
103.962 |
|
2019.02.27T09:45:01.000000212 |
64.604 |
|
2019.02.27T09:45:01.000000278 |
103.956 |
|
2019.02.27T09:45:01.000000412 |
103.944 |
|
2019.02.27T09:45:01.000000445 |
103.95 |
|
2019.02.27T09:45:01.000000496 |
103.956 |
|
2019.02.27T09:45:01.000000556 |
64.6 |
|
2019.02.27T09:45:01.000000598 |
64.596 |
|
2019.02.27T09:45:01.000000712 |
64.6 |
|
2019.02.27T09:45:01.000000789 |
103.962 |
|
2019.02.27T09:45:01.000000989 |
64.604 |
以上结果为在每个时间戳经过 ETF 权重调整的股票价格。为了计算 ETF 在每个时间戳的内在价值,需要使用 ffill 函数来填充每个股票的 NULL 值,然后将所有股票价格相加。可在一个 SQL 语句中完成。
$ select rowSum(ffill(price)) from ETF pivot by time, symbol;
Time |
rowSum |
---|---|
2019.02.27T09:45:01.000000146 |
103.962 |
2019.02.27T09:45:01.000000212 |
168.566 |
2019.02.27T09:45:01.000000278 |
168.56 |
2019.02.27T09:45:01.000000412 |
168.548 |
2019.02.27T09:45:01.000000445 |
168.554 |
2019.02.27T09:45:01.000000496 |
168.56 |
2019.02.27T09:45:01.000000556 |
168.556 |
2019.02.27T09:45:01.000000598 |
168.552 |
2019.02.27T09:45:01.000000712 |
168.556 |
2019.02.27T09:45:01.000000789 |
168.562 |
2019.02.27T09:45:01.000000989 |
168.566 |
例3:使用 pivot by 时某些新建列名的自动转换
1.30.14 版本以前,DolphinDB 中列名仅可使用中文或英文字母、数字或下划线 (_),且必须以中文或英文字母开头。
1.30.14 版本开始,由 pivot by, addColumn 操作产生的列名,支持包含特殊字符,或以数字开头。
详情可以参考:创建表章节。
$ date = take(2021.08.01 2021.08.02 2021.08.03, 12)
$ sym = take(["IBM N", "_MSFTN", "3_GOOGS", ""], 12).sort()
$ value = 1..12
$ t=table(date, sym, value)
$ re = select value from t pivot by date, sym
date |
NULL |
3_GOOGS |
IBM N |
_MSFTN |
---|---|---|---|---|
2021.08.01 |
1 |
4 |
7 |
10 |
2021.08.02 |
2 |
5 |
8 |
11 |
2021.08.03 |
3 |
6 |
9 |
12 |
$ select _"NULL" from re
NULL |
---|
1 |
2 |
3 |
$ select _"3_GOOGS" from re
3_GOOGS |
---|
4 |
5 |
6 |
下例,设置 removeSpecialCharInColumnName = true,股票代码 “600300” 与 “600600” 在成为列名后,系统自动将其处理为 “C600300” 与 “C600600”。
$ symbol=take(`600300, 6) join take(`600600, 5)
$ time=2019.02.27T09:45:01.000000000+[146, 278, 412, 445, 496, 789, 212, 556, 598, 712, 989]
$ price=173.27 173.26 173.24 173.25 173.26 173.27 161.51 161.50 161.49 161.50 161.51
$ quotes=table(symbol, time, price)
$ weights=dict(`600300`600600, 0.6 0.4)
$ ETF = select symbol, time, price*weights[symbol] as price from quotes;
$ select last(price) from ETF pivot by time, symbol;
time |
C600300 |
C600600 |
---|---|---|
2019.02.27T09:45:01.000000146 |
103.962 |
|
2019.02.27T09:45:01.000000212 |
64.604 |
|
2019.02.27T09:45:01.000000278 |
103.956 |
|
2019.02.27T09:45:01.000000412 |
103.944 |
|
2019.02.27T09:45:01.000000445 |
103.95 |
|
2019.02.27T09:45:01.000000496 |
103.956 |
|
2019.02.27T09:45:01.000000556 |
64.6 |
|
2019.02.27T09:45:01.000000598 |
64.596 |
|
2019.02.27T09:45:01.000000712 |
64.6 |
|
2019.02.27T09:45:01.000000789 |
103.962 |
|
2019.02.27T09:45:01.000000989 |
64.604 |
例4:行维度为多列(即 pivot by 之后多于两列)
$ date = 2020.09.21 + 0 0 0 0 1 1 1 1
$ sym = `MS`MS`GS`GS`MS`MS`GS`GS$SYMBOL
$ factorNum = 1 2 1 2 1 2 1 2
$ factorValue = 1.2 -3.4 -2.5 6.3 1.1 -3.2 -2.1 5.6
$ t = table(date, sym, factorNum, factorValue);
$ t;
date |
sym |
factorNum |
factorValue |
---|---|---|---|
2020.09.21 |
MS |
1 |
1.2 |
2020.09.21 |
MS |
2 |
-3.4 |
2020.09.21 |
GS |
1 |
-2.5 |
2020.09.21 |
GS |
2 |
6.3 |
2020.09.22 |
MS |
1 |
1.1 |
2020.09.22 |
MS |
2 |
-3.2 |
2020.09.22 |
GS |
1 |
-2.1 |
2020.09.22 |
GS |
2 |
5.6 |
将表 t 的 date 列和 sym 列作为行索引,factorNum 列作为列索引,查看 factorValue 列的值。
$ select factorValue from t pivot by date, sym, factorNum;
date |
sym |
1 |
2 |
---|---|---|---|
2020.09.21 |
GS |
-2.5 |
6.3 |
2020.09.21 |
MS |
1.2 |
-3.4 |
2020.09.22 |
GS |
-2.1 |
5.6 |
2020.09.22 |
MS |
1.1 |
-3.2 |
返回每个 factorNum 对应的多个指标
$ select factorValue, factorValue>0 as factorSign from t pivot by date, sym, factorNum;
date |
sym |
factorValue_1 |
factorValue_2 |
factorSign_1 |
factorSign_2 |
---|---|---|---|---|---|
2020.09.21 |
GS |
-2.5 |
6.3 |
0 |
1 |
2020.09.21 |
MS |
1.2 |
-3.4 |
1 |
0 |
2020.09.22 |
GS |
-2.1 |
5.6 |
0 |
1 |
2020.09.22 |
MS |
1.1 |
-3.2 |
1 |
0 |