PostgreSQL查询同一会话下不同用户重叠时间并转为用户配对

以下是可直接返回目标结果的PostgreSQL查询语句：

WITH user_session_ranges AS (
    -- 提前构造每个会话记录的有效在线时间范围，过滤未结束的无效记录
    SELECT 
        id,
        user_id,
        music_session_id,
        tsrange(created_at, session_removed_at, '[]') AS active_range
    FROM music_sessions_user_history
    WHERE session_removed_at IS NOT NULL
)
SELECT 
    u1.user_id AS "User",
    ARRAY_AGG(DISTINCT u2.user_id ORDER BY u2.user_id) AS "Other users in the session"
FROM user_session_ranges u1
-- 自连接匹配符合条件的共同参与用户
JOIN user_session_ranges u2 
    ON u1.music_session_id = u2.music_session_id
    AND u1.user_id != u2.user_id
    AND u1.active_range && u2.active_range
GROUP BY u1.user_id
-- 按需开启：过滤没有共同参与用户的记录
-- HAVING ARRAY_AGG(DISTINCT u2.user_id) <> '{}'
ORDER BY u1.user_id;

逻辑说明 #

• CTE层提前预处理时间范围和无效数据，避免后续重复计算tsrange提升执行效率。如果你的数据量很大，可以直接把你已经写好的exists过滤逻辑加到CTE的WHERE条件里，提前过滤掉没有任何重叠的孤立记录，进一步减少后续连接的计算量。
• 自连接的三个匹配条件严格对应需求：同一会话、不同用户、在线时间有重叠，用PostgreSQL原生的范围重叠运算符&&实现时间交集判断，语法简洁性能更高。
• 聚合时用DISTINCT去重，避免同一个用户多次进出同一会话导致的重复配对，额外加的ORDER BY可以让返回的共同用户列表排序更规整。
• 如果你需要按会话拆分结果（同一个用户在不同会话的共同参与人分开统计），只要把music_session_id同时加到SELECT和GROUP BY子句中即可。

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