高频数据分析：使用旋转

对于金融市场中的高频数据，每条记录通常保存特定时间戳的股票信息。我们经常需要将一列（或多列的计算结果）重新排列成矩阵或表格，以时间戳作为行标签，以安全 ID 作为列标签。这个操作（称为“透视”）可以通过SQLpivot by关键字或者pivotDolphinDB中的函数来实现。结果可用于矢量化运算以获得最佳性能。 

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

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

首先，用美国股票数据加载“quotes”表。

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（指示性优化投资组合价值）。

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

模拟 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，如下所示：

• 重新排列“quotes”表中的三列（时间戳、符号和价格）以生成一个新表。
• 向前填充新表中的 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