Postgresql-在同一会话中查找不同用户的重叠时间范围，并将它们成对呈现

Question

我有一张表，里面记录了玩家在集体音乐播放中播放的会话。(乐器(

因此，如果用户加入会话并离开，则会创建一行。如果它们甚至加入同一会话2x，则创建两行。

表：music_sessions_user_history

| Column  | Type | Default|
| --- | --- | ---|---
| id  | character varying(64) | uuid_generate_v4()|
| user_id | user_id | |
| created_at | timestamp without time zone | now()|
| session_removed_at | timestamp without time zone | |
| max_concurrent_connections | integer |
| music_session_id|character varying(64)|

该表基本上是用户在给定会话中的时间量。因此，您可以将其视为PG中的时间范围或tsrange。max_concurrent_connections是一次在会话中的用户数。

因此，核心查询需要为同一会话中的不同用户找到重叠的时间范围；然后把他们算在一起玩。

查询需要这样做：它试图报告在与其他人的音乐会话中播放的每个用户，以及这些用户是谁

例如，如果一个userA和userB一起玩，而这是数据库中唯一的数据，那么会返回两行，如下所示：

| User  | Other users in the session |
| --- | --- |
|userA | [userB] |
|userB | [userA] | 

但如果用户A同时与用户B和用户C一起玩，那么三行会是这样的：

| User  | Other users in the session |
| --- | --- |
|userA | [userB, userC]|
|userB | [userA, userC]|
|userC | [userA, userB]|

非常感谢对构建此查询的任何帮助。

更新：

我可以使用此查询获得重叠的记录。

select m1.user_id, m1.created_at, m1.session_removed_at, m1.max_concurrent_connections, m1.music_session_id
from music_sessions_user_history m1
where exists (select 1
from music_sessions_user_history m2
where tsrange(m2.created_at, m2.session_removed_at, '[]') && tsrange(m1.created_at, m1.session_removed_at, '[]')
and m2.music_session_id = m1.music_session_id
and m2.id <> m1.id);

需要找到一种将这些结果转换为成对结果的方法。

Answer 1

1. 创建一个游标，并为每个提取的记录使用开始时间和结束时间之间的时间来确定哪些其他记录相交
2. 将相交结果追加到临时表中
3. 选择临时表的结果