Could someone please suggest a query which splits items by working minutes per hour?

Source table

Expected result

Thanks in advance!

CodePudding user response：

You can accomplish this using a recursive query, which should work in both Redshift and PostgreSQL. The query uses a Common Table Expression to introduce a toggle, in order to generate two rows for each line in the original table:

• The hour and amount of minutes worked the first hour
• The following hour and remaining minutes (if any)

Then, the recursion splits each of the following hours the same way, until the remaining minutes are 60 or less.

Finally, the output is filtered to only include the first row of each split (pt = 0), and the results are aggregated by hour and ID.

with recursive 
  toggle(t) as (values(0), (1)),
  split_times(timestamp_by_hour, item_id, working_minutes, pt) as
(
  select case t 
    when 0 then date_trunc('hour', start_timestamp)
    else date_trunc('hour', start_timestamp   '1 hour')
  end,
  item_id,
  case t 
    when 0 
    then least(total_working_minutes, 60 - extract(minutes from start_timestamp))
    else total_working_minutes - 60   extract(minutes from start_timestamp)
  end,
  t
  from work_time cross join toggle
  where t = 0 or total_working_minutes > 60
union all
  select case t 
    when 0 then timestamp_by_hour
    else timestamp_by_hour   '1 hour'
  end,
  item_id,
  case t 
    when 0 
    then least(working_minutes, 60)
    else working_minutes - 60
  end,
  t
  from split_times cross join toggle
  where pt = 1 and (t = 0 or working_minutes > 60)
)
select timestamp_by_hour, item_id, sum(working_minutes) working_minutes
from split_times
where pt = 0
group by timestamp_by_hour, item_id
order by timestamp_by_hour, item_id;