PostgreSQL查询：获取连续日期间增减的ETF成分股Ticker

• 目标：查询特定ETF/指数（index_of_ref为ftse100）在连续交易日之间，成分股（ticker）的新增/删除记录
• 当前问题：使用LEFT JOIN仅能获取新增记录，无法获取删除记录；尝试FULL OUTER JOIN未达预期，查询耗时约10秒

表创建语句 #

CREATE TABLE IF NOT EXISTS public.t_etf_holdings
(
    trade_date date NOT NULL,
    index_of_ref character varying(25) COLLATE pg_catalog."default" NOT NULL,
    ticker character varying(25) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT t_etf_holdings_pkey PRIMARY KEY (trade_date, index_of_ref, ticker)
);

测试数据插入语句 #

INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-06', 'ftse100', 'AAF');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-06', 'ftse100', 'AAL');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-05', 'ftse100', 'AAF');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-05', 'ftse100', 'AAL');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-05', 'ftse100', 'GLEN');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-04', 'ftse100', 'AAF');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-04', 'ftse100', 'AAL');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-04', 'ftse100', 'WPP');

trade_date  ticker  change_type
2024-01-05  GLEN    +
2024-01-04  AAF     +
2024-01-04  AAL     +
2024-01-04  WPP     +

trade_date  ticker  change_type
2024-01-06  GLEN    -
2024-01-05  GLEN    +
2024-01-05  WPP     -
2024-01-04  AAF     +
2024-01-04  AAL     +
2024-01-04  WPP     +

要同时获取新增和删除记录，需将每个交易日的成分股与前一个交易日做全量对比，结合窗口函数和FULL OUTER JOIN实现，同时利用主键索引保证性能：

WITH ranked_dates AS (
    -- 为ftse100的每个交易日生成排序，关联前一日数据
    SELECT 
        trade_date,
        LAG(trade_date) OVER (ORDER BY trade_date) AS prev_trade_date
    FROM (
        SELECT DISTINCT trade_date
        FROM t_etf_holdings
        WHERE index_of_ref = 'ftse100'
        ORDER BY trade_date
    ) AS dates
),
current_holdings AS (
    -- 提取ftse100的所有成分股记录
    SELECT trade_date, ticker
    FROM t_etf_holdings
    WHERE index_of_ref = 'ftse100'
),
holdings_changes AS (
    -- 对比当日与前一日成分股，识别变化
    SELECT
        COALESCE(c.trade_date, r.trade_date) AS trade_date,
        COALESCE(c.ticker, p.ticker) AS ticker,
        CASE
            WHEN c.ticker IS NULL THEN '-'  -- 前一日有、当日无：删除
            WHEN p.ticker IS NULL THEN '+'  -- 前一日无、当日有：新增
        END AS change_type
    FROM ranked_dates r
    LEFT JOIN current_holdings c ON r.trade_date = c.trade_date
    FULL OUTER JOIN current_holdings p ON r.prev_trade_date = p.trade_date AND c.ticker = p.ticker
    WHERE c.ticker IS NULL OR p.ticker IS NULL  -- 仅保留有变化的记录
)
-- 补充最早交易日的新增记录（无前置对比，所有成分股均为新增）
SELECT trade_date, ticker, '+' AS change_type
FROM current_holdings
WHERE trade_date = (SELECT MIN(trade_date) FROM current_holdings)
UNION ALL
SELECT trade_date, ticker, change_type
FROM holdings_changes
WHERE trade_date IS NOT NULL
ORDER BY trade_date DESC, change_type, ticker;

逻辑说明 #

1. ranked_dates：提取ftse100的唯一交易日，用LAG()获取每个交易日的前一个交易日，解决连续交易日关联问题
2. current_holdings：筛选目标指数的所有成分股数据，简化后续关联逻辑
3. holdings_changes：通过FULL OUTER JOIN对比当日与前一日成分股，标记新增/删除状态
4. 补充最早交易日的新增记录：因为该日无前置交易日，所有成分股均为首次纳入

此方案利用表主键索引（trade_date, index_of_ref, ticker）优化关联效率，避免全表扫描，可有效控制查询耗时。

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