从 SQL 数据库中存储的使用信息查询重叠时段
我在 PostgreSQL 数据库中有一个表，用于跟踪各种资源的使用情况。该表的（简化）架构是每行都有一个 ResourceID、StartTime 时间戳和 EndTime 时间戳。表中的每一行代表资源使用的时间跨度，因此表可能如下所示：（注意，时间戳还包括日期，为清楚起见，在下面删除了日期）
ResourceID StartTime EndTime
---------------------------------------
1 12:30:00 12:45:00
1 12:48:25 12:50:22
2 12:32:50 12:33:44
数据库可能会跟踪一千个不同的资源和几百万个资源表中的行。我最近收到了一个新报告的功能请求，该报告详细说明了一组资源都在使用中的时间段，因此查询可能是“在 12:00 到 15:00 之间，显示资源 1 的所有时间段” 、2、5、8和12都在使用中”。此外，查询应该有一个“最小空闲”时间段，资源在被视为空闲之前需要空闲该时间段（例如：如果最小空闲时间为 2 秒，则资源正在使用 12:00:00-12:01 :00 和 12:01:01 到 12:02:00 不会被视为有任何空闲时间，即使从技术上讲它没有使用 1 秒）。
查询的输出将是所有查询资源正在使用时的所有时间的开始时间/结束时间的列表。从那时起，我需要计算该数据集的一些统计数据，这对我来说不是问题，但我不知道如何从原始表有效地创建该数据集。如果有必要，我可以在插入时将附加信息记录到数据库，如果不是任意资源子集要求，我可以创建一个包含所有空闲时间的表，但包含 1000 个不同的资源和 1-1000 的任何可能组合查询中的资源，这似乎过多，因为只会报告极少数的组合。
预先感谢您的任何帮助或见解。
I have a table in a PostgreSQL database which tracks usage of various resources. The (simplified) Schema of the table is that each row has a ResourceID, StartTime Timestamp and EndTime Timestamp. Each row in the table represents a timespan in which the resource was in use, so the table might look like: (Note, timestamps also include dates, removed below for clarity)
ResourceID StartTime EndTime
---------------------------------------
1 12:30:00 12:45:00
1 12:48:25 12:50:22
2 12:32:50 12:33:44
The database would have perhaps a thousand different resources tracked and a few million rows in the table. I've recently received a feature request for a new report which details time periods in that a group of resources are all in use, so the query might be "Between 12:00 and 15:00, display all the time periods when resources 1,2,5,8 and 12 were all in use". In addition,the query should have a "Minimum Idle" period, which a resource needs to be idle for before being considered idle, (example: If Minimum Idle is 2 seconds, a resource in use 12:00:00-12:01:00 and 12:01:01 to 12:02:00 would not be considered to have any idle time,even though technically it was not in use for 1 second).
The output of the query would be a list of starttime/endtimes of all times when all the queried resources were in use. From that point, I need to compute some statistics on that dataset, which won't be a problem for me, but I'm at a loss on how to efficiently create that dataset from the original table. If necessary I can log additional information to the database at insert time, and if not for the arbitary resource subset requirement, I could just create a table of all the idle times then, but with 1000 different resources and any possible combination of 1-1000 resources in a query, that seems excessive as only a very small number of combinations will ever be reported on.
Thanks in advance for any help or insights.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对于使用周期
使用 PostgreSQL 9.2 中的范围类型并检查您拥有的任何周期之间的重叠。您可以采用多个重叠的片段，以便逐步缩小范围。
这不是很简单，所以恐怕我没有一个简单的例子。
对于空闲期：
我认为您需要使用某种间隔类型（9.2 中的新类型在这里会很有帮助）来执行此操作，或者创建一个可用于查询目的的类似类型。请注意，我所做的这一切并不是微不足道的。
您要做的第二件事是创建一个自定义聚合来比较和添加间隔。它需要返回这些类型的数组。最后，您需要能够迭代地解决差异。
这里没有简单的解决方案。所涉及的代码比您可能想要的更复杂，并且它比通常从此处的答案中获得的代码还要多。这涉及大量的逻辑和设计工作。这是很有可能的，但并不是非常简单。
For usage periods
Use a range type from PostgreSQL 9.2 and check for overlap across whatever periods you have. You can take multiple overlapping segments so you can whittle down ranges progressively.
This is not quite trivial so I am afraid I don't have a simple example.
For idle periods:
I think you'd want to do this with some sort of interval type (the new types in 9.2 would be helpful here) or create a similar type you could use for query purposes. Note that where I have done this, it has not been trivial.
The second thing you'd want to do is create a custom aggregate to compare and add intervals. It would need to return an array of these types. Finally you will need to be able to iteratively run through differences.
There isn't a simple solution here. The code involved is more complex than you'd probably like, and it will be more than one would typically get from an answer here. There's a significant amount of logic involved and design effort involved. It is quite possible, but it isn't extremely simple.