高频数据分析：使用数据透视

1. 计算股票收益的成对相关性

在配对交易和对冲中，我们经常需要计算多个证券的配对相关性。传统数据库无法执行如此复杂的计算。使用统计软件将需要系统之间的数据迁移，这对于大量数据可能非常耗时。在 DolphinDB 中，可以借助 SQLpivot by子句计算成对相关性。

首先，使用股票数据加载“报价”表。

quotes = loadTable(“dfs://TAQ”, “quotes”)

选择 2009 年 8 月 4 日报价数量最多的 500 只股票：

dateValue=2009.08.04
num=500
syms = (exec count(*) from quotes where date = dateValue, time between 09:30:00 : 15:59:59, 0<bid, bid<ofr, ofr<bid*1.1 group by Symbol order by count desc).Symbol[0:num]

使用 pivot by 子句和聚合函数 avg 将原始数据下采样为分钟级数据。该exec关键字生成一个矩阵，其中股票 ID 作为列标签，分钟作为行标签。

priceMatrix = exec avg(bid + ofr)/2.0 as price from quotes where date = dateValue, Symbol in syms, 0<bid, bid<ofr, ofr<bid*1.1, time between 09:30:00 : 15:59:59 pivot by time.minute() as minute, Symbol

将价格矩阵转换为股票收益矩阵：

retMatrix = ratios(priceMatrix)-1

使用函数corrMatrix计算成对相关：

corrMAT = corrMatrix(retMatrix)

上述脚本从“quotes”表的 2693 亿条记录中选取 08/04/2009 的近 1.9 亿条记录，计算 500 只股票收益的成对相关性。完成计算仅需 2629.85 毫秒。

我们可以在 corrMAT 矩阵上运行以下查询：

1. 对于每只股票，选择相关性最高的 10 只股票：

mostCorrelated = select * from table(corrMAT.columnNames() as sym, corrMAT).unpivot(`sym, syms).rename!(`sym`corrSym`corr) context by sym having rank(corr,false) between 1:10

2.选择与“SPY”相关性最高的10只股票：

select * from mostCorrelated where sym=’SPY’ order by corr desc

2. IOPV 计算

在回测指数套利策略时，我们需要计算指数或ETF的IOPV（Indicative Optimized Portfolio Value）。

为简单起见，我们假设 ETF 有 2 个成分股，AAPL 和 FB。成分的权重保存在“权重”字典中。此示例中使用了纳秒时间戳。

模拟 ETF 的数据：

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 weightedPrice from quotes
select last(weightedPrice) from ETF pivot by Time, Symbol;

上面的脚本创建表 ETF 并将其重新排列到一个带有pivot by子句的新表中：

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

传统的统计系统会在每个时间戳计算索引的 IOPV，如下所示：

• 重新排列“报价”表中的三列（时间戳、符号和价格）以生成新表。
• 向前填充新表中的 NULL。
• 总结每行成分股的加权价格。

由于美国市场使用纳秒时间戳，因此不同股票具有相同时间戳的记录是非常罕见的。此外，指数通常由大量成分组成（例如，标准普尔 500 指数）。如果回测的时间周期很长，涉及数亿甚至数十亿行，使用传统的统计系统会生成一个比原表大很多的中间表。这可能会导致内存不足并降低性能。

上述步骤可以在 DolphinDB 中完成，只需一个带有 pivot by. 不仅需要更少的脚本，而且性能也显着提高。不生成中间表，从而避免内存不足的问题。

select rowSum(ffill(last(weightedPrice))) 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