按datediff对行进行分组，然后使用diff函数

debugcn 发表于 Dev

Tomasgabrs

我在PostgreSQL中有带时间戳列created_at和整数列的表user_id。

id | created_at | user_id
1 | 2019-10-14 09:26:53.813 | 1
2 | 2019-10-14 09:26:54.813 | 1
3 | 2019-10-14 09:46:53.813 | 1
4 | 2019-10-14 09:46:54.813 | 2
5 | 2019-10-14 09:46:55.813 | 1
6 | 2019-10-14 09:46:56.813 | 1
7 | 2019-10-14 09:46:57.813 | 2

每行代表用户的某些操作。我需要计算平均用户会话长度。会话定义为时间差小于10分钟的一组动作。当两个用户操作之间相差10分钟或更长时间时，新会话开始。

我在想：

将行分组user_id。
通过单个用户当前项目和下一项之间的差异将行分组（以某种方式）。（获得会议）
计算一组（会话）中第一行和最后一行之间的差异。（以获取会话长度）
计算平均会话时长。（以获取平均会话时长）

但是我不能用SQL编写它。

您能给我一些建议/示例如何在SQL中完成吗？

男装

分步演示：db <> fiddle

SELECT 
    user_id,
    AVG(diff)
FROM (
    SELECT DISTINCT
        user_id,
        group_id,
        first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at DESC)
            - first_value(created_at) OVER (PARTITION BY user_id, group_id ORDER BY created_at) as diff
    FROM (
        SELECT
            id, created_at, user_id,
            SUM(group_id) OVER (PARTITION BY user_id ORDER BY created_at) AS group_id
        FROM (
            SELECT
                *,
                (created_at 
                    - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at)
                    > interval '10 minutes')::int AS group_id
            FROM
                mytable   
        )s
    )s
)s
GROUP BY user_id

(created_at - lag(created_at, 1, created_at) OVER (PARTITION BY user_id ORDER BY created_at) > interval '10 minutes')::int AS group_id：lag() 窗口函数created_at从有序user_id分区（组）中取自上一个记录的值（第二个参数：步长，第三个参数：如果没有上一个记录==当前值，则为默认值）。然后created_at计算当前值与前一个值之间的差。如果此值> 10分钟，则结果为true; false除此以外。该布尔值可以转换为一个int值，结果为0或1
累积SUM()在0/1值，这导致group_idS表示每每一个新的会话user_id
每个user_id会话的第一个created_at时间戳group_id可以通过first_value()window函数获取，最后一个byfirst_value()和DESCorder可以获取。不同之处在于您每次会话的时长。使用该DISTINCT子句是因为将差值放在每个记录上。但是我们只需要一次。
最后，您可以AVG()为用户分组和区别。