利用PostgreSQL表顺序统计包含各时间的时间范围数量

问题背景 #

现有按startdate排序的表logs_bl_sj，结构如下：

需求：对每行r，统计同bundesland下满足x.startdate ≤ r.startdate且r.startdate < x.enddate的行x的数量（即当前行startdate被多少同区域的时间区间包含，至少为1）。已知表已按startdate排序，每行之后的行无需参与计算。

现有查询问题 #

当前使用的查询语句无法得到正确结果：

SELECT bundesland, startdate, COUNT(time_range) FILTER (WHERE time_range @> startdate::timestamp) OVER (PARTITION BY bundesland)
FROM logs_bl_sj_timerange

该语句统计的是整个bundesland分区内符合条件的时间区间总数，而非当前startdate对应的重叠数量，且未利用表已排序的特性，效率低下。

PostgreSQL优化方案 #

利用表已按startdate排序的特性，使用分区窗口函数+范围限制实现高效计算：

SELECT 
  bundesland,
  startdate,
  COUNT(*) FILTER (WHERE enddate > r.startdate) OVER (
    PARTITION BY bundesland 
    ORDER BY startdate 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS overlapping_count
FROM logs_bl_sj r;

优化原理 #

1. 分区限制：通过PARTITION BY bundesland确保只统计同区域的行；
2. 范围限制：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW限定窗口仅包含当前行及之前的所有行（符合“后续行无需参与计算”的要求）；
3. 过滤条件：FILTER (WHERE enddate > r.startdate)筛选出结束时间晚于当前行startdate的区间，即包含当前startdate的有效区间；
4. 排序利用：由于表已按startdate排序，PostgreSQL可直接按顺序扫描，无需额外排序，配合(bundesland, startdate)索引可进一步提升性能。

额外问题：过程式实现是否更优？ #

• 适用场景：当数据量达到千万级以上，且数据库端计算资源有限时，Python等过程式实现可能有优势。具体思路为：
  1. 从数据库读取排序后的全表数据；
  2. 按bundesland分组，维护一个最小堆存储当前未结束的区间enddate；
  3. 遍历每行的startdate，先弹出堆中所有≤当前startdate的enddate（这些区间已不包含当前时间）；
  4. 当前重叠计数即为堆的大小+1（当前行自身）。
• 劣势：需要将全量数据从数据库传输到应用端，增加网络IO开销；代码复杂度高于SQL方案。
• 结论：数据量不大时，优先选择PostgreSQL的SQL方案，无需额外编码，且数据库端处理更高效；超大规模数据时可考虑过程式实现。

内容的提问来源于stack exchange，提问作者rob.loh