主页 / dba / 问题 / 306275
Accepted
amq
Asked: 2022-01-20 05:12:30 +0800 CST

挂钟每小时使用 postgres 或 timescale 超出时间间隔

  • 772

我记录了带有开始和结束时间戳的会话持续时间：

user_id | session_id | session_start                 | session_end
--------+------------+-------------------------------+------------------------------
1       | 1          | 2021-02-25 10:10:00.000 +0100 | 2021-02-25 10:20:00.000 +0100
1       | 2          | 2021-02-25 10:50:00.000 +0100 | 2021-02-25 10:55:00.000 +0100
1       | 3          | 2021-02-25 11:40:00.000 +0100 | 2021-02-25 12:30:00.000 +0100

获取每个会话的持续时间就像减去两个时间戳一样简单。现在，我想用挂钟每小时桶来表示会话持续时间，每个用户求和。

这里的主要问题是间隔跨越多个小时的会话。一个从 11:40 开始到 12:30 结束的会话应该用 11:00 20 分钟的存储桶和 12:00 30 分钟的存储桶来表示：

user_id | bucket   | duration
--------+----------+---------
1       | 00:00:00 | 00:00:00
1       | 01:00:00 | 00:00:00
...
1       | 10:00:00 | 00:15:00
1       | 11:00:00 | 00:20:00
1       | 12:00:00 | 00:30:00

我尝试使用time_seriesand date_trunc，但没有成功。

理想情况下，存储桶还包括日期，这也可能简化逻辑。如果没有，一次选择一天也可以。

user_id | bucket              | duration
--------+---------------------+----------
1       | 2021-02-25 00:00:00 | 00:00:00
1       | 2021-02-25 01:00:00 | 00:00:00
...
1       | 2021-02-25 10:00:00 | 00:15:00
1       | 2021-02-25 11:00:00 | 00:20:00
1       | 2021-02-25 12:00:00 | 00:30:00

我将使用查询结果生成一个热图，其中一个轴为用户，另一个轴为小时。

postgresql timescaledb

2 个回答

  1. Best Answer

    生成一系列时间戳范围，加入您的数据，计算重叠并聚合：

    SELECT user_id,
       bucket,
       coalesce(sum(upper(inters) - lower(inters)), 0) AS duration
FROM (SELECT user_id,
             lower(ranges.r) AS bucket,
             tsrange(tab.start, tab.end, '[)') * ranges.r AS inters
      FROM (SELECT tsrange(st, st + '1 hour'::interval, '[)') AS r
            FROM generate_series(
                    '2021-01-01 00:00:00'::timestamp,
                    '2021-12-31 23:00:00'::timestamp,
                    '1 hour'::interval
                 ) AS g(st)
           ) AS ranges
         LEFT JOIN tab
            ON tsrange(tab.start, tab.end, '[)') * ranges.r <> 'empty'
     ) AS intersections
GROUP BY tab.user_id, bucket;
    • 1

  2. 你可以做这样的事情——下面的所有代码都可以在这里找到。该解决方案利用了 PostgreSQL范围类型——一个非常强大的工具，尤其是对于这类工作。我还使用了一个物化日历表，按照此处的说明生成。

    CREATE TABLE test
(
  user_id    INTEGER   NOT NULL,
  session_id INTEGER   NOT NULL,
  s_start    TIMESTAMP NOT NULL,
  s_end      TIMESTAMP NOT NULL     
);

    然后填充它：

    INSERT INTO test VALUES
(1, 1, '2021-02-25 10:10:00.000 +0100',   '2021-02-25 10:20:00.000 +0100'),

(1, 2, '2021-02-25 10:50:00.000 +0100',   '2021-02-25 10:55:00.000 +0100'),

(1, 3, '2021-02-25 11:40:00.000 +0100',   '2021-02-25 12:30:00.000 +0100'),

(1, 4, '2021-02-26 11:46:00 +0100', '2021-02-26 11:57:00 +0100');  
-- added for testing - not 4 minutes and 7 minutes.

    我添加了最后一条记录进行测试 - 时隙的边界与日历表的 10 分钟不匹配。

    现在，您必须有一个日历表 - 这是为了JOIN您的时间段并执行计算。

    CREATE TABLE calendar_range
(
  dr          TSTZRANGE NOT NULL,
  minute_slot SMALLINT NOT NULL,
  hour_slot   SMALLINT NOT NULL
);

    现在，每条记录需要 50 个字节，因此对于 100MB，您将拥有超过 30 年的记录 - 或者您可能希望按照其他答案动态生成它 - 您的存储、CPU 和 RAM 会告诉您该怎么做在这里-我建议永久日历表是更好的解决方案，特别是如果您定期进行此类计算！它也将更加高效。

    我在小提琴上留下了一些我的第一个查询 - 这是最后一个：

    INSERT INTO calendar_range (dr, minute_slot, hour_slot)
SELECT
  TSTZRANGE
  (
    '2021-01-01 00:00:00'::TIMESTAMPTZ 
      + (m ||    ' MINUTE')::INTERVAL,
    
    '2021-01-01 00:10:00'::TIMESTAMPTZ
      + (m ||   ' MINUTE')::INTERVAL,

    '[)'
  ) AS tsr,

  (m % 60)/10 AS slot,
  
  DATE_PART
  (
    'HOUR',     
    '2021-01-01 00:00:00'::TIMESTAMPTZ 
      + (m ||    ' MINUTE')::INTERVAL 
  ) AS t_hour

