查找用户第一次和第二次监听之间的平均时间

提问者：小点点

查找用户第一次和第二次监听之间的平均时间

假设我有一个音乐平台的用户监听的下表：

其中，id(int)是收听音乐（或书籍或任何其他材料）的用户的记录，user_id(int)是收听者id，started_at(timestamp)是用户开始收听的时间，finished_at是用户完成收听的时间

我需要做的是找出用户第一次和第二次收听之间的平均时间。例如，对于user_id=101的第一个用户，它将是：

“2017-10-05 15:15:30”(started_at列，第二行）-“2017-10-05 13:59:03”(finished_at,第一行）

这就产生了1小时15分钟的时差。

对于这种情况，我编写了以下代码：

   SELECT  user_id, (TIMESTAMPDIFF(SECOND, pDataDate, started_at)/3600)
   FROM    (
        SELECT  *,
                LAG(finished_at) OVER (ORDER BY finished_at) pDataDate
        FROM    listenings
    
        ) q
   WHERE   pDataDate IS NOT NULL

我的问题是处理表中只有一个user_id的情况（本例中user_id=102)。它只有一行，表示started_at和finished_at在同一列。我不知道如何正确地写一个陈述，结合这两个案例。有没有人可以建议一个包含这两种情况的查询（可以使用if/case语句）？

还有，我想找到一种方法，只取组的前两个最上面行的平均值。假设，对于user_id=101，我将只取行1和行2之间的平均值。

谢谢你，如果我写得不清楚，我很抱歉。我可能会编辑问题以方便阅读

共1个答案

匿名用户

使用row_number()代替lag()。然后可以使用聚合：

SELECT user_id,
        TIMESTAMPDIFF(SECOND, MAX(finished_at), NULLIF(MIN(finished_at), MAX(finished_at))) / 3600
FROM (SELECT l.*,
             ROW_NUMBER() OVER (PARTITION BY user_id ORJDER BY finished_at) as seqnum
      FROM listenings l    
     ) l
WHERE seqnum <= 2
GROUP BY user_id;

如果用户只有一行，则返回null。