使用两个日期/时间戳列计算豆荚的总运行时间

Question

我目前正在Postgresql中存储所有的吊舱。我通过created_time和deleted_time列跟踪每个豆荚的创建和删除。当pod未被删除时，其deleted_time列仍然为空。

SqlFiddle可以在这里找到：SQLFiddle

下面给出了该模式，以供快速参考：

create table pods (
  name text primary key,
  created_time timestamp without time zone,
  deleted_time timestamp without time zone default null
);

insert into pods (name, created_time)
values
('pod-1', '02-01-2020 01:00:00'),
('pod-2', '02-01-2020 02:00:00');

update pods set deleted_time = '02-01-2020 03:00:00' where name = 'pod-1';

在计算运行时间时遇到的挑战如下：

1. 处理空deleted_time列(pod仍在运行)
2. 将created_time和deleted_time裁剪到用户给定的范围(开始和结束时间)。

因此，我想出了下面的查询，它计算了使用时间，但当您处理给定日期范围内的数百万个豆荚时，它的速度有点慢。

-- end_time = '2020-02-01 04:00:00'
-- start_time = '2020-02-01 02:00:00'

with model as (
  select
    name,
    case when created_time < '2020-02-01 02:00:00' then '2020-02-01 02:00:00' else created_time end as created_time,
    coalesce(
      case when deleted_time >= created_time and deleted_time <= '2020-02-01 04:00:00' then deleted_time else null end,
      '2020-02-01 04:00:00'
    ) as deleted_time,
    extract(
      epoch from(
          coalesce(
          case when deleted_time >= created_time and deleted_time <= '2020-02-01 04:00:00' then deleted_time else null end, '2020-02-01 04:00:00'
          ) - case when created_time < '2020-02-01 02:00:00' then '2020-02-01 02:00:00' else created_time end)) / 3600::float as usage_hours
  from pods
  where created_time <= '2020-02-01 04:00:00' and
        (deleted_time is null or deleted_time >= '2020-02-01 02:00:00')
)
select sum(usage_hours) from model; -- should return 3.

因此，我的问题是，我想知道是否有任何有效的方法来计算这些统计数字？或者甚至可以用给定的模式计算它吗？

Answer 1

总体逻辑在我看来不错。

您可以使用least()、greatest()、coalesce()和更简单的日期算法将其简化一些：

select 
    sum(extract(epoch from 
        least('2020-02-01 04:00:00', coalesce(deleted_time, '2020-02-01 04:00:00'))
        - greatest('2020-02-01 02:00:00', created_time)
    )) / 60.0 / 60 usage_hours 
from pods
where 
    created_time <= '2020-02-01 04:00:00' and
    (deleted_time is null or deleted_time >= '2020-02-01 02:00:00')