掛鐘每小時使用 postgres 或 timescale 超出時間間隔

我記錄了帶有開始和結束時間戳的會話持續時間：

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

我將使用查詢結果生成一個熱圖，其中一個軸為使用者，另一個軸為小時。

生成一系列時間戳範圍，加入您的數據，計算重疊並聚合：

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;

引用自：https://dba.stackexchange.com/questions/306275