For high-frequency data in financial markets, each record typically holds the information of a stock at a specific timestamp. We often need to rearrange a column (or the calculation results involving multiple columns) into a matrix or table with the timestamps as row labels and security IDs as column labels. This operation (referred to as “pivoting”) can be achieved with the SQL pivot by keyword or the pivot function in DolphinDB. The result can be used in vectorized operations for optimal performance.
1. Calculating Pairwise Correlations of Stock Returns
In pairs trading and hedging, we often need to calculate the pairwise correlations of multiple securities. Traditional databases are not able to perform such complex calculations. Using statistical software would require data migration between systems, which can be very time-consuming with a large amount of data. In DolphinDB, pairwise correlation can be calculated with the help of SQL pivot by clause.