利用PostgreSQL查询中表的顺序来计算包含每次时间的时间范围

您可以通过自加入来实现这一点(假设为id公钥，以缩短group by).每一行都将与其自身以及具有匹配time_range:Demo at db<>fiddle:的同一bundesland中的任何其他行相连

select a.*,count(*)
from logs_bl_sj_timerange a
join logs_bl_sj_timerange b 
  on a.bundesland=b.bundesland
 and b.time_range @> a.startdate
group by a.id;

相关纯量子查询，几乎相当于Cetin Basoz' idea:从每一行中，它会查看同一bundesland中的其他行，并根据time_range的匹配情况对它们进行计数.

select *,(select count(*)
          from logs_bl_sj_timerange s2
          where s1.bundesland = s2.bundesland
            and s1.startdate >= s2.startdate
            and s1.startdate < s2.enddate)
from logs_bl_sj_timerange s1

一些 comments :

1. 我有一个表logs_BL_sj，它按startDate排序:

   请注意表的顺序是多么有用:对于每一行，该行后面的行将不计数，因此甚至不应该判断.

   除非你有select件事并添加order by条款，否则table order的概念不适用.表可以按照some的顺序物理写入，您可以通过设置索引并要求数据库将表按cluster强制执行，但除非您将order by添加到select，否则SQL既不保证任何行顺序，甚至不必考虑任何操作.

   如果您观察到表的访问方式存在某种模式，那么您就不能依赖它.在任何时候，数据库都可以自由地重新排序二元组，它总是可以以任何方式自由地读取它们，并在整个查询执行过程中重新排序它们，只要它发现有用就可以多次.

2. 您的聚合/窗口filter条款中的time_range @> startdate条件指的是来自完全相同行(当前由count()考虑的行)的值，使其始终为真.如果startdate左右来自外部查询上下文中的当前行，而time_range是从窗框中的其他行获取的，那么您的 idea 就会有效.

   请注意，其中没有这样的区分，至于哪个是哪个，这给了你一个暗示，它不会像那样工作，因为如果没有这种区分，它就不可能像那样工作.

3. 通过程序方式(例如在Python中)完成这件事会更好吗？迭代排序的开始日期，可以保留运行计数，该计数根据存储的结束日期数组而变化..而PostgreSQL必须为每一行开始新的计数.

   它不会.如果您真的不想信任PostgreSQL planner/optimizer并强制以某种方式收集结果，那么您可以使用recursive CTE.您还可以求助于PL/pgSQL，甚至还有PL/Python，而无需离开DB一步.我甚至认为您不需要其中任何一个，更不用说手动输入命令如何获得您可以声明性地告诉数据库的结果，只给您，并让它提出创建、访问和处理所有相关数据 struct 的最佳方法.

   放弃数据库规划器/优化器提供的内容并按命令处理事情与放弃编译器并编写自己的低级代码没有什么不同.有时这确实是有道理的，但最好首先真正确保它有道理，我认为这不是其中之一.DB自动执行很多事情，您必须重新实现才能接近其性能和可靠性.