为SAFEX期权交易SQL查询添加OI(t-1)列获取前一日持仓量

要获取前一日持仓量（OI(t-1)），核心是通过匹配目标合约的前一日数据，以下提供两种可行方案：

方案一：表自关联查询 #

通过将tbl_SAFEX_EDM_Statistics表与自身关联，匹配当前日期的前一日数据：

SELECT
    ot.*,
    s.*,
    s_prev.OpenInterest AS `OI(t-1)`
FROM tbl_SAFEX_EDM_Options_Traded ot
JOIN tbl_SAFEX_EDM_Statistics s 
    ON ot.ContractID = s.ContractID 
    AND s.TradeDate = '20230727'
LEFT JOIN tbl_SAFEX_EDM_Statistics s_prev
    ON ot.ContractID = s_prev.ContractID 
    AND s_prev.TradeDate = '20230726' -- 直接指定前一日日期，或用DATE_SUB函数（需根据字段类型调整）
-- 保留原查询的指标计算、排序逻辑
ORDER BY ot.ContractType, ot.StrikePrice;

• 补充：若TradeDate是日期类型，可替换为DATE_SUB('20230727', INTERVAL 1 DAY)；用LEFT JOIN避免前一日无数据时丢失当前行，如需空值转0，可改为COALESCE(s_prev.OpenInterest, 0) AS OI(t-1)``。

方案二：LAG()窗口函数提取 #

如果表中存在该合约的连续日期数据，用窗口函数直接提取上一日持仓量：

SELECT
    ot.*,
    s.*,
    LAG(s.OpenInterest) OVER (PARTITION BY s.ContractID ORDER BY s.TradeDate) AS `OI(t-1)`
FROM tbl_SAFEX_EDM_Options_Traded ot
JOIN tbl_SAFEX_EDM_Statistics s 
    ON ot.ContractID = s.ContractID 
    AND s.TradeDate BETWEEN '20230726' AND '20230727'
WHERE s.TradeDate = '20230727'
-- 保留原查询的指标计算、排序逻辑
ORDER BY ot.ContractType, ot.StrikePrice;

• 说明：PARTITION BY s.ContractID确保按单个合约分组，ORDER BY s.TradeDate保证日期顺序正确，最终仅筛选当日数据即可得到对应前一日持仓量。

关键注意点 #

• 确认TradeDate字段类型：字符串类型需保证日期格式完全匹配，日期类型用日期函数处理更稳妥。
• 若同一合约同一日期有多条数据，需先对tbl_SAFEX_EDM_Statistics做聚合（如SUM(OpenInterest)）再关联或使用窗口函数。

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