如何查询PostgreSQL表中每日仅含O/C单状态的ISIN？

需求回顾 #

给定表index_constituents，需按日期维度检查每个ISIN的opening_closing状态（仅O/C两种）：若某ISIN在某一日期下仅存在其中一种状态，则返回该ISIN；若同时存在两种状态则排除。

基础解决方案 #

1. 获取所有状态不完整的(date, ISIN)组合 #

该查询直接返回每个日期下状态缺失的ISIN及其对应日期：

SELECT date, ISIN
FROM index_constituents
GROUP BY date, ISIN
HAVING COUNT(DISTINCT opening_closing) = 1;

2. 获取去重后的ISIN列表 #

若只需得到所有存在至少一个日期状态不完整的ISIN（去重后），可使用嵌套查询：

SELECT DISTINCT ISIN
FROM (
    SELECT date, ISIN
    FROM index_constituents
    GROUP BY date, ISIN
    HAVING COUNT(DISTINCT opening_closing) = 1
) AS incomplete_records;

逻辑说明 #

• 按date和ISIN分组：确保我们检查的是同一日期下的同一ISIN的状态完整性
• COUNT(DISTINCT opening_closing)：统计当前分组内的状态种类数，等于1说明仅存在O或仅存在C
• 外层DISTINCT：避免同一ISIN因多个日期状态不完整而重复出现

性能优化建议 #

针对13000+行、2000+日期的数据规模，创建复合索引可大幅提升分组统计效率：

CREATE INDEX idx_date_isin_opening ON index_constituents (date, ISIN, opening_closing);

该索引能让PostgreSQL直接通过索引完成分组和去重统计，无需扫描全表。

示例验证 #

基于提供的示例数据，运行第二个查询将返回：

FOHOAS
JIOFHS
HIFHSA

（注：示例中HIFHSA在2020-01-02仅存在C状态，符合返回条件）

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