PostgreSQL跨两表日期范围匹配的高效SQL实现方案

你当前逐天生成日期序列再CROSS JOIN的写法逻辑正确，但性能瓶颈非常明显：本质是把每个时间区间按天拆成N条重复记录，一段10年的生效记录会凭空生成3600+行中间数据，数据量稍大就会耗尽计算资源。
处理这类由from_date/to_date定义的时态表关联，完全不需要生成日期序列，核心是直接计算两个区间的重叠部分，通过时长加权得到和逐天计算完全一致的结果，性能可以提升2~3个数量级。

核心逻辑 #

两个时间区间能关联的唯一前提是存在重叠，对应判定和计算规则如下：

• 重叠判定：对于员工A的薪资区间[s_from, s_to]和职位区间[t_from, t_to]，满足s_from < t_to AND t_from < s_to即存在重叠（适配你当前样例里左闭右开的区间约定：上一条记录的to_date等于下一条的from_date，不会重复/漏算）
• 重叠区间起点：两个区间起点的较大值 GREATEST(s.from_date, t.from_date)
• 重叠区间终点：两个区间终点的较小值 LEAST(s.to_date, t.to_date)
• 重叠天数：重叠终点 - 重叠起点（PostgreSQL中date类型直接相减即可得到间隔天数）
• 平均薪资计算：按重叠天数做加权平均，即SUM(薪资 * 重叠天数) / SUM(重叠天数)，和逐天展开计算的结果完全等价。

优化后SQL #

SELECT
    t.title,
    ROUND(
        SUM(s.salary * (LEAST(s.to_date, t.to_date) - GREATEST(s.from_date, t.from_date)))
        / SUM(LEAST(s.to_date, t.to_date) - GREATEST(s.from_date, t.from_date))::numeric,
        2
    ) AS avg_salary
FROM salaries s
INNER JOIN titles t
    ON s.emp_no = t.emp_no
    -- 仅匹配存在时间重叠的记录
    AND s.from_date < t.to_date
    AND t.from_date < s.to_date
-- 如果需要限定统计的时间范围，直接在这里加过滤条件即可，无需生成全量日期
-- WHERE GREATEST(s.from_date, t.from_date) >= '1985-01-01'
--   AND LEAST(s.to_date, t.to_date) <= '2003-01-01'
GROUP BY t.title
ORDER BY avg_salary DESC;

优化建议 #

• 索引配置：给salaries和titles表分别创建(emp_no, from_date, to_date)的联合B树索引，关联时可以直接走索引定位匹配记录，千万级数据量也能秒级返回。
• 区间边界适配：如果你的业务里时间区间是双闭区间（即to_date当天也属于生效范围），把重叠判定条件改成s.from_date <= t.to_date AND t.from_date <= s.to_date即可。
• 永久值兼容：样例中用9999-01-01标记当前生效的无固定结束时间的记录，上述逻辑不需要额外特殊处理，LEAST函数会自动取合理的结束时间计算。
• 场景扩展：这个逻辑可以复用到所有时态表关联场景，比如部门人力成本统计、员工编制变动核算、客户等级对应权益计算等，都不需要拆成细粒度时间行。

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