DuckDB中处理带循环引用的关联表问题方案咨询

针对你遇到的证券标的关联（包括期权基于期权的场景）问题，推荐采用统一主表+子表关联的模型，完全适配DuckDB特性，同时解决循环引用、扩展性和报价关联的问题。

核心设计思路 #

创建一个全局的security主表作为所有证券的唯一标识入口，每种证券类型（股票、期权、期货、债券）对应独立的子表，通过主表的id关联。标的关系直接引用主表的id，无需区分标的类型，自然解决循环引用问题。

步骤1：定义枚举类型（可选但推荐） #

先定义证券类型和期权类型的枚举，保证数据一致性：

CREATE TYPE SecurityType AS ENUM ('STOCK', 'OPTION', 'FUTURE', 'BOND');
CREATE TYPE OptionType AS ENUM ('CALL', 'PUT');

步骤2：创建全局主表security #

所有证券共享这个主表的主键id，记录通用属性：

CREATE TABLE security (
    id INTEGER PRIMARY KEY,
    type SecurityType NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 可选：记录创建时间
);

步骤3：创建各证券子表 #

每个子表通过security_id关联主表，并添加约束保证类型匹配：

股票表

CREATE TABLE stock (
    security_id INTEGER PRIMARY KEY REFERENCES security(id),
    ticker VARCHAR NOT NULL UNIQUE,
    name VARCHAR NOT NULL,
    exchange VARCHAR,
    -- 约束：确保对应的主表记录类型为STOCK
    CHECK ((SELECT type FROM security WHERE id = security_id) = 'STOCK')
);

期权表（支持标的为任意证券类型）

CREATE TABLE option (
    security_id INTEGER PRIMARY KEY REFERENCES security(id),
    underlying_security_id INTEGER NOT NULL REFERENCES security(id), -- 直接关联主表id，支持期权作为标的
    option_type OptionType NOT NULL,
    strike_price DECIMAL NOT NULL,
    strike_date DATE NOT NULL,
    -- 约束：确保对应的主表记录类型为OPTION
    CHECK ((SELECT type FROM security WHERE id = security_id) = 'OPTION'),
    -- 可选约束：禁止期权以自身为标的
    CHECK (security_id != underlying_security_id)
);

期货表

CREATE TABLE future (
    security_id INTEGER PRIMARY KEY REFERENCES security(id),
    underlying_security_id INTEGER REFERENCES security(id), -- 标的可为任意证券
    contract_size DECIMAL NOT NULL,
    expiration_date DATE NOT NULL,
    CHECK ((SELECT type FROM security WHERE id = security_id) = 'FUTURE')
);

债券表

CREATE TABLE bond (
    security_id INTEGER PRIMARY KEY REFERENCES security(id),
    cusip VARCHAR NOT NULL UNIQUE,
    face_value DECIMAL NOT NULL,
    coupon_rate DECIMAL NOT NULL,
    maturity_date DATE NOT NULL,
    CHECK ((SELECT type FROM security WHERE id = security_id) = 'BOND')
);

步骤4：创建统一的报价表 #

所有证券的每日报价只需关联主表id，无需区分类型：

CREATE TABLE daily_quote (
    security_id INTEGER REFERENCES security(id) NOT NULL,
    quote_date DATE NOT NULL,
    open_price DECIMAL,
    high_price DECIMAL,
    low_price DECIMAL,
    close_price DECIMAL,
    volume BIGINT,
    PRIMARY KEY (security_id, quote_date)
);

方案优势 #

• 解决循环引用：标的关系直接指向主表id，期权可以轻松关联另一期权的security_id，无需额外映射表。
• 扩展性强：新增证券类型时，只需添加对应子表并更新SecurityType枚举，无需修改现有表结构。
• 查询高效：通过主表统一关联，查询跨类型证券或报价时，只需关联主表即可；可创建视图简化多表查询（示例如下）。
• 数据一致性：通过CHECK约束保证子表与主表的类型匹配，避免无效数据。

可选：创建统一视图简化查询 #

为了方便查询所有证券的完整信息，可创建视图：

CREATE VIEW all_securities AS
SELECT 
    s.id,
    s.type,
    s.created_at,
    st.ticker,
    st.name,
    st.exchange,
    o.underlying_security_id,
    o.option_type,
    o.strike_price,
    o.strike_date,
    f.contract_size,
    f.expiration_date,
    b.cusip,
    b.face_value,
    b.coupon_rate,
    b.maturity_date
FROM security s
LEFT JOIN stock st ON s.id = st.security_id
LEFT JOIN option o ON s.id = o.security_id
LEFT JOIN future f ON s.id = f.security_id
LEFT JOIN bond b ON s.id = b.security_id;

查询时直接使用SELECT * FROM all_securities WHERE type = 'OPTION'即可获取所有期权信息，包含标的关联。

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