在postgres中查找用户的总会话时间

Question

我正在尝试创建一个查询，该查询将为每个用户提供一个每月登录时间的列。

username | auth_event_type |         time        | credential_id 

Joe      |       1         | 2021-11-01 09:00:00 | 44
Joe      |       2         | 2021-11-01 10:00:00 | 44
Jeff     |       1         | 2021-11-01 11:00:00 | 45
Jeff     |       2         | 2021-11-01 12:00:00 | 45
Joe      |       1         | 2021-11-01 12:00:00 | 46    
Joe      |       2         | 2021-11-01 12:30:00 | 46
Joe      |       1         | 2021-12-06 14:30:00 | 47
Joe      |       2         | 2021-12-06 15:30:00 | 47

auth_event_type列指定事件是登录(1)还是注销(2)，credential_id指示会话。

我正在尝试创建一个具有如下输出的查询：

username | year_month | total_time
Joe      | 2021-11    | 1:30
Jeff     | 2021-11    | 1:00
Joe      | 2021-12    | 1:00

我要怎么在postgres做这件事？我在想它会有一个窗口功能吗？如果有人能给我指明正确的方向，那就太好了。谢谢。

Answer 1

解决方案1部分工作的

在您的情况下，不确定窗口函数对您有帮助，但是聚合函数会：

WITH list AS
(
SELECT username
     , date_trunc('month', time) AS year_month
     , max(time ORDER BY time) - min(time ORDER BY time) AS session_duration
  FROM your_table
 GROUP BY username, date_trunc('month', time), credential_id
 )
SELECT username
     , to_char (year_month, 'YYYY-MM') AS year_month
     , sum(session_duration) AS total_time
  FROM list
 GROUP BY username, year_month

查询的第一部分聚合相同用户名credential_id的登录/注销时间，第二部分将每个year_month的登录/注销时间之和进行汇总。这个查询运行良好，直到登录时间和注销时间都在同一个月内，但如果不是，则会失败。

解决方案2全工作

为了计算每个用户名和每个月的total_time (无论登录时间和注销时间)，我们可以使用一种时间范围方法，它将会话范围[login_time, logout_time)与每月范围[monthly_start_time, monthly_end_time)相交：

WITH monthly_range AS
(
SELECT to_char(m.month_start_date, 'YYYY-MM') AS month
     , tsrange(m.month_start_date, m.month_start_date+ interval '1 month' ) AS monthly_range
  FROM
     ( SELECT generate_series(min(date_trunc('month', time)), max(date_trunc('month', time)), '1 month') AS month_start_date
         FROM your_table
     ) AS m
), session_range AS
(
SELECT username
     , tsrange(min(time ORDER BY auth_event_type), max(time ORDER BY auth_event_type)) AS session_range
  FROM your_table
 GROUP BY username, credential_id
)
SELECT s.username
     , m.month
     , sum(upper(p.period) - lower(p.period)) AS total_time
  FROM monthly_range AS m
 INNER JOIN session_range AS s
    ON s.session_range && m.monthly_range
 CROSS JOIN LATERAL (SELECT s.session_range * m.monthly_range AS period) AS p
 GROUP BY s.username, m.month

参见[医]小提琴中的结果

Answer 2

使用lag()窗口函数，并使用由time命令的credential_id对其进行分区。

WITH j AS (
  SELECT username, time, age(time, LAG(time) OVER w)
  FROM t
  WINDOW w AS (PARTITION BY credential_id ORDER BY time
               ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
) 
SELECT username, to_char(time,'yyyy-mm'),sum(age) FROM j
GROUP BY 1,2;

注意事项：在本例中，框架ROWS BETWEEN 1 PRECEDING AND CURRENT ROW几乎是可选的，但保持窗口功能尽可能明确是一种很好的做法，这样以后您就不必阅读文档来确定查询要做什么了。

演示：db<>fiddle