因此，我的目标是能够以小时为单位计算在某些活动上花费的时间。
我的数据包含：某个活动的开始和该活动的结束，例如，我知道有人从"2019-01-09 17:04:34"休息到"2019-001-09 19:55:03"。
我的目标是计算出这个人在"17-18"中场休息时花了55分钟，在"18-19"中场休息了60分钟，在"19-20"中场休息了55分钟。
我的想法是始终拆分源，这样对于包含活动的开始和结束的行，我将接收到与时间范围在小时范围内拆分一样多的行(对于此示例数据，我将收到3行："2019-01-09 17:04:34"到"2019-001-09 17:59:59"、"2019-01-9 18:00:00"到"2019 9-01-09 18:59:59)和"2019-0-09 19:00:00"one_answers"2019-01-09 19:55:03")
如果我能获得这样的东西，我可以计算出我需要的所有东西。我预测要获得这个结果，我应该使用CTE(因为我们不知道我们需要划分多少范围的时间间隔)，但我没有经验。
希望我能把我的问题解释清楚。我从事oracle sql开发工作。
我将非常感谢你至少在一些提示方面的帮助。
由于您提到了递归，因此使用递归子查询因子分解：
-- CTE for sample data
with your_table (id, start_time, end_time) as (
select 1, timestamp '2019-01-09 17:04:34', timestamp '2019-01-09 19:55:03' from dual
union all
select 2, timestamp '2019-01-09 23:47:01', timestamp '2019-01-10 02:05:03' from dual
union all
select 3, timestamp '2019-01-09 18:01:01', timestamp '2019-01-09 18:02:07' from dual
union all
select 4, timestamp '2019-01-09 13:00:00', timestamp '2019-01-09 14:00:01' from dual
),
-- recursive CTE
rcte (id, hour_period, minutes, period_start_time, end_time, hour_num) as (
select id,
-- first period is the original start hour
extract(hour from start_time),
-- minutes in first period, which can end at the end of that hour, or at original
-- end time if earlier
case when extract(minute from end_time) = 0
and end_time >= cast(trunc(start_time, 'HH') as timestamp) + interval '1' hour
then 60
else extract(minute from
least(cast(trunc(start_time, 'HH') as timestamp) + interval '1' hour, end_time)
- start_time
)
end,
-- calculate next period start
cast(trunc(start_time, 'HH') as timestamp) + interval '1' hour,
-- original end time
end_time,
-- first hour period (for later ordering)
1
from your_table
union all
select id,
-- this period's hour value
extract(hour from period_start_time),
-- minutes in this period - either 60 if we haven't reach the end time yet;
-- or if we have then the number of minutes from the end time
case when end_time < period_start_time + interval '1' hour
then extract(minute from end_time)
else 60
end,
-- calculate next period start
period_start_time + interval '1' hour,
-- original end time
end_time,
-- increment hour period (for later ordering)
hour_num + 1
from rcte
where period_start_time < end_time
)
select id, hour_period, minutes
from rcte
order by id, hour_num;
ID HOUR_PERIOD MINUTES
---------- ----------- ----------
1 17 55
1 18 60
1 19 55
2 23 12
2 0 60
2 1 60
2 2 5
3 18 1
4 13 60
4 14 0
它查找锚点成员在该时段的第一个小时内花费的时间量，然后递归地查看后续小时，直到达到结束时间，每次增加传递的时段结束时间；在递归成员中，它检查是使用固定的60分钟(如果它知道还没有到达结束时间)还是使用距离结束时间的实际分钟。
我的示例时段包括跨越午夜、不到一个小时、从一小时的第一分钟开始、到一小时的前一分钟结束的时段，(无论如何，在我的计算中)以该小时的一行结束，分钟数为零。如果你不想看到它，你可以很容易地过滤掉
从您的文章中还不完全清楚您希望如何处理非零秒分量(舍入和/或截断的组合)。在任何情况下，一旦达成一整套不矛盾的规则，就可以很容易地对其进行编码。
除此之外，您的问题由两部分组成：确定每个id(每个活动或事件)的正确时间，以及该事件在该时间内的持续时间。在下面的查询中，使用CONNECT BY分层技术，我将小时和持续时间生成为天到秒的间隔。正如我所说，一旦你澄清了四舍五入规则，这可以转换为分钟(在0到60之间)。
with
your_table (id, start_time, end_time) as (
select 1, timestamp '2019-01-09 17:04:34', timestamp '2019-01-09 19:55:03'
from dual union all
select 2, timestamp '2019-01-09 23:47:01', timestamp '2019-01-10 02:05:03'
from dual union all
select 3, timestamp '2019-01-09 18:01:01', timestamp '2019-01-09 18:02:07'
from dual union all
select 4, timestamp '2019-01-09 13:00:00', timestamp '2019-01-09 14:00:01'
from dual
)
select id,
trunc(start_time, 'hh') + interval '1' hour * (level - 1) as hr,
case when level = 1 and connect_by_isleaf = 1
then end_time - start_time
when level = 1
then trunc(start_time, 'hh') + interval '1' hour - start_time
when connect_by_isleaf = 1
then end_time - trunc(end_time, 'hh')
else interval '1' hour
end as duration
from your_table
connect by trunc(start_time, 'hh') + interval '1' hour * (level - 1) < end_time
and prior id = id
and prior sys_guid() is not null
;
输出：
ID HR DURATION
---------- ------------------- -------------------
1 2019-01-09 17:00:00 +00 00:55:26.000000
1 2019-01-09 18:00:00 +00 01:00:00.000000
1 2019-01-09 19:00:00 +00 00:55:03.000000
2 2019-01-09 23:00:00 +00 00:12:59.000000
2 2019-01-10 00:00:00 +00 01:00:00.000000
2 2019-01-10 01:00:00 +00 01:00:00.000000
2 2019-01-10 02:00:00 +00 00:05:03.000000
3 2019-01-09 18:00:00 +00 00:01:06.000000
4 2019-01-09 13:00:00 +00 01:00:00.000000
4 2019-01-09 14:00:00 +00 00:00:01.000000