如何在R中按Fixing日期区间汇总DV01并合并两张表？

核心思路 #

先给表2的每个fixing日期生成相邻的下一个fixing日期，明确区间范围，再关联表1筛选出落在该区间内的DT_VCTO记录，最后聚合求和DV01并关联回表2。

具体SQL实现 #

假设表1名为cash_table（字段：DT_VCTO、DV01），表2名为futures_table（字段：fixing_date、contract_code等合约相关字段）：

-- 第一步：给表2的每个fixing日期匹配下一个相邻fixing日期，确定区间
WITH futures_with_ranges AS (
    SELECT 
        *,
        -- 按fixing日期排序，获取下一个日期
        LEAD(fixing_date) OVER (ORDER BY fixing_date) AS next_fixing_date
    FROM futures_table
)
-- 第二步：关联表1，按区间聚合DV01求和
SELECT 
    f.*,
    -- 没有匹配记录时返回0而非NULL
    COALESCE(SUM(c.DV01), 0) AS total_DV01
FROM futures_with_ranges f
LEFT JOIN cash_table c
    -- 匹配DT_VCTO在当前fixing日期到下一个fixing日期之间的记录
    ON c.DT_VCTO >= f.fixing_date
    AND (c.DT_VCTO < f.next_fixing_date OR f.next_fixing_date IS NULL)
-- 分组需包含表2所有非聚合字段，避免分组错误
GROUP BY f.fixing_date, f.next_fixing_date, f.contract_code, f.其他合约字段
ORDER BY f.fixing_date;

关键细节说明 #

1. LEAD()窗口函数：用来自动获取每个fixing日期的下一个相邻日期，最后一条记录的next_fixing_date为NULL，此时区间为fixing_date之后的所有日期（可根据实际业务调整条件）。
2. 区间匹配逻辑：用>=和<确保每个DT_VCTO只会落在一个区间内，避免重复匹配。
3. COALESCE()处理空值：如果某个fixing区间内没有对应的DT_VCTO记录，求和结果返回0，避免出现NULL影响后续分析。
4. 分组注意事项：必须包含表2的所有非聚合字段（比如合约代码、其他属性），否则会出现分组维度错误，导致聚合结果不符合预期。

为什么之前左连接+分组失败？ #

通常是两个原因：

• 未提前明确表2的相邻日期区间，直接左连接会导致一条DT_VCTO记录匹配多个fixing日期，聚合结果重复计算。
• 分组时未覆盖表2的所有必要字段，导致分组维度缺失，出现数据混乱。

内容的提问来源于stack exchange，提问作者André F