计算时间段重叠，并使用聚合查找剩余时间。
我有一个timings表，定义如下：
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
start_time | timestamp without time zone | | not null |
end_time | timestamp without time zone | | not null |
type | integer | | not null |它有这样的数据：
start_time | end_time | type
---------------------+---------------------+------
2020-11-10 23:50:00 | 2020-11-10 23:50:17 | 1
2020-11-11 23:50:17 | 2020-11-11 23:50:34 | 1
2020-11-10 23:50:08 | 2020-11-10 23:50:15 | 2
2020-11-15 23:50:08 | 2020-11-16 23:50:08 | 2计算
对于每个type = 1的日期，我想计算出start_time和end_time之间的时间差。所以对于第1行，时差是17秒。
然后，我想在type = 2的同一日期减去/删除任何时间的重叠。例如，第3行的差为7秒，start_time和end_time都属于第1行的start_time和end_time (它们是相同的日期)。所以我们把7从17拿走，剩下的是10。
如果带有type = 2的行的日期与type = 1中的任何日期不匹配，则可以忽略该行(例如，完全忽略第4行)。
我希望所有日期都能发生这种情况，然后对所有日期的总数进行汇总。在本例中，我将总共得到27，因为我们只需要添加第2行的时间差(17)，所以10 + 17给出了27。
期望输出
type_one_seconds
----------------------
27我以前没有使用过SQL中的时间周期计算，我不知道从哪里开始。
假设
我有一个评论/问题消失了。它问，当我有两行具有相同日期的type = 1时，会发生什么。我必须检查我的数据，然后再给你回电，看看是否会发生这种情况。现在，让我们假设每个type = 1都有一个唯一的日期。所以我们只是总结了他们的时差。但我会检查这个并确保。
我创建了一个大小提琴。
回答 3
Stack Overflow用户
发布于 2021-04-11 13:37:13
您可以尝试将条件拆分为CASE：
SELECT sum(sec) FROM (
SELECT
CASE
WHEN type = 1 THEN
EXTRACT(SECOND FROM end_time-start_time)
WHEN type = 2 AND NOT EXISTS (SELECT 1 FROM timings
WHERE type = 1 AND
(start_time::date = t.start_time::date OR
end_time::date = t.end_time::date)) THEN 0
WHEN type = 2 AND EXISTS (SELECT 1 FROM timings
WHERE start_time < t.start_time AND
end_time > t.end_time AND
type = 1) THEN
EXTRACT(SECOND FROM end_time-start_time) * -1
END AS sec
FROM timings t) j;演示：db<>fiddle
Stack Overflow用户
发布于 2021-04-11 15:01:17
假设type中的行之间没有时间重叠，我将使用tsrange数据类型来利用它的Intersect*和重叠&&操作符：
with ranges as (
select type, tsrange(start_time, end_time) as rng
from timings
), find_neg as (
select n.type, p.rng * n.rng as rng
from ranges p
join ranges n
on n.type = 2
and p.type = 1
and n.rng && p.rng
union all
select type, rng
from ranges
where type = 1
)
select sum(extract(epoch from upper(rng) - lower(rng))) filter (where type = 1)
- sum(extract(epoch from upper(rng) - lower(rng))) filter (where type = 2)
from find_neg;如果在type中存在重叠，那么我将首先使用缺口和孤岛策略来整合它们。
Stack Overflow用户
发布于 2021-04-11 15:07:35
使用表的自连接。
带有type = 1的每一行都将使用LEFT连接与对应的type = 2连接，通过CASE表达式，您可以为每种类型计算end_time和start_time的差异并减去。
函数LEAST()和GREATEST()将用于在与type = 1对应的行的时限内缩小type = 2行的间隔。
最后用SUM()进行聚合
SELECT SUM(
EXTRACT(SECOND FROM t1.end_time - t1.start_time) -
CASE
WHEN t2.type IS NULL THEN 0
ELSE EXTRACT(SECOND FROM LEAST(t1.end_time, t2.end_time) - GREATEST(t1.start_time, t2.start_time))
END
) type_one_seconds
FROM timings t1 LEFT JOIN timings t2
ON t2.start_time::date = t1.start_time::date AND t2.type = 2
WHERE t1.type = 1见演示。
https://stackoverflow.com/questions/67045305
复制相似问题
腾讯云开发者
