基于Postgres事件数据计算用户会话指标的SQL查询咨询

基础数据说明 #

我们在PostgreSQL中存储了捕获的用户事件数据，表结构及示例数据如下：

Session定义 #

用户连续操作的时间区间，若某操作后10分钟及以上无后续操作，则该操作所在区间为一个session，后续操作属于新session。

核心指标查询SQL #

1. 单个用户或用户组的平均session时长 #

通用查询（支持单个用户/用户组过滤）

WITH user_sessions AS (
    SELECT
        user,
        -- 标记session分组：当前事件与上一事件间隔≥10分钟则开启新session
        SUM(CASE WHEN EXTRACT(EPOCH FROM (event_datetime - LAG(event_datetime) OVER (PARTITION BY user ORDER BY event_datetime))) >= 600 THEN 1 ELSE 0 END) OVER (PARTITION BY user ORDER BY event_datetime) + 1 AS session_id,
        event_datetime
    FROM user_events
    -- 可选：过滤单个用户或用户组
    -- WHERE user IN ('Bob', 'Jack')
),
session_duration AS (
    SELECT
        user,
        session_id,
        EXTRACT(EPOCH FROM (MAX(event_datetime) - MIN(event_datetime))) / 60 AS session_duration_minutes
    FROM user_sessions
    GROUP BY user, session_id
)
SELECT
    user,
    AVG(session_duration_minutes) AS avg_session_duration_minutes
FROM session_duration
GROUP BY user;

• 说明：通过窗口函数LAG计算相邻事件的时间差，标记session分组；再统计每个session的起止时间差，最后计算平均时长。可通过WHERE子句指定单个用户或用户组。

2. 用户的平均session数量 #

WITH user_sessions AS (
    SELECT
        user,
        SUM(CASE WHEN EXTRACT(EPOCH FROM (event_datetime - LAG(event_datetime) OVER (PARTITION BY user ORDER BY event_datetime))) >= 600 THEN 1 ELSE 0 END) OVER (PARTITION BY user ORDER BY event_datetime) + 1 AS session_id
    FROM user_events
),
user_session_count AS (
    SELECT
        user,
        COUNT(DISTINCT session_id) AS session_count
    FROM user_sessions
    GROUP BY user
)
SELECT
    AVG(session_count) AS avg_sessions_per_user
FROM user_session_count;

• 说明：先统计每个用户的session总数，再计算所有用户的session数量平均值。

3. 特定用户组的session内高频活动统计（以Perform Search为例） #

WITH user_sessions AS (
    SELECT
        user,
        SUM(CASE WHEN EXTRACT(EPOCH FROM (event_datetime - LAG(event_datetime) OVER (PARTITION BY user ORDER BY event_datetime))) >= 600 THEN 1 ELSE 0 END) OVER (PARTITION BY user ORDER BY event_datetime) + 1 AS session_id,
        event_name
    FROM user_events
    WHERE user IN ('Jack', 'Ashley') -- 指定目标用户组
),
session_activity_stats AS (
    SELECT
        user,
        session_id,
        COUNT(CASE WHEN event_name = 'Perform Search' THEN 1 END) AS perform_search_count,
        -- 可选：添加其他活动的统计
        COUNT(CASE WHEN event_name = 'View Project' THEN 1 END) AS view_project_count
    FROM user_sessions
    GROUP BY user, session_id
)
SELECT * FROM session_activity_stats ORDER BY user, session_id;

• 说明：先为目标用户组的事件标记session，再按session分组统计指定活动的发生次数，可扩展添加其他事件的统计项。

预计算优化方案 #

当数据量较大时，实时计算session会存在性能瓶颈，可通过预计算表/视图优化：

1. 创建session预计算表 #

-- 创建session维度表
CREATE TABLE user_session_dim (
    session_id SERIAL PRIMARY KEY,
    user VARCHAR(50) NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    duration_minutes NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建事件与session关联表
CREATE TABLE user_event_session_map (
    event_id INT REFERENCES user_events(id),
    session_id INT REFERENCES user_session_dim(session_id),
    PRIMARY KEY (event_id)
);

2. 定期刷新session数据（可通过PostgreSQL定时任务pg_cron实现） #

-- 清空旧数据
TRUNCATE user_session_dim, user_event_session_map;

-- 计算并插入新的session数据
WITH user_events_with_lag AS (
    SELECT
        id,
        user,
        event_datetime,
        LAG(event_datetime) OVER (PARTITION BY user ORDER BY event_datetime) AS prev_event_time
    FROM user_events
),
session_markers AS (
    SELECT
        id,
        user,
        event_datetime,
        CASE WHEN prev_event_time IS NULL OR EXTRACT(EPOCH FROM (event_datetime - prev_event_time)) >= 600 THEN 1 ELSE 0 END AS is_new_session
    FROM user_events_with_lag
),
session_groups AS (
    SELECT
        id,
        user,
        event_datetime,
        SUM(is_new_session) OVER (PARTITION BY user ORDER BY event_datetime) AS session_group_id
    FROM session_markers
),
session_details AS (
    SELECT
        user,
        session_group_id,
        MIN(event_datetime) AS start_time,
        MAX(event_datetime) AS end_time,
        EXTRACT(EPOCH FROM (MAX(event_datetime) - MIN(event_datetime))) / 60 AS duration_minutes
    FROM session_groups
    GROUP BY user, session_group_id
)
-- 插入session维度表
INSERT INTO user_session_dim (user, start_time, end_time, duration_minutes)
SELECT user, start_time, end_time, duration_minutes FROM session_details;

-- 插入事件与session关联表
WITH session_groups AS (
    SELECT
        sg.id,
        usd.session_id
    FROM session_groups sg
    JOIN user_session_dim usd ON sg.user = usd.user AND sg.session_group_id = (
        SELECT COUNT(*) FROM user_session_dim WHERE user = sg.user AND start_time <= sg.event_datetime
    )
)
INSERT INTO user_event_session_map (event_id, session_id)
SELECT id, session_id FROM session_groups;

3. 基于预计算表的指标查询 #

• 平均session时长：

SELECT user, AVG(duration_minutes) AS avg_session_duration_minutes FROM user_session_dim GROUP BY user;

• 用户平均session数量：

SELECT AVG(session_count) AS avg_sessions_per_user FROM (SELECT user, COUNT(session_id) AS session_count FROM user_session_dim GROUP BY user) t;

• 特定用户组session内活动统计：

SELECT
    usd.user,
    usd.session_id,
    COUNT(CASE WHEN ue.event_name = 'Perform Search' THEN 1 END) AS perform_search_count
FROM user_session_dim usd
JOIN user_event_session_map uesm ON usd.session_id = uesm.session_id
JOIN user_events ue ON uesm.event_id = ue.id
WHERE usd.user IN ('Jack', 'Ashley')
GROUP BY usd.user, usd.session_id
ORDER BY usd.user, usd.session_id;

内容的提问来源于stack exchange，提问作者Ege Ersoz