PostgreSQL-根据列值选择拆分的行

Question

有人能建议一个按每小时工作分钟数划分项目的查询吗？

源表

total_working_minutes

start_timestamp	item_id
2021-02-01 14:10			120
2021-02-01 14:30	B	20
2021-02-01 16:30	A	10

您可以使用递归查询来实现这一点，它应该在Redshift和PostgreSQL中都能工作。首先，提取

• 第一个小时工作的小时和分钟数
• 工作的总分钟数

然后，对当前小时的工作分钟数小于总工作分钟数的每一行进行递归重复。在递归中，将起始小时增加1，并将总工作分钟数减去前一小时的工作分钟数。

最后，按小时和ID汇总结果。

with recursive 
split_times(timestamp_by_hour, item_id, working_minutes, total_working_minutes) as
(
select
date_trunc('hour', start_timestamp),
item_id,
least(total_working_minutes, 60 - extract(minutes from start_timestamp)),
total_working_minutes
from work_time
union all
select
timestamp_by_hour + interval '1 hour',
item_id,
least(total_working_minutes - working_minutes, 60),
total_working_minutes - working_minutes
from split_times
where total_working_minutes > working_minutes
)
select timestamp_by_hour, item_id, sum(working_minutes) working_minutes
from split_times
group by timestamp_by_hour, item_id
order by timestamp_by_hour, item_id;

DB Fiddle