測量事件之間的持續時間，按小時劃分

我在PostgreSQL 9.4中有下表，它保留了使用者登錄/註銷事件（登錄為event_type1，註銷為event_type0）。

CREATE TABLE user_online_offline_events (
 id serial,
 user_id int4,
 event_type int4,
 created_at timestamp
);

樣本數據：

INSERT INTO user_online_offline_events
      (id, user_id, event_type, created_at)
VALUES (1,  123,     1,         '2015-10-07 12:15:00'),
      (2,  123,     0,         '2015-10-07 12:25:00'),
      (3,  123,     1,         '2015-10-07 12:45:00'),
      (4,  123,     0,         '2015-10-07 13:10:00');

我想計算每個使用者每小時登錄的分鐘數：

| id | user_id |     time | minutes logged on |
+----+---------+----------+-------------------+
|  1 |     123 | 12:00:00 |                30 |
|  2 |     123 | 13:00:00 |                10 |

這是我的 WIP 版本。它不是很優雅，它仍然硬編碼了小時，忽略了 user_ids 並假設線上-離線事件是連續的：

select time, sum(minutes) / 60 as minutes from (
 SELECT
   date_trunc('hour', time) as time,
   CASE
   WHEN event_type = 0 AND lag(event_type, 1) OVER w = 1
     THEN
       extract(EPOCH FROM time - lag(time, 1) OVER w)
   WHEN event_type = 0 AND lag(event_type, 1) OVER w ISNULL
     THEN
       extract(EPOCH FROM time - date_trunc('hour', created_at))
   WHEN event_type = 1 AND lead(event_type, 1) OVER w ISNULL
     THEN
       extract(EPOCH FROM date_trunc('hour', time) + INTERVAL '1 hour' - time)
   ELSE 0
   END AS minutes
 FROM user_online_offline_events
 WHERE date_trunc('hour', time) = '2015-10-07 12:00:00'
 WINDOW w AS ( ORDER BY time )
 ORDER BY time
) m group by time;

如何正確執行此操作？

這比我最初理解的要復雜。處理它的一種方法是使用generate-series(),範圍類型和相關的函式和運算符：

SELECT user_id, hour, sum(upper(min_on) - lower(min_on)) AS minutes_on
FROM  (
  SELECT user_id, hour, u.range * h.range AS min_on
  FROM  (
     SELECT hour, tsrange(hour, hour + interval '1h') AS range
     FROM   generate_series('2015-10-07 00:00'::timestamp  -- defines range of interest
                          , '2015-10-08 03:00'::timestamp  -- cut off the rest
                          , interval '1h') hour
     ) h
  JOIN  (
     SELECT user_id, event_type
          , tsrange(created_at
                  , lead(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                   ) AS range
     FROM   user_online_offline_events
     -- add WHERE conditions to limit selection
     -- careful with cutting off leading "on" / trailing "off" events
     ) u ON u.event_type = 1 AND u.range && h.range
  ) sub
GROUP  BY  user_id, hour
ORDER  BY  user_id, hour;

SQL Fiddle 擴展數據。

要點

• 一次定義外部時間範圍generate_series()。該函式每小時生成一行。
• 將多天中每天每小時的分鐘數相加 - 如果外部時間範圍跨越多天。
• 沒有任何線上時間的小時不在結果中。如果你需要那個使用LEFT JOIN而不是JOIN
• 使用範圍的重疊運算元**&&來辨識匹配時間，並使用交點運算元***來計算實際重疊。
• 結果時間為interval。由於可能將多天相加，因此結果可能超過一小時，而簡單的EXTRACT()方法無法捕捉到這一點。您可以通過這種方式將分鐘提取為整數：

EXTRACT('epoch' FROM sum(upper(min_on) - lower(min_on))) / 60 AS minutes_on

• 基本時間戳範圍是用視窗函式lead()簡單計算的。這假設每次登錄後都會註銷，這通常會錯過特殊情況：

  • 同一使用者連續多次登錄/註銷事件
  • 沒有匹配的登錄/註銷您需要準確定義可以存在哪些極端情況以及如何處理它們。無論哪種方式都有解決方案。

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