如何遍历日期范围并针对每个日期执行PostgreSQL查询

你可以通过分组聚合或者关联查询的方式一次性实现需求，无需手动遍历每个日期执行多次查询，这也是SQL的最优实践：

方法一：分组聚合（推荐） #

通过按date和id分组，判断每个分组内是否存在opening_closing = 'O'且不存在opening_closing = 'C'：

-- 保留所有日期下符合条件的id（包括同一id在多个日期符合的情况）
SELECT id
FROM index_constituents
GROUP BY date, id
HAVING 
  SUM(CASE WHEN opening_closing = 'O' THEN 1 ELSE 0 END) > 0
  AND SUM(CASE WHEN opening_closing = 'C' THEN 1 ELSE 0 END) = 0;

如果需要对最终结果的id去重（同一个id即使在多个日期符合条件也只出现一次），只需添加DISTINCT：

SELECT DISTINCT id
FROM index_constituents
GROUP BY date, id
HAVING 
  SUM(CASE WHEN opening_closing = 'O' THEN 1 ELSE 0 END) > 0
  AND SUM(CASE WHEN opening_closing = 'C' THEN 1 ELSE 0 END) = 0;

方法二：EXISTS关联查询 #

通过EXISTS和NOT EXISTS分别验证每个(id,date)对是否满足条件：

-- 保留所有符合条件的id（含重复）
SELECT ic.id
FROM index_constituents ic
WHERE 
  EXISTS (
    SELECT 1 FROM index_constituents ic_o
    WHERE ic_o.id = ic.id AND ic_o.date = ic.date AND ic_o.opening_closing = 'O'
  )
  AND NOT EXISTS (
    SELECT 1 FROM index_constituents ic_c
    WHERE ic_c.id = ic.id AND ic_c.date = ic.date AND ic_c.opening_closing = 'C'
  );

-- 去重版本
SELECT DISTINCT ic.id
FROM index_constituents ic
WHERE 
  EXISTS (
    SELECT 1 FROM index_constituents ic_o
    WHERE ic_o.id = ic.id AND ic_o.date = ic.date AND ic_o.opening_closing = 'O'
  )
  AND NOT EXISTS (
    SELECT 1 FROM index_constituents ic_c
    WHERE ic_c.id = ic.id AND ic_c.date = ic.date AND ic_c.opening_closing = 'C'
  );

逻辑说明 #

两种方法本质都是对每个(id, date)组合做判断：

1. 该id在当前日期下有opening_closing = 'O'的记录
2. 该id在当前日期下没有opening_closing = 'C'的记录
   然后将所有符合条件的id汇总输出。

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