将时间分配到区间桶中

Question

我有以下表格：

CREATE TABLE f_contact (
    agent character varying,
    datetimeconnect timestamp without time zone,
    datetimedisconnect timestamp without time zone,
    duration integer
);

duration 是断开时间和连接时间之间的秒数。
我可能有这样的数据：

agent   datetimeconnect         datetimedisconnect      duration
20024   2019-03-18 12:01:00.0   2019-03-18 13:01:30.0   3630
20011   2019-03-11 08:47:40.0   2019-03-11 09:30:10.0   2550

我希望将这些数据分配到15分钟的间隔中，得到以下结果：

20024   12:00   840
20024   12:15   900
20024   12:30   900
20024   12:45   900 
20024   13:00   90
20011   08:45   740
20011   09:00   900
20011   09:15   900
20011   09:30   10

如何实现这个目标？

- urbanmojo

Answer 1

这是一个有趣的问题。我稍微简化了列名，做如下：

with t as (
      select 20024 as agent, '2019-03-18 12:01:00.0'::timestamp as conn, '2019-03-18 13:01:30.0'::timestamp as disconn, 3630 duration union all
      select 20011, '2019-03-11 08:47:40.0', '2019-03-11 09:30:10.0', 2550
     )
select gs.t, t.*,
       extract(epoch from least(gs.t + interval '15 minute', disconn) - greatest(gs.t, conn))
from t cross join lateral
     generate_series(date_trunc('hour', t.conn), date_trunc('hour', t.disconn) + interval '1 hour', interval '15 minute') gs(t)
where conn <= gs.t + interval '15 minute' and disconn >= gs.t ;

这里是一个db<>fiddle。

我意识到持续时间列是不必要的。您试图捕获每个15分钟间隔的重叠秒数。

这会在整点边界处创建间隔--这很容易。这意味着一些重叠不正确，这就是where子句过滤掉的内容。

- Gordon Linoff

Answer 2

应该立即将其转换为秒，并使用整数进行计算，这样可以最快速地完成：

SELECT agent
     , to_char(to_timestamp(q) AT TIME ZONE 'UTC', 'HH24:MI') AS quarter_hour
     , least(q + 900, b) - greatest(a, q) AS seconds
FROM   (
   SELECT agent
        , extract(epoch FROM datetimeconnect)::int    AS a
        , extract(epoch FROM datetimedisconnect)::int AS b
   FROM   f_contact
   )  f, generate_series(a / 900 * 900, b, 900) q
ORDER  BY agent DESC, q;

产生期望的结果。 db<>fiddle here（在测试案例中添加了特殊情况）。

关键要点。

• extract(epoch FROM datetimeconnect)提取(引用手册) ...

  自1970年01月01日00:00:00 UTC以来的秒数

• generate_series()(整数变量)只生成所需的行数，没有多余的。确切地说是每个相关季度的下限 - 在结果中显示的值。

• a / 900 * 900使用整数除法向下舍入到完整的15分钟(900的倍数)。由于date_trunc()没有将时间戳截断为15分钟的选项，因此使用这个函数。

• least(q + 900, b) - greatest(a, q)确保正确计算起始时间和结束时间，即使两者都在同一个15分钟内(如fiddle中的扩展测试案例所示)。

• to_timestamp(q) AT TIME ZONE 'UTC'因为to_timestamp()返回timestamptz，我们想要相应的来自它的UTCtimestamp。

附注： duration 是一个功能相关的值。它不是任务所需的，也不应该在表中冗余存储。那只会拖慢一切。可以便宜地实时计算。

- Erwin Brandstetter

Answer 3

在Postgres中，您可以使用generate_series()生成数据序列。我会从生成一系列数字开始，然后将其与原始数据JOIN起来，以生成15分钟的时间段。内部查询可用于预先计算起始和结束边界。

考虑以下查询，展示了将时间戳舍入为15分钟并使用系列表将表连接的逻辑：

    SELECT *
    FROM generate_series(0, 99, 1) t(x)
    INNER JOIN (
        SELECT 
            f.*,
            DATE_TRUNC('hour', datetimeconnect)    
                + DATE_PART('minute', datetimeconnect   )::int / 15 * interval '15 min' connect_15min,
            DATE_TRUNC('hour', datetimedisconnect) 
                + DATE_PART('minute', datetimedisconnect)::int / 15 * interval '15 min' disconnect_15min
        FROM f_contact f
    ) c 
        ON c.disconnect_15min >= c.connect_15min + ((t.x * 15) || ' minute')::interval 
    ORDER BY c.datetimeconnect, t.x;

例如，对于agent = 2011，这将返回：

| x   | agent | datetimeconnect          | datetimedisconnect       | duration | connect_15min            | disconnect_15min         |
| --- | ----- | ------------------------ | ------------------------ | -------- | ------------------------ | ------------------------ |
| 0   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |
| 1   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |
| 2   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |
| 3   | 20011 | 2019-03-11T08:47:40.000Z | 2019-03-11T09:30:10.000Z | 2550     | 2019-03-11T08:45:00.000Z | 2019-03-11T09:30:00.000Z |

---

现在我们可以在FROM子句中计算持续时间。关键是要正确处理第一个和最后一个间隔，使用LEAST()和GREATEST()（请注意，duration不用于计算）：

SELECT 
    agent,
    c.connect_15min + ( t.x * 15 || ' minute' )::interval interval_start_15min,

    EXTRACT(EPOCH FROM (
        LEAST(datetimedisconnect, c.connect_15min + ( (t.x + 1) * 15 || ' minute' )::interval) 
        - GREATEST(datetimeconnect, c.connect_15min + ( t.x * 15 || ' minute' )::interval )
    )) duration
FROM generate_series(0, 99, 1) t(x)
INNER JOIN (
    SELECT 
        f.*,
        DATE_TRUNC('hour', datetimeconnect)    
            + DATE_PART('minute', datetimeconnect   )::int / 15 * interval '15 min' connect_15min,
        DATE_TRUNC('hour', datetimedisconnect) 
            + DATE_PART('minute', datetimedisconnect)::int / 15 * interval '15 min' disconnect_15min
    FROM f_contact f
) c 
    ON c.disconnect_15min >= c.connect_15min + ((t.x * 15) || ' minute')::interval 
ORDER BY agent, interval_start_15min;

这个DB Fiddle演示返回：

| agent | interval_start_15min     | duration |
| ----- | ------------------------ | -------- |
| 20011 | 2019-03-11T08:45:00.000Z | 740      |
| 20011 | 2019-03-11T09:00:00.000Z | 900      |
| 20011 | 2019-03-11T09:15:00.000Z | 900      |
| 20011 | 2019-03-11T09:30:00.000Z | 10       |
| 20024 | 2019-03-18T12:00:00.000Z | 840      |
| 20024 | 2019-03-18T12:15:00.000Z | 900      |
| 20024 | 2019-03-18T12:30:00.000Z | 900      |
| 20024 | 2019-03-18T12:45:00.000Z | 900      |
| 20024 | 2019-03-18T13:00:00.000Z | 90       |

- GMB