如何高效计算单只股票与其他股票的价格相关性

假设你的股票数据表名为stock_prices，目标股票为'stock_a'，时间区间为'2023-01-01'至'2023-12-31'，可以用以下SQL批量计算相关性：

WITH stock_a_data AS (
    SELECT time_stamp, price
    FROM stock_prices
    WHERE stock_name = 'stock_a'
      AND time_stamp BETWEEN '2023-01-01' AND '2023-12-31'
),
other_stocks_data AS (
    SELECT stock_name, time_stamp, price
    FROM stock_prices
    WHERE stock_name != 'stock_a'
      AND time_stamp BETWEEN '2023-01-01' AND '2023-12-31'
)
SELECT
    o.stock_name,
    CORR(sa.price, o.price) AS correlation_coefficient
FROM stock_a_data sa
JOIN other_stocks_data o ON sa.time_stamp = o.time_stamp
GROUP BY o.stock_name
ORDER BY correlation_coefficient DESC;

• 用CTE预筛选目标股票和其他股票的时间区间数据，避免重复扫描表
• 内连接只保留两者都有价格记录的时间点，保证相关性计算的有效性
• PostgreSQL原生CORR()函数直接计算皮尔逊相关系数，性能高效
• 若存在单时间点多记录的情况，可先按stock_name和time_stamp聚合（如取当日均价）再关联计算

假设你的数据已加载为Pandas DataFramedf，包含stock_name、price、time_stamp字段，可按以下步骤批量计算：

import pandas as pd

# 1. 筛选时间区间数据（确保time_stamp为datetime类型）
time_start = pd.to_datetime('2023-01-01')
time_end = pd.to_datetime('2023-12-31')
filtered_df = df[(df['time_stamp'] >= time_start) & (df['time_stamp'] <= time_end)]

# 2. 转换为宽表：行是时间戳，列是股票名，值为价格
# 若单时间点多记录，先聚合取均值
pivot_df = filtered_df.groupby(['time_stamp', 'stock_name'])['price'].mean().unstack()

# 3. 计算stock_a与其他所有股票的相关性
stock_a_corr = pivot_df.corrwith(pivot_df['stock_a']).drop('stock_a')

# 4. 整理结果格式
result_df = stock_a_corr.reset_index()
result_df.columns = ['stock_name', 'correlation_coefficient']
result_df = result_df.sort_values('correlation_coefficient', ascending=False)

print(result_df)

• 透视表将数据转换为适合相关性计算的格式，corrwith()方法通过向量运算批量计算，比循环逐个计算效率高
• 500只股票的规模在常规内存下即可快速处理，若数据量极大，可改用dask进行分布式分块计算

内容的提问来源于stack exchange，提问作者林彥良