FROM
  GENERATE_SERIES(0,  100000,  10) AS t(m);

    只是为了检查我们的calendar_range桌子：

    SELECT * FROM calendar_range;

    结果：

    dr  minute_slot     hour_slot
["2021-01-01 00:00:00+00","2021-01-01 00:10:00+00")     0   0
["2021-01-01 00:10:00+00","2021-01-01 00:20:00+00")     1   0
["2021-01-01 00:20:00+00","2021-01-01 00:30:00+00")     2   0
...
... snipped for brevity
...

    因此，我们有一个范围（包括开始，不包括结束边界），从 2021 年开始并持续 100 天 - 足以涵盖问题中的样本数据。

    然后我们对实际时隙数据做一个SELECT和JOIN日历表如下：

    SELECT
  t.user_id, t.session_id, 
  t.s_start::TIME, t.s_end::TIME,
 
  CASE
    WHEN (LOWER(cr.dr) >= t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN 10
    ELSE 
      CASE
        WHEN (LOWER(cr.dr) < t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN
          EXTRACT(EPOCH FROM (UPPER(cr.dr) - t.s_start)) / 60
        ELSE EXTRACT(EPOCH FROM (t.s_end - LOWER(cr.dr))) / 60
      END
  END AS cas,
  
  cr.dr,
  
  cr.minute_slot,
  cr.hour_slot

FROM test t
JOIN calendar_range cr
  ON TSTZRANGE(t.s_start, t.s_end, '[)') && cr.dr;

    注意 &&OVERLAPS运算符的使用。

    结果：

    user_id     session_id  s_start     s_end   cas     dr  minute_slot     hour_slot
1   1   2021-02-25 10:10:00     2021-02-25 10:20:00     10  ["2021-02-25 10:10:00+00","2021-02-25 10:20:00+00")     1   10
1   2   2021-02-25 10:50:00     2021-02-25 10:55:00     5   ["2021-02-25 10:50:00+00","2021-02-25 11:00:00+00")     5   10
1   3   2021-02-25 11:40:00     2021-02-25 12:30:00     10  ["2021-02-25 11:40:00+00","2021-02-25 11:50:00+00")     4   11
...
... snipped for brevity
...
1   4   2021-02-26 11:46:00     2021-02-26 11:57:00     4   ["2021-02-26 11:40:00+00","2021-02-26 11:50:00+00")     4   11
1   4   2021-02-26 11:46:00     2021-02-26 11:57:00     7   ["2021-02-26 11:50:00+00","2021-02-26 12:00:00+00")     5   11

    插槽的持续时间为 10 分钟（根据问题 - 对于 1 小时长的插槽，请在此处查看小提琴）。hour_slot如果您有兴趣了解您的事件发生在一天中的哪个小时，我还包括了一个字段。当然，您可以根据自己的要求进行更改-原理相同-您可以根据需要SUM()和GROUP BY各种插槽。

    请注意，从 11:46 到 11:57 的时段已正确计算 - 从11:40-开始的时段为 4 分钟，在-时段为11:507 分钟。请务必检查边缘情况，因为它们很容易被遗漏。11:5012:00

    请确保您了解包含/排除边界符号（开始和结束方 ( []) 和圆 ( ())）括号 - 以及如何使用它们 - 任何混淆都可能是微妙的、难以发现的错误的根源！

    最后，您似乎将TIMESTAMPs 与TIME ZONE- 即 theTIMESTAMPTZ和它们相应的范围类型一起使用 - 这是一件好事！您应该始终将 UTC 用于与时间戳有关的任何事情 - 而不是存储例如偏移量 - 因为这可能会根据 DST（夏令时）而有所不同。

    以下评论：

    How could I get rows for all ranges - also where there is no corresponding tab entry (where duration is zero, where user_id is null)?

    SELECT
  COALESCE(t.user_id, 0) AS u_id, COALESCE(t.session_id, 0) AS s_id, 
  t.s_start::TIME, t.s_end::TIME,
  COALESCE(
  
  CASE
    WHEN (LOWER(cr.dr) >= t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN 10
    ELSE 
      CASE
        WHEN (LOWER(cr.dr) < t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN
          EXTRACT(EPOCH FROM (UPPER(cr.dr) - t.s_start)) / 60
        ELSE EXTRACT(EPOCH FROM (t.s_end - LOWER(cr.dr))) / 60
      END
  END, 0) AS "No. mins",
  
  cr.dr,
  
  cr.minute_slot,
  cr.hour_slot

FROM calendar_range cr
LEFT OUTER JOIN test t
  ON TSTZRANGE(t.s_start, t.s_end, '[)') && cr.dr
WHERE LOWER(cr.dr) >= '2021-02-25 10:00:00'
  AND LOWER(cr.dr) <  '2021-02-25 13:30:00';

    结果：

    在此处输入图像描述

    注意会话 2 计算的 5 分钟。此外，使用该WHERE子句限制范围 - 以免有太多空记录。我在这里使用了图像而不是发布文本，因为对于所有NULLs，对齐记录非常困难。

    Ah, I think my examples gave an impression that I'm looking for 10-minute slots, sorry for that! I was only looking for 1-hour slots

    每小时时段的原理是完全一样的——请看这里的小提琴。

    • 1

相关问题