将时间聚合数据分解为几个均匀分布的间隔

Question

数据库结构如下所示。 它只是代表员工、正在处理的任务数量、开始整个活动的事实以及结束的事实和所花费的秒数的总摘要的一行。

员工	number_of_tasks	start_act	end_act	total_seconds
AXF-6263	5	12:30	14:10	6000

我想将其按相等的时间间隔分解，每次 1 小时，另外总结总秒数，落入特定的时间间隔。从而得到这样的结果。那么有没有什么SQL方法可以解决这个问题呢？

员工	number_of_tasks	start_act	end_act	Total_seconds
AXF-6263	5	12:00	13:00	1800
AXF-6263	5	13:00	14:00	3600
AXF-6263	5	14:00	15:00	600

提前感谢您提供任何帮助！

Answer 1

有趣的任务，我决定加入一个系列，然后找出他的重叠部分。

我的方法是采用灵活的时间单位，这样您就可以更改为天/周/等

with employees AS (
  SELECT
    'AXF-6263' AS employee,
    5 AS number_of_tasks,
    '2022-03-25 12:30'::timestamp AS start_act,
    '2022-03-25 14:10'::timestamp AS end_act,
    6000 AS total_seconds
),

-- select a set of employees to work on
sample_set AS (
  SELECT
    employee,
    start_act,
    end_act
  FROM
    employees
  -- WHERE ...
  -- LIMIT ...
),

-- choose a unit for the interval
unit AS (
  SELECT
    'hour' AS unit,
    '1 hour'::interval AS interval
),

-- generate a full series of time intervals
time_spans AS (
  SELECT
    sample_set.employee,
    generate_series(
       min(date_trunc(unit.unit, start_act)),
       max(date_trunc(unit.unit, end_act)),
       unit.interval
     ) AS start_act
  FROM
    sample_set
  JOIN unit
    ON true
  GROUP BY
    employee,
    unit.interval
)

-- final results
SELECT
  employee,
  number_of_tasks,
  time_spans.start_act,
  time_spans.start_act + unit.interval AS end_act,
  CASE
    WHEN employees.start_act > time_spans.start_act THEN time_spans.start_act - employees.start_act + unit.interval
    WHEN employees.end_act < time_spans.start_act + unit.interval THEN employees.end_act - time_spans.start_act
    ELSE unit.interval
  END AS total_seconds
FROM
  employees
JOIN time_spans USING (employee)
JOIN unit ON
  true
ORDER BY
  employee,
  time_spans.start_act

Answer 2

使用generate_series并将其与您的表交叉连接。

-- Test case
create temporary table the_table (employee text, number_of_tasks integer, start_act time, end_act time, total_seconds integer);
insert into the_table values ('AXF-6263', 5, '12:30', '14:10', 6000);

-- Query
select employee, number_of_tasks, 
       h::time start_act, h::time  + interval '1 hour' end_act, 
       extract('epoch' from least(h::time + interval '1 hour', end_act) - greatest(h::time, start_act))::integer total_seconds
from the_table cross join lateral
     generate_series(date_trunc('hour', current_date + start_act), date_trunc('hour', current_date + end_act), interval '1 hour') h
order by h;

员工	任务数	开始时间	结束时间	总秒数
AXF-6263	5	12:00:00	13:00:00	1800
AXF-6263	5	13:00:00	14:00:00	3600
AXF-6263	5	14:00:00	15:00:00	600