如何获取SQL中列的平均日期间隔？-Java 学习之路

我在网站上有一个用户交互表，我需要计算每个用户之间的平均交互时间 . 为了使它更容易理解，这里有一些表的记录：

Table of user interactions

第一列是用户ID，第二列是交互时间 . 我需要的结果是每个用户的交互之间的平均时间 . 例：

用户12345的平均交互间隔是1天

我已经尝试过使用窗口函数，但由于PostgreSQL不让我在窗口函数上使用GROUP BY或AVG，我无法得到平均值，我可以使用以下命令获取间隔，但无法对其进行分组基于用户ID .

SELECT INTERACTION_DATE - LAG(INTERACTION_DATE ) OVER (ORDER BY INTERACTION_DATE )

所以，我决定创建自己的自定义函数，然后创建一个自定义聚合函数来执行此操作，并在group by子句上使用此函数：

CREATE OR REPLACE FUNCTION DATE_INTERVAL(TIMESTAMP)  
     RETURNS TABLE (USER_INTERVALS INTERVAL) 
AS $$
  SELECT $1 - LAG($1) OVER (ORDER BY $1) 
$$
LANGUAGE SQL
IMMUTABLE;

但是这个函数只返回几行，其中一列具有空值 .

有一个更好的方法吗？

2 回答

您需要首先计算每行（和用户）的交互之间的差异，然后您可以计算平均值：

select user_id, avg(interaction_time)
from (
   select user_id, 
          interaction_date - lag(interaction_date) over (partition by user_id order by interaction_date) as interaction_time
   from the_table
) t
group by user_id;

回复于 2024-04-29T21:56:59+08:00

封装您的第一个查询然后计算平均值：

SELECT AVG(InteractionTime) FROM (
    SELECT INTERACTION_DATE - LAG(INTERACTION_DATE ) OVER (ORDER BY INTERACTION_DATE ) AS InteractionTime
)

回复于 2024-04-29T21:56:59+08:00

如何获取SQL中列的平均日期间隔？

2 回答

相关问题