对于金融市场中的高频数据，每条记录通常在特定时间戳保存股票的信息。我们经常需要将一列（或涉及多个列的计算结果）重新排列成矩阵或表，其中时间戳作为行标签，安全ID作为列标签。此操作（称为“透视”）可以使用 SQL 关键字或海豚数据库中的函数来实现。结果可用于矢量化操作以获得最佳性能。pivot bypivot
1. 计算股票回报的成对相关性
在货币对交易和对冲中，我们经常需要计算多种证券的成对相关性。传统数据库无法执行如此复杂的计算。使用统计软件需要在系统之间进行数据迁移，这对于大量数据来说可能非常耗时。在海豚数据库中，可以在 SQL 子句的帮助下计算成对相关性。pivot by
首先，加载包含美国股票数据的“报价”表。
quotes = loadTable(“dfs://TAQ”, “quotes”)复制
选择 2009 年 4 月 8 日报价最多的 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]复制
将透视 by 子句与聚合函数 avg 结合使用，可将原始数据缩减为分钟级数据。关键字生成一个矩阵，其中股票 ID 作为列标签，分钟作为行标签。exec
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)复制
上述脚本从“报价”表的 2693 亿条记录中选取 2009 年 4 月 8 日的近 1.9 亿条记录，以计算 500 只股票回报的成对相关性。只需 2629.85 毫秒即可完成计算。
我们可以对 corrMAT 矩阵运行以下查询：
- 对于每只股票，选择相关性最高的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
复制- 选择与“SPY”相关性最高的10只股票：
select * from mostCorrelated where sym=’SPY’ order by corr desc
复制2. IOPV计算
在回溯测试指数套利策略时，我们需要计算指数或ETF的IOPV（指示性优化投资组合价值）。
为简单起见，我们假设ETF有2个成分股，AAPL和FB。成分的权重保存在“权重”字典中。此示例中使用纳秒时间戳。
交易所买卖基金的模拟数据：
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指数）。如果回溯测试的时间段很长，涉及数亿甚至数十亿行，使用传统的统计系统将生成一个比原始表大得多的中间表。这可能会导致内存不足并降低性能。
上述步骤可以在海豚数据库中完成，只需一个带有 . 不仅需要的脚本要少得多，而且性能也得到了显着提高。不会生成中间表，从而避免内存不足问题。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复制
原文标题：High-Frequency Data Analysis: Working With Pivoting
原文作者:Davis Zhou
原文地址：https://dzone.com/articles/high-frequency-data-analysis-working-with-pivoting
