如何对自连接查询所得行执行运算 计算同时间不同金融资产价格比值

前置说明 #

• 金融数据表表名假设为financial_data，使用时替换为你的实际表名
• 两个待查询资产的sourceId分别为asset1_id、asset2_id，执行前替换为实际取值
• 目标查询的时间周期值为target_timeframe，执行前替换为实际的timeframe取值

实现逻辑 #

将原表通过自连接拆分为两个独立的数据源，分别筛选对应资产、对应时间周期的记录，再通过Unixtime字段关联相同时间戳的记录，计算价格比值后按时间戳升序排序即可。

关联条件同时匹配Unixtime和timeframe，可以避免不同周期的同时间戳数据错误关联，保证结果准确性。

可直接使用的SQL示例 #

SELECT
    t1.Unixtime,
    t1.open / t2.open AS `Asset1open/asset2open`,
    t1.close / t2.close AS `Asset1close/asset2close`
FROM
    financial_data t1
INNER JOIN
    financial_data t2
    ON t1.Unixtime = t2.Unixtime
    AND t1.timeframe = t2.timeframe
WHERE
    t1.sourceId = 'asset1_id'
    AND t2.sourceId = 'asset2_id'
    AND t1.timeframe = 'target_timeframe'
ORDER BY
    t1.Unixtime ASC

可选优化 #

• 如果需要保留资产1有记录但资产2无对应时间戳记录的行，将INNER JOIN替换为LEFT JOIN即可，无匹配的比值字段返回NULL
• 为避免资产2价格为0触发计算错误，可对分母加NULLIF处理，示例：t1.open / NULLIF(t2.open, 0)，分母为0时比值返回NULL

内容的提问来源于stack exchange，提问作者mark