DolphinDB使用案例5：透视数据pivot

• 数据维度

  DolphinDB提供了模板函数pivot，unpivot和SQL语句pivot by来改变数据维度。

• 构建表

  syms=`BIDU`MSFT`ORCL$symbol
  sym=syms[0 0 0 0 0 0 0 1 1 1 1 1 1 1 2 2 2 2 2 2 2]
  price=172.12 170.32 172.25 172.55 175.1 174.85 174.5 36.45 36.15 36.3 35.9 36.5 37.15 36.9 40.1 40.2 40.25 40.15 40.1 40.05 39.95
  qty=100* 10 3 7 8 25 6 10 4 5 1 2 8 6 10 2 2 5 5 4 4 3
  trade_time=09:40:00+1 30 65 90 130 185 195 10 40 90 140 160 190 200 5 45 80 140 170 190 210
  t1=table(sym, price, qty, trade_time)
  

• 模板函数pivot

• 模板函数

  模板是一种内置的高阶函数，可以扩展、修饰和增强函数或者运算符的功能。

  模板以函数和某些对象作为输入内容，作用类似于一个函数和数据之间的管道。

  通常，输入数据首先以一种预设的方式被分解成多个数据块（可能重叠），然后单独的数据块同时用于给定的函数并产生结果，最后单独的结果被组装成一个对象返回。

  模板的输入数据可以是向量、矩阵或者表，也可以是标量和字典。

  通过使用模板，很多数据总结和分析任务可以用几行代码就可以高效地完成。

  模板总是与运算符、用户自定义函数或者系统函数共同使用。

  **所有的模板符号以冒号开头，之后紧跟一个大写字母。 **

  所有模板的第一个参数必须是一个函数名，不可以是一个函数名数组。如果想要在多个函数上运行多个模板，需要在模板中使用 call 模板。

  分解规则：一个向量被分解成多个标量，一个矩阵被分解成多列（向量），一个表被分解成多行（字典）。

  组装规则：标量类型合并组成一个向量，向量合并成一个矩阵，字典合并成一张表。

  遍历规则：按元素遍历向量，按列遍历矩阵，按行遍历表。

• pivot重组数据

  pivot(func, funcArgs, rowAlignCol, colAlignCol)
  

  参数	意义	备注
  func	一个聚合函数
  funcArgs	func的参数	如果func有多个参数，那么它是一个元组
  rowAlignCol	行的分组变量
  colAlignCol	列的分组变量

  rowAlignCol，colAlignCol和funcArgs中的每个参数都是相同长度的向量。

  在二维维度上重组数据，转换成一个新的结果。

  假设rowAlignCol有n个不同元素，colAlignCol有m个不同元素。该模板将会返回n行m列的矩阵，colAlignCol的唯一值作为行标签，rowAlignCol的唯一值作为列标签。

  对于矩阵的每一个元素，将指定函数应用到rowAlignCol和colAlignCol所指定的矩阵单元格的元素上。

• 重构为矩阵

  1. 计算每个股票每分钟内的观察数

  pivot(count, price, minute(trade_time), sym)
  # 解析：
  # count是函数，计算数量；
  # price是count的参数，即计算price这一列的数量；
  # minute(trade_time)（的唯一值）表示行标签，minute是函数，计算参数所在分钟；
  # sym（的唯一值）是列标签；
        BIDU MSFT ORCL
        ---- ---- ----
  09:40m|2    2    2
  09:41m|2    1    1
  09:42m|1    2    2
  09:43m|2    2    2
  

  2. 每个股票每分钟内的最新价格

  pivot(last, price, minute(trade_time), sym)
  # 解析：
  # last是函数，表示序列的最后一个元素
        BIDU   MSFT  ORCL
        ------ ----- -----
  09:40m|170.32 36.15 40.2
  09:41m|172.55 36.3  40.25
  09:42m|175.1  36.5  40.1
  09:43m|174.5  36.9  39.95
  

• SQL语句pivot by

• pivot by

  pivot by 是DolphinDB的独有功能，是对标准SQL的拓展。

  它按照两个维度将表中某列的内容重新整理（可以使用数据转换函数），

  和select子句在一起使用时返回一个表，

  和exec语句一起使用时将转换成一个矩阵。

• 查看每个股票每分钟的最新价格

  select last(price) from t1 pivot by minute(trade_time), sym
  
  minute_trade_time BIDU   MSFT  ORCL
  ----------------- ------ ----- -----
  09:40m            170.32 36.15 40.2
  09:41m            172.55 36.3  40.25
  09:42m            175.1  36.5  40.1
  09:43m            174.5  36.9  39.95
  

  pivot by需要与select等语句一起使用

• unpivot 把两列重组成一列

  unpivot(obj, keyColNames, valueColNames, [func])
  

  参数	意义	备注
  obj	一个表
  keyColNames	表示列名的标量或向量	用于指定要在输出表中保留的列
  valueColNames	一个列名向量	valueColNames中的数据将转换成一列，如果指定了func函数，该函数将会应用到valueColNames上。
  func	一个函数

  把多列数据转换成一列，valueColNames中的列名组成了结果中的valueType，keyColNames中的值会重复多次形成同名的列。

• 计算每日股票的相关性的均值

  x = 2017.03.27..2017.03.31
  date = take(x, 20)
  symbol = take(`A`B`C`D, 20).sort()
  returns = 0.0121 0.0059 -0.0215 -0.004 0.0312 0.0019 0.0004 -0.0321 -0.0098 -0.0001 0.0003 0.0078 -0.0276 -0.0021 0.0219 0.0087 -0.0003 -0.0127 -0.0022 0.0186
  t = table(date, symbol, returns)
  t
  
  date	symbol	returns
  -----------------------
  2017.03.27	A	0.0121
  2017.03.28	A	0.0059
  2017.03.29	A	(0.0215) # 用()表示负数
  2017.03.30	A	(0.004)
  2017.03.31	A	0.0312
  2017.03.27	B	0.0019
  2017.03.28	B	0.0004
  2017.03.29	B	(0.0321)
  2017.03.30	B	(0.0098)
  2017.03.31	B	(0.0001)
  2017.03.27	C	0.0003
  2017.03.28	C	0.0078
  2017.03.29	C	(0.0276)
  2017.03.30	C	(0.0021)
  2017.03.31	C	0.0219
  2017.03.27	D	0.0087
  2017.03.28	D	(0.0003)
  2017.03.29	D	(0.0127)
  2017.03.30	D	(0.0022)
  2017.03.31	D	0.0186
  

• 用pivot将returns转成矩阵

  retMat = pivot(last, t.returns, t.date, t.symbol);
  retMat;
            A       B       C       D
            ------- ------- ------- -------
  2017.03.27|0.0121  0.0019  0.0003  0.0087
  2017.03.28|0.0059  0.0004  0.0078  -0.0003
  2017.03.29|-0.0215 -0.0321 -0.0276 -0.0127
  2017.03.30|-0.004  -0.0098 -0.0021 -0.0022
  2017.03.31|0.0312  -0.0001 0.0219  0.0186
  

• 计算任意两个股票之间的相关性

  • 模板函数cross

    cross(func, X, [Y])
    # 或
    X <operator>:C Y
    # 并行版本
    pcross
    

    参数	意义	备注
    func	一个二元函数
    X	可以是数据对、向量或矩阵	X和Y可以有不同的数据形式、长度、维度
    Y	可选参数	如果不指定，将会执行cross(func,X,X),func必须是对称二元函数

    假设X有m个元素或m列，Y有n个元素或n列，如果func(X[i], Y[j])是标量，将返回一个m×n矩阵；

    如果func(X[i], Y[j])是向量，将返回一个长度为m的元组，每个元素是一个长度为n的元组。

  • 计算相关性

    corrs = retMat corr :C retMat
    corrs
    
     A        B        C        D
     -------- -------- -------- --------
    A|1        0.844171 0.945889 0.98557
    B|0.844171 1        0.882214 0.800108
    C|0.945889 0.882214 1        0.891941
    D|0.98557  0.800108 0.891941 1
    

  • 将相关矩阵的所有对角线元素设置为NULL

    for(i in 0:rows(corrs)) corrs[i,i]=NULL
    
           label	A	B	C	D
    ------------------------------
    A	1	0.8442	0.9459	0.9856
    B	0.8442	1	0.8822	0.8001
    C	0.9459	0.8822	1	0.8919
    D	0.9856	0.8001	0.8919	1
    

  • 计算股票相关性的均值

    corrs.flatten().avg()
    0.891